How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues

Session Tracing

alter session set tracefile_identifier='10046';

alter session set timed_statistics = true;

alter session set statistics_level=all;

alter session set max_dump_file_size = unlimited;

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

-- Execute the queries or operations to be traced here --

select * from dual;

exit;

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

Tracing a process after it has started

The first step is to identify the session to be traced by some means:

select p.PID,p.SPID,s.SID

from v$process p,v$session s

where s.paddr = p.addr

and s.sid = &SESSION_ID

/

SPID is the operating system Process identifier (os pid)

PID is the Oracle Process identifier (ora pid)

Once the OS process id for the process has been determined then the trace can be initialised as follows:

connect / as sysdba

oradebug setospid 9834

oradebug unlimit

oradebug event 10046 trace name context forever,level 12

oradebug event 10046 trace name context off

Instance wide tracing

alter system set events '10046 trace name context forever,level 12';

alter system set events '10046 trace name context off';

Via a Logon Trigger

CREATE OR REPLACE TRIGGER SYS.set_trace

AFTER LOGON ON DATABASE

WHEN (USER like '&USERNAME')

DECLARE

lcommand varchar(200);

BEGIN

EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';

EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';

EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';

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

END set_trace;

/

References:

How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues (Doc ID 376442.1)