Available methods to enable 10046 trace are described in My Oracle Support Note 376442.1. You can enable 10046-tracing
– on session level (alter session)
– for other sessions (e.g. with oradebug, the package DBMS_MONITOR or DBMS_SYSTEM)
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:
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:
SQL> alter system set events 'sql_trace[sql: cjrha4bzuupzf] level=12';
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.
Let’s see if only the statement in question is being traced. From another session I’m doing the following:
REMARK: I actually want to trace the statement with the GATHER_PLAN_STATISTICS-hint.
SQL> select /* BEFORE TRACE */ count(*) from t1 where object_type='INDEX';
SQL> select /*+ GATHER_PLAN_STATISTICS */ count(*) from t1 where object_type='INDEX';
SQL> select /*+ AFTER TRACE */ count(*) from t1 where object_type='INDEX';
SQL> select value from v$diag_info where name = 'Default Trace File';
Below is the content of the produced trace file:
PARSING IN CURSOR #305977200 len=79 dep=0 uid=42 oct=3 lid=42 tim=628480230944 hv=4289550318 ad='7ff95ed9ed80' sqlid='cjrha4bzuupzf'
select /*+ GATHER_PLAN_STATISTICS */ count(*) from t1 where object_type='INDEX'
END OF STMT
WAIT #305977200: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=15671 tim=628480231942
STAT #305977200 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=189 pr=0 pw=0 time=652 us)'
STAT #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)'
WAIT #305977200: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=15671 tim=628480232858
*** 2016-02-05 15:11:21.578
WAIT #305977200: nam='SQL*Net message from client' ela= 1336677 driver id=1111838976 #bytes=1 p3=0 obj#=15671 tim=628481569546
So, as expected, only the SQL with SQL_ID cjrha4bzuupzf has been traced.
To switch off the setting for SQL_ID cjrha4bzuupzf do the following:
SQL> alter system set events 'sql_trace[sql: cjrha4bzuupzf] off';
The event settings done with “alter system set events …” are not persistent settings. I.e. after a next restart of the instance the event is no longer active. To set the event persistently you would have to set it in the spfile as well:
SQL> alter system set event='sql_trace[sql: cjrha4bzuupzf] level=12' scope=spfile;
If you want to see if an event is currently active on the running instance do the following:
SQL> oradebug setmypid
SQL> oradebug eventdump session
sql_trace[sql: cjrha4bzuupzf] level=12
If you have several events set in your spfile then it’s difficult to remove a single one of them, because the events are stored concatenated with a “:” as a single event. You can just overwrite the current setting or remove them all:
SQL> alter system reset event scope=spfile;
REMARK: Do not set events (except event 10046) without the instruction from Oracle Support to do so. I also do recommend to NOT set event 10046 in the spfile.
Update on the 18th of December 2017: The tracing of a specific SQL is broken in 12.2. Thanks to Nenad Noveljic who discovered that (see the Comment section below). The issue seems caused by the bugs 25989066 and 25994378 which are fixed in 18.1. More details on the issue in 12.2. can be found in Nenad’s Blog.