dbms_monitor开启/关闭会话跟踪

从10g开始，可以使用dbms_monitor开启/关闭会话跟踪。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

sql> desc dbms_monitor procedure client_id_stat_disable  argument name                  type                    in/out default?  ------------------------------ ----------------------- ------ --------  client_id                      varchar2                in procedure client_id_stat_enable  argument name                  type                    in/out default?  ------------------------------ ----------------------- ------ --------  client_id                      varchar2                in procedure client_id_trace_disable  argument name                  type                    in/out default?  ------------------------------ ----------------------- ------ --------  client_id                      varchar2                in procedure client_id_trace_enable  argument name                  type                    in/out default?  ------------------------------ ----------------------- ------ --------  client_id                      varchar2                in  waits                          boolean                 in     default  binds                          boolean                 in     default  plan_stat                      varchar2                in     default procedure database_trace_disable  argument name                  type                    in/out default?  ------------------------------ ----------------------- ------ --------  instance_name                  varchar2                in     default procedure database_trace_enable  argument name                  type                    in/out default?  ------------------------------ ----------------------- ------ --------  waits                          boolean                 in     default  binds                          boolean                 in     default  instance_name                  varchar2                in     default  plan_stat                      varchar2                in     default procedure serv_mod_act_stat_disable  argument name                  type                    in/out default?  ------------------------------ ----------------------- ------ --------  service_name                   varchar2                in  module_name                    varchar2                in  action_name                    varchar2                in     default procedure serv_mod_act_stat_enable  argument name                  type                    in/out default?  ------------------------------ ----------------------- ------ --------  service_name                   varchar2                in  module_name                    varchar2                in  action_name                    varchar2                in     default procedure serv_mod_act_trace_disable  argument name                  type                    in/out default?  ------------------------------ ----------------------- ------ --------  service_name                   varchar2                in  module_name                    varchar2                in     default  action_name                    varchar2                in     default  instance_name                  varchar2                in     default procedure serv_mod_act_trace_enable  argument name                  type                    in/out default?  ------------------------------ ----------------------- ------ --------  service_name                   varchar2                in  module_name                    varchar2                in     default  action_name                    varchar2                in     default  waits                          boolean                 in     default  binds                          boolean                 in     default  instance_name                  varchar2                in     default  plan_stat                      varchar2                in     default procedure session_trace_disable  argument name                  type                    in/out default?  ------------------------------ ----------------------- ------ --------  session_id                     binary_integer          in     default  serial_num                     binary_integer          in     default procedure session_trace_enable  argument name                  type                    in/out default?  ------------------------------ ----------------------- ------ --------  session_id                     binary_integer          in     default  serial_num                     binary_integer          in     default  waits                          boolean                 in     default  binds                          boolean                 in     default  plan_stat                      varchar2                in     default   sql>

 

1.会话级设置跟踪

?

1 2 3 4 5 6 7 8 9 10 11

#找到会话sid select sid,serial#,username from v$session;   #开启跟踪（备注：四个参数都是可选参数，不带任何参数跟踪的是当前会话，跟踪结束后要关闭跟踪） exec dbms_monitor.session_trace_enable(session_id=>190,serial_num=>8351,waits=>true,binds=>true);   #检查会话是否被跟踪 select sql_trace,sql_trace_waits,sql_trace_binds from v$session where sid=190;   #关闭跟踪 exec dbms_monitor.session_trace_disable(session_id=>190,serial_num=>8351);

　　

2.根据客户端标识设置会话跟踪

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

#设置客户端标识符 SQL> exec dbms_session.set_identifier('abce_id');   SQL> select sid,serial#,client_identifier from v$session where client_identifier is not null;          SID    SERIAL# CLIENT_IDENTIFIER ---------- ---------- ----------------------------------------        190       8355 abce_id   SQL>    #客户端标识符设置跟踪 SQL> exec dbms_monitor.client_id_trace_enable(client_id=>'abce_id',waits=>true,binds=>false); SQL> select primary_id as client_id,waits,binds from dba_enabled_traces where trace_type='CLIENT_ID';   CLIENT_ID                                                        WAITS BINDS ---------------------------------------------------------------- ----- ----- abce_id                                                          TRUE  FALSE   SQL> #关闭跟踪 SQL> exec dbms_monitor.client_id_trace_disable(client_id=>'abce_id');

 

3.模块级、数据库级监控(略，具体查看上面的定义)

 

4.跟踪视图
查看dba_enabled_traces和dba_enabled_aggregations视图，可以看到启用的跟踪和收集的统计信息。可以使用这些视图确保已经禁用的所有跟踪选项。