Oracle traces including 10053 and 10046 trace

Running various oracle traces including 10053 and 10046 trace file

 

10053 trace file generation

trace 10053 enables you to give insight about the cost-based optimizer internal calculation. You can use the trace file to look at cardinality, selectivity and draw a good parallel with the way cost of table, index or sort etc may be calculated.

Below is the simple method to generate 10053 trace file using SQL*PLUS tool

SQL> alter session set max_dump_file_size=unlimited;
Session altered.

SQL> alter session set tracefile_identifier='RAF_OPT';
Session altered.

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

SQL> explain plan for select * from XX_ALL_OBJECTS where owner='X';
Explained.

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

SQL> exit

the trace file will be generated in the trace directory of diagnostics dest location (in 11g).

10046 trace file genearion

Event 10046 is the standard method of gathering extended sql_trace information for Oracle sessions.

1. trace a particular session

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;
alter session set events '10046 trace name context off';
exit;

2. Tracing a process after it has started

For example, in SQL*Plus, start a session to find the OS process id (spid) for the target session:

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)

If you do not know the Session ID then you can use a select similar to the following to help you identify the target session:

column line format a79
set heading off
select 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||''' '||
  s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
from v$session s , v$process p
where p.addr = s.paddr
and s.username <> ' ';

Document For multi threaded processes in 12c, use newly added column stid from v$process to find specific thread, as Oracle combines many processes into a single ospid.  To find the specific thread, use following sytax:

oradebug setospid <spid> <stid>

Lets assume that the process to be traced has an os pid of 9834.
Login to SQL*Plus as a dba and execute the following:

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

Remember to replace the example '9834' value with the actual os pid.

The tracefile name will be something like <instance><spid>_<stid>.trc