示例
[oracle@dex trace]$ ps -aef | grep oracledex
oracle 6750 1 0 Apr05 00:00:01 oracledex (LOCAL=NO)
oracle 7785 1 0 Apr05 00:00:00 oracledex (LOCAL=NO)
oracle 20482 1 0 09:37 00:00:00 oracledex (LOCAL=NO)
oracle 20730 20727 0 09:53 00:00:00 oracledex (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 20752 20746 0 09:54 00:00:00 oracledex (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 20757 9610 0 09:54 pts/3 00:00:00 grep oracledex
sys@DEX11g> alter system set events 'sql_trace {process:20752} level 12';
System altered.
dexter@DEX11g> select * from tab ;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
RUN_STATS TABLE
STATS VIEW
T TABLE
TS TABLE
sys@DEX11g> alter system set events 'sql_trace {process:20752} off';
System altered.
已知sql_id对其进行跟踪
sql_trace
用法
alter system set events 'sql_Trace[sql:&sql_id] level 12' ;
alter system set events 'sql_Trace[sql:&sql_id] off' ;
示例
sys@DEX11g> select sql_text , sql_id from v$sql where sql_textlike '%from t ';
SQL_TEXT SQL_ID
-------------------------------------------------------------------------
selectmin(object_id) from t gp2gyxwx140jx
select count(*) from t 45vdc2q5hs1f3
select count(*) from t 45vdc2q5hs1f3
select avg(object_id) from t 1xbrzw9w1m9rf
select avg(object_id) from t 1xbrzw9w1m9rf
select sum(object_id) from t 3jpp2an783paa
select sum(object_id) from t 3jpp2an783paa
7 rows selected.
sys@DEX11g> alter system set events 'sql_Trace[sql:&sql_id]level 12' ;
Enter value for sql_id: gp2gyxwx140jx
old 1: alter system setevents 'sql_Trace[sql:&sql_id] level 12'
new 1: alter system setevents 'sql_Trace[sql:gp2gyxwx140jx] level 12'
System altered.
dexter@DEX11g> select min(object_id) from t ;
MIN(OBJECT_ID)
--------------
2
sys@DEX11g> alter system set events 'sql_Trace[sql:&sql_id]off' ;
Enter value for sql_id: gp2gyxwx140jx
old 1: alter system setevents 'sql_Trace[sql:&sql_id] off'
new 1: alter system setevents 'sql_Trace[sql:gp2gyxwx140jx] off'
System altered.
注：
因为trace文件是和session关联的，所以不同session执行sql_id为gp2gyxwx140jx的语句的时候，会生成多个trace文件。
当关闭sql_id的trace的时候，对已经连接的session来说，不受影响（还是会生成trace信息），对于新建立连接的session生效。