26 | 06 | 2017
Latest Articles
Popular Articles

Performance Tuning

Trace with DBMS_MONITOR

User Rating:  / 0
PoorBest 

Trace with DBMS_MONITOR

 

See also Session trace with event 10046

 

Oracle 10G has introduced the DBMS_MONITOR as the preferred trace utility.

The DBMS_MONITOR package has a set of procedures for user, service and client identifier tracing. There is an added value for shared server tracing comapred with the older methods. Several trace files can be merged together with $ORACLE_HOME/bin/trcasst before. The regrouped trace files can then be formetted with tkprof.

Today I limit my DBMS_MONITOR work to the procedures SESSION_TRACE_ENABLE and SESSION_TRACE_DISABLE 

 

SQL> exec dbms_monitor.session_trace_enable(523,122,waits=>false,binds=>false);

PL/SQL procedure successfully completed.

 

is the equivalent of the old fashionned

 

SQL > exec sys.dbms_system.set_ev(523,122,10046,1,'');

 

SQL> exec dbms_monitor.session_trace_enable(523,122,waits=>true,binds=>false);

PL/SQL procedure successfully completed.

 

is the equivalent of the old fashionned

 

SQL > exec sys.dbms_system.set_ev(523,122,10046,8,'');

 

SQL> exec dbms_monitor.session_trace_enable(523,122,waits=>false,binds=>true);

PL/SQL procedure successfully completed.

 

is the equivalent of the old fashionned

 

SQL > exec sys.dbms_system.set_ev(523,122,10046,4,'');

 

SQL> exec dbms_monitor.session_trace_enable(523,122,waits=>true,binds=>true);

PL/SQL procedure successfully completed.

 

is the equivalent of the old fashionned

SQL > exec sys.dbms_system.set_ev(523,122,10046,12,'');

 

We stop tracing with

 

SQL> exec dbms_monitor.session_trace_disable(523,122);

 

From Oracle 11G R1 onwards the PLAN_STAT argument can be given. Possible input value for PLAN_STAT are NEVER, FIRST_EXECUTION (equivalent to NULL ) or ALL_EXECUTIONS


SQL> exec dbms_monitor.session_trace_enable(523,122,waits=>true,binds=>true, plan_stat => 'FIRST_EXECUTION');

PL/SQL procedure successfully completed.