Performance Tuning
Session trace with event 10046
User Rating: / 0
- Details
-
Parent Category: Articles
-
Created on Thursday, 18 March 2010 22:19
-
Last Updated on Thursday, 06 November 2014 12:10
-
Published on Thursday, 18 March 2010 22:19
-
Written by Guy Lambregts
-
Hits: 5601
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
VALUE
------------------------------------ ----------- ------------------------------
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';
SID SERIAL# USERNAME
------------------------------------------
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
declare
us varchar2(30);
begin
select user into us from dual;
if us='BALDRIK' then
execute immediate 'alter session set sql_trace=true';
end if;
end;
/
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.