Performance Tuning
Trace with DBMS_MONITOR
User Rating: / 0
- Details
-
Parent Category: Articles
-
Created on Thursday, 18 March 2010 22:21
-
Last Updated on Thursday, 06 November 2014 12:09
-
Published on Thursday, 18 March 2010 22:21
-
Written by Guy Lambregts
-
Hits: 6079
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.