Collect 10046 Trace SQL TRACE to diagnose performance issues

Collect 10046 Trace SQL_TRACE to diagnose performance issues
MOS ID: 376442.1

- 11g R1+ SQL> show parameter diagnostic_dest
 * Trace file location

- Pre 11g R1 SQL> show parameter user_dump_dest

alter session set tracefile_identifier='10046';
 * Session Tracing
 * Before query starts

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 table; exit

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

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)
 * After query already started

connect / as sysdba oradebug setospid [SPID] oradebug unlimit oradebug event 10046 trace name context forever,level 12

or

connect / as sysdba oradebug setorapid [PID] oradebug unlimit oradebug event 10046 trace name context forever,level 12 oradebug event 10046 trace name context off