26 | 06 | 2017
Latest Articles
Popular Articles

Performance Tuning

Session trace with event 10046

User Rating:  / 0

Trace with event 10046


See also Trace with DBMS_MONITOR 


I use sqltrace at the session level very often in order


1. to capture the problematic sql causing transaction failures, errors

2. to capture the problematic sql causing slow performance.


However sql trace will likely not harm your database the site-effects of it are


1. performance decrease

2. huge trace files can be generated


As such I only use it when necesarry, Do no put the whole database in trace mode ! Trace files can become very big. The size of it can be controlled by the instance parameter max_dump_file_size. Tracing is normally done by system, trace files were generated in the user dump directory ( < 11 G ) or in the diagnostic directory ( 11G R1 onwards ) and can be formatted with tkprof


SQL> show release

release 1001000400

SQL> show parameter user_dump_dest

NAME                                 TYPE        

------------------------------------ ----------- ------------------------------

user_dump_dest                       string      C:\ORACLE\ADMIN\UTF8\UDUMP


SQL> show release

release 1201000200

SQL> show parameter diagnostic

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /u01/app/oracle



Assume I have to trace the session of user Baldrik 


SQL> select sid,serial#,username from v$session where username='BALDRIK';



162 63         BALDRIK

SQL> exec sys.dbms_system.set_ev(162,63,10046,8,'');

PL/SQL procedure successfully completed.


In rare cases I need to trace the session as soon as the user will connect but right now the user is not yet connected, short after connection the user got disconnected, I don' t know why and I don' t have the time to wait for the phone call, picking up the session' s sid & serial# and to start tracing. This is a way how I already solved this issues; I create an after logon on database trigger.

Take care with this one' s. Try to disable them as soon as you reached your goal, 


create or replace trigger session_trace
after logon on database
us varchar2(30);
select user into us from dual;
if us='BALDRIK' then
execute immediate 'alter session set sql_trace=true';
end if;

Trigger created.


Disable this trigger as soon as you have the trace file, otherwise the session will be trace traced every time Baldriks connects (which is very likely not wanted)

SQL> alter trigger session_trace disable;

Trigger altered.


If you want to trace your own session you can use


SQL> alter session set events='10046 trace name context forever,level 1';
Session altered.


Run the sql you would like to trace, possible trace levels are


1 : Perfomance Metrics and execution plan metrics for 10G all executiions ( 10G ) or execution plan metrics only for the first execution ( 11G )

4 :   level 1 + bind variable info 

8 :   level 1 + wait event info

12 : level 4 + level 8

16 : level 1 + execution plan metrics for all the executions ( 11G )

64 : level 1 + execution plan metrics for first execution and for all executions if DB  time > 1 min ( 11G R2 )

76 : level 12 + level 64  


You can stop the session trace with

SQL> alter session set events='10046 trace name context off';
Session altered.