从10g开始,可以使用dbms_monitor开启/关闭会话跟踪。
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.会话级设置跟踪
#找到会话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.根据客户端标识设置会话跟踪
#设置客户端标识符
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视图,可以看到启用的跟踪和收集的统计信息。可以使用这些视图确保已经禁用的所有跟踪选项。