10046 trace a module using logon trigger

1) Update – thanks to Joaquin Gonzalez for pointing out a preferable safer method than logon trigger for 10046 tracing a particular module:

EXEC DBMS_MONITOR.serv_mod_act_stat_enable ( service_name => 'myservice' , module_name => 'myModule.exe' , binds => TRUE )

You can verify service_name is what you expect by checking the column of the same name in gv$sql.

To switch that off once you are done:

EXEC DBMS_MONITOR.serv_mod_act_stat_disable ( service_name => 'myservice' , module_name => 'myModule.exe' )

2) To generate 10046 traces just for a particular SQL statement, regardless of when it runs or who runs it:

ALTER SYSTEM SET EVENTS 'sql_trace [sql: sql_id=cnju0jr4y2ujs] bind=true , wait=true' ;

And to switch that off:

ALTER SYSTEM SET EVENTS 'sql_trace off' ;

H/t Julian Dyke.

3) The less preferable option of using a logon trigger to 10046 trace a particular module is:

CREATE OR REPLACE TRIGGER sys.set_trace
AFTER LOGON ON DATABASE
WHEN ( USER != 'SYS' )
BEGIN
   IF SYS_CONTEXT ( 'userenv' , 'module' ) LIKE 'myModule.exe%'
   THEN
      DBMS_MONITOR.session_trace_enable ( binds => TRUE ) ;
   END IF ;
END set_trace ;
/

For versions 10gR2 and below, replace the dbms_monitor line above with the older style syntax:

EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''' ;

Drop the logon trigger once you have collected the information you need, session tracing does add an overhead, especially when recording bind values as above.

Posted in Performance tuning
One comment on “10046 trace a module using logon trigger
  1. Joaquin Gonzalez says:

    in 10g, why do you need a trigger for that? Better just use DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE

Leave a Reply

Your email address will not be published. Required fields are marked *

*