Thursday, July 24, 2008

EVENT 10046 "enable SQL statement tracing"

 

For details of interpreting 10046 output see: Note 39817.1

SQL> alter session set timed_statistics=true;

Session altered.

SQL> alter session set max_dump_file_size=unlimited;


Session altered.

SQL> alter session set tracefile_identifier='robyn';
Session altered.

SQL>
SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

 

--- Activate Trace, Option # 1


SQL> select sid,serial# from v$session;

       SID    SERIAL#
---------- ----------
       141      42444
       142      17668
       145      50777
       146      12751
       147      57287
       148          2
       149      56942
       150      13009
       152          3
       153          1
       159       6842
       160          1
       161          1
       162          1
       163          1
       164          1
       165          1
       166          1
       167          1

       SID    SERIAL#
---------- ----------
       168          1
       169          1
       170          1

22 rows selected.

 

****  for session to be traced

SQL>  exec dbms_system.set_ev(
        sid => ##,
        serial => ####,
event => 10046,
level => 12,
null => '')
/* Wait for executing code to complete during this time window */
exec dbms_system.set_ev(
        sid => ##,
        serial => ####,
event => 10046,
level => 0,
null => '');


 


 


Check UDUMP directory for generated trace file.
file name like "orcl10g_ora_22625_robyn.trc"

Disable 10046 tracing.

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

 

No comments:

Post a Comment