Oracle‎ > ‎Performance Tuning‎ > ‎

Tracing

10046 Trace

10046 trace tells you exactly what Oracle is doing, how long it took to do it, and what each step was waiting for. It is the trace used to feed analysis tools such as Oracle's tkprof, Trace Analyzer, and Method-R's Profiler.

alter session set tracefile_identifier='seiler10046';
alter session set events '10046 trace name context forever,level 12';

-- Execute the queries or operations to be traced here --

alter session set events '10046 trace name context off';

Setting the tracefile_identifier adds that string into the file name of the generated trace file, making it easier to find.

For methods that require tracing levels the following are valid values.

  • 0 - No trace. Like switching sql_trace off.
  • 2 - The equivalent of regular sql_trace.
  • 4 - The same as 2, but with the addition of bind variable values.
  • 8 - The same as 2, but with the addition of wait events.
  • 12 - The same as 2, but with both bind variable values and wait events.
See also:

10053 Trace

This is used to obtain an optimizer trace to tell us why the CBO chose the plan that it did.

alter session set tracefile_identifier='seiler10053';
alter session set events '10053 trace name context forever,level 1';

-- Execute the queries or operations to be traced here --
-- Note that you can run 10053 trace just by calling explain for a query --

alter session set events '10053 trace name context off';

Tracing Other Sessions

For example, to get a 10046 of the MRP0 session in a DataGuard standby:

ps -ef|grep mrp0 # get the OS PID of the MRP0 process
sqlplus '/ as sysdba'
oradebug setospid <PID of MRP0 process>;
oradebug unlimit;
oradebug event 10046 trace name context forever, level 12;
-- leave this running for 5 minutes or so and then turn it off again using
exec dbms_lock.sleep(300);
oradebug event 10046 trace name context off;
oradebug tracefile_name;
exit;
Comments