Collect 10046 Trace SQL TRACE to diagnose performance issues

Collect 10046 Trace SQL_TRACE to diagnose performance issues

MOS ID: 376442.1

• Trace file location

- 11g R1+ SQL> show parameter diagnostic_dest

- Pre 11g R1 SQL> show parameter user_dump_dest

• Session Tracing
  • Before query starts

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

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

• • After query already started

  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) 


  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