这篇文章主要介绍dbms_monitor包中新增加的跟踪方法。新增加的跟踪过程可以基于特定的客户端标识或服务名,模块名与操作名的组合来启用诊断与工作量管理。跟踪也可以在会话级别启用。在有些情况下可以产生多个跟踪文件(例如,当对一个模块跟踪服务级别时)。trcsess工具可以用来扫描所有跟踪文件并将它们合并成一个跟踪文件。在合并之后可以使用标准跟踪文件分析方法比如tkprof。对客户端标识或服务/模块/操作的跟踪状态是永久的可以跨会话的断开与数据库的关闭,并且可以应用于所有实例。跟踪直到使用dbms_monitor禁用之前都是启用状态。
如何查看是否启用跟踪 SQL> select trace_type, primary_id, qualifier_id1, waits, binds from dba_enabled_traces; TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 WAITS BINDS --------------------- --------------------------------------- ------------------------------- ----- ----- SERVICE_MODULE SYS$USERS SQL*Plus TRUE FALSE CLIENT_ID HUGO TRUE FALSE SERVICE v101_DGB TRUE FALSE
可以看到三个不同的跟踪被启用。
session_trace_enable函数 dbms_monitor.session_trace_enable(session_id=>x,serial_num=>y,waits=>(TRUE|FALSE),binds=>(TRUE|FALSE)); 禁用跟踪 dbms_monitor.session_trace_disable(session_id=>x,serial_num=>y); 缺省情况下跟踪对于等待为true,而绑定变量为false 通过查询v$session得到会话与serial号 SQL> select sid, serial#,username from v$session; SID SERIAL# USERNAME ---------- ---------- ------------------------------ 2 3257 INSUR_CHANGDE 4 45331 41 20923 INSUR_CHANGDE 42 19225 INSUR_CHANGDE 77 1 78 2191 CARD_DB 115 1 118 47221 YBCX 153 1 157 25173 INSUR_CHANGDE 191 1 执行下面的命令开始跟踪 SQL> execute dbms_monitor.session_trace_enable(157,25173); PL/SQL procedure successfully completed. 需要注意的是在dba_enabled_traces视图中没有记录,因为跟踪并没有经历数据库关闭。 可以通过查询v$session得到被跟踪会话列表: SQL> select sid,serial#,username,sql_trace_waits,sql_trace_binds,sql_trace_plan_stats 2 from v$session 3 where sql_trace = 'enabled'; no rows selected 当会话断羡慕或执行以下命令可以停止跟踪 SQL> execute dbms_monitor.session_trace_disable(157,25173); PL/SQL procedure successfully completed.
client_id_trace_enable函数 execute dbms_monitor.client_id_trace_enable ( client_id =>'client_id', waits => (TRUE|FALSE), binds => (TRUE|FALSE) ); 禁用跟踪 execute dbms_monitor.client_id_trace_disable ( client_id =>'client_id'); 缺省情况下跟踪对于等待为true,绑定变量为false 下面通过使用dbms_session.set_identifier过程来设置client_identifier SQL> execute dbms_session.set_identifier('JY'); PL/SQL procedure successfully completed.
可以通过两种方法来找到客户端标识 SQL> select sys_context('USERENV','CLIENT_IDENTIFIER') client_id from dual; CLIENT_ID -------------------------------------------------------------------------------- JY 2.从不同的会话中 SQL> select client_identifier client_id from v$session where sid =18; CLIENT_ID ---------------------------------------------------------------- JY 对客户端标识为'JY'的所有会话启用跟踪 SQL> execute dbms_monitor.client_id_trace_enable('JY'); PL/SQL procedure successfully completed. 现在这个跟踪可以跨越数据库的关闭 SQL> select trace_type, primary_id, qualifier_id1, waits, binds from dba_enabled_traces; TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 WAITS BINDS --------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----- ----- CLIENT_ID JY TRUE FALSE 为了禁用跟踪,执行以下命令: SQL> execute dbms_monitor.client_id_trace_disable('JY'); PL/SQL procedure successfully completed. SQL> select trace_type, primary_id, qualifier_id1, waits, binds from dba_enabled_traces; TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 WAITS BINDS --------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----- ----- 这可能造成有时生成多个跟踪文件。例如,当使用共享服务器时,不同的共享服务器进程可以执行SQL语句。这将导致生成多个跟踪文件。对于RAC来说也同样会生成多个跟踪文件。后面将介绍如何使用trcsess工具将多个跟踪文件合并成一个跟踪文件。
SERV_MOD_ACT_TRACE_ENABLE函数
通过查询v$session视图的service_name,module和action列可以查看服务名,模块与操作名。语法如下: execute dbms_monitor.serv_mod_act_trace_enable('Service1', 'Module1', 'Action1', waits => (TRUE|FALSE), binds => (TRUE|FALSE), instance_name => 'ORCL' ); 禁用跟踪 execute dbms_monitor.serv_mod_act_trace_disable('Service1', 'Module1', 'Action1'); 缺省情况下跟踪对于等待为true,对于绑变量为false。缺省实例名为null。
示例 SQL> execute dbms_monitor.serv_mod_act_trace_enable('SYS$USERS', 'SQL*Plus' ); PL/SQL procedure successfully completed. 检查是否启用跟踪 SQL> select primary_id, qualifier_id1, waits, binds from dba_enabled_traces where trace_type = 'SERVICE_MODULE'; PRIMARY_ID QUALIFIER_ID1 WAITS BINDS ---------------------------------------------------------------- ---------------------------------------------------------------- ----- ----- SYS$USERS SQL*Plus TRUE FALSE 禁用跟踪 SQL> execute dbms_monitor.serv_mod_act_trace_disable('SYS$USERS', 'SQL*Plus'); PL/SQL procedure successfully completed. SQL> select primary_id, qualifier_id1, waits, binds from dba_enabled_traces where trace_type = 'SERVICE_MODULE'; PRIMARY_ID QUALIFIER_ID1 WAITS BINDS ---------------------------------------------------------------- ---------------------------------------------------------------- ----- -----
使用trcsess合并跟踪文件 trcsess [output=] [session=] [clientid=] [service=] [action=] [module=] 会话1: SQL> execute dbms_session.set_identifier('JY'); PL/SQL procedure successfully completed. SQL> execute dbms_monitor.client_id_trace_enable('JY'); PL/SQL procedure successfully completed. SQL> select 'session 1' from dual; 'SESSION1 --------- session 1 SQL> execute dbms_monitor.client_id_trace_disable('JY'); PL/SQL procedure successfully completed. 会话2: SQL> execute dbms_session.set_identifier('JY'); PL/SQL procedure successfully completed. SQL> execute dbms_monitor.client_id_trace_enable('JY'); PL/SQL procedure successfully completed. SQL> select 'session 2' from dual; 'SESSION2 --------- session 2 SQL> execute dbms_monitor.client_id_trace_disable('JY'); PL/SQL procedure successfully completed. 使用trcsess合并跟踪文件 [oracle@jyrac1 trace]$ trcsess output=trcsess_Jy_Trace.txt clientid='JY' *.trc [oracle@jyrac1 trace]$ ls -lrt *Jy*.txt -rw-r--r-- 1 oracle oinstall 97786 Mar 2 15:17 trcsess_Jy_Trace.txt
dbms_application_info SQL> begin 2 dbms_application_info.set_module(module_name => 'add_employee',action_name => 'insert into emp'); 3 insert into scott.emp (ename, empno, sal, mgr, job, hiredate, comm, deptno ) 4 values ( 'scott', 9998, 1000, 7698,'clerk', sysdate,0, 10); 5 dbms_application_info.set_module(null,null); 6 end; 7 / PL/SQL procedure successfully completed. 下面通过使用module与action列作为查询条件来查询v$sqlarea视图来获得上面执行的SQL语句 SQL> select sql_text from v$sqlarea where module = 'add_employee' and action = 'insert into emp'; SQL_TEXT -------------------------------------------------------------------------------- INSERT INTO SCOTT.EMP (ENAME, EMPNO, SAL, MGR, JOB, HIREDATE, COMM, DEPTNO ) VAL UES ( 'scott', 9998, 1000, 7698,'clerk', SYSDATE,0, 10) 也可以执行以下过程来获得信息 SQL> set serveroutput on SQL> declare 2 l_clinent varchar2(100); 3 l_mod_name varchar2(100); 4 l_act_name varchar2(100); 5 begin 6 dbms_application_info.set_client_info('my client'); 7 dbms_application_info.read_client_info(l_clinent); 8 dbms_output.put_line('client='||l_clinent); 9 dbms_application_info.set_module('my mod','inserting'); 10 dbms_application_info.read_module(l_mod_name,l_act_name); 11 dbms_output.put_line('mod_name='||l_mod_name); 12 dbms_output.put_line('act_name='||l_act_name); 13 end; 14 / client=my client mod_name=my mod act_name=inserting PL/SQL procedure successfully completed |
|