Tuning through 10046 Trace

To Enable Trace
Set these initialization parameters for your trace session to guarantee the integrity of the trace file

alter session set max_dump_file_size=unlimited;
ALTER session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;
alter session set “_rowsource_execution_statistics” = true

In order to seperate your produced trace file easily from the others at user_dump_dest folder of Oracle
alter session set tracefile_identifier = SQL_Trace ;

To start tracing from this session
Alter session set SQL_Trace = true ; or
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 8′; or
ALTER SESSION SET EVENTS ‘10053 TRACE NAME CONTEXT FOREVER, LEVEL 1′; or
EXECUTE DBMS_SYSTEM.SET_EV(session_id, serial_id, 10046, level, ”);

Run the application that you want to trace, any SQL(s) or any PL/SQL block(s)
select sysdate, user from dual;

To Disable Trace

Alter session set SQL_Trace = false ;
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT OFF’;
ALTER SESSION SET EVENTS ‘10053 TRACE NAME CONTEXT OFF’;
EXECUTE DBMS_SYSTEM.SET_EV(session_id, serial_id, 10046, 0, ”);


Formatting Your Trace Files
Use the TKPROF command to format your trace files into readable output. This is the TKPROF syntax:
OS> tkprof tracefile outputfile [options]
tracefile Name of the trace output file (input for TKPROF)
outputfile Name of the file to store the formatted results
When the TKPROF command is entered without any arguments, it generates a usage message together with a description of all TKPROF options. See the next slide for a full listing. This is the output that you get when you enter the TKPROF command without any arguments:
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
By default, the .trc file is named after the SPID.