Available methods to enable 10046 trace are described in My Oracle Support Note 376442.1. You can enable 10046-tracing<\/p>\n
– on session level (alter session)
\n– for other sessions (e.g. with oradebug, the package DBMS_MONITOR or DBMS_SYSTEM)<\/p>\n
What is not covered with the methods above is the possibility to trace a specific SQL-statement, which runs “somewhen” in the future on the database. E.g. a SQL, which runs during a next batch job. With the introduction of UTS (Unified Tracing Service) in 11.2., you can actually do exactly that:<\/p>\n
I.e. suppose I need a 10046-trace, level 12 of the SQL with SQL_ID cjrha4bzuupzf, which runs somewhen in the next 24 hours. So what I have to do is to just set the event “sql_trace” for the SQL_ID:<\/p>\n
REMARK: With the introduction of the parameter “_evt_system_event_propagation” in 11g (default is TRUE) the event-settings of “alter system set events”-commands are also propagated to existing sessions.<\/p>\n Let’s see if only the statement in question is being traced. From another session I’m doing the following: Below is the content of the produced trace file:<\/p>\n So, as expected, only the SQL with SQL_ID cjrha4bzuupzf has been traced.<\/p>\n To switch off the setting for SQL_ID cjrha4bzuupzf do the following:<\/p>\n
\nSQL> alter system set events 'sql_trace[sql: cjrha4bzuupzf] level=12';
\n<\/code><\/p>\n
\nREMARK: I actually want to trace the statement with the GATHER_PLAN_STATISTICS-hint.<\/p>\n
\nSQL> select \/* BEFORE TRACE *\/ count(*) from t1 where object_type='INDEX';
\n
\n COUNT(*)
\n----------
\n 1432
\n
\nSQL> select \/*+ GATHER_PLAN_STATISTICS *\/ count(*) from t1 where object_type='INDEX';
\n
\n COUNT(*)
\n----------
\n 1432
\n
\nSQL> select \/*+ AFTER TRACE *\/ count(*) from t1 where object_type='INDEX';
\n
\n COUNT(*)
\n----------
\n 1432
\n
\nSQL> select value from v$diag_info where name = 'Default Trace File';
\n
\nVALUE
\n--------------------------------------------------------------------
\nD:APPCBLdiagrdbmsgen11204gen11204tracegen11204_ora_11552.trc
\n<\/code><\/p>\n
\n=====================
\nPARSING IN CURSOR #305977200 len=79 dep=0 uid=42 oct=3 lid=42 tim=628480230944 hv=4289550318 ad='7ff95ed9ed80' sqlid='cjrha4bzuupzf'
\nselect \/*+ GATHER_PLAN_STATISTICS *\/ count(*) from t1 where object_type='INDEX'
\nEND OF STMT
\nEXEC #305977200:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3724264953,tim=628480230943
\nWAIT #305977200: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=15671 tim=628480231942
\nFETCH #305977200:c=0,e=652,p=0,cr=189,cu=0,mis=0,r=1,dep=0,og=1,plh=3724264953,tim=628480232614
\nSTAT #305977200 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=189 pr=0 pw=0 time=652 us)'
\nSTAT #305977200 id=2 cnt=1432 pid=1 pos=1 obj=15671 op='TABLE ACCESS FULL T1 (cr=189 pr=0 pw=0 time=622 us cost=56 size=10024 card=1432)'
\nFETCH #305977200:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3724264953,tim=628480232838
\nWAIT #305977200: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=15671 tim=628480232858
\n*** 2016-02-05 15:11:21.578
\nWAIT #305977200: nam='SQL*Net message from client' ela= 1336677 driver id=1111838976 #bytes=1 p3=0 obj#=15671 tim=628481569546
\nCLOSE #305977200:c=0,e=8,dep=0,type=0,tim=628481569645
\n<\/code><\/p>\n