Introduction to Oracle Trace Utility

Introduction to Oracle Trace Utility and Understanding The Fundamental Performance Equation
copied from http://tonguc.wordpress.com/2006/12/30/introduction-to-oracle-trace-utulity-and-understanding-the-fundamental-performance-equation/

-- it is better to make a new connection before you start a trace each time, if not information
-- will be appended if any trace has already been started
-- config section, please refer the http://www.ubtools.com/ubtools/products/itrprof/itrprof_user_manual.html
-- acronym meanings, please refer http://www.gplivna.eu/papers/otrace.htm

conn hr/hr

-- 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
exec dbms_system.ksdddt -- prints out wall clock value

-- show parameters
-- in order to seperate your produced trace file easily from the others at user_dump_dest folder of Oracle
alter session set tracefile_identifier = 'tracefilename' ;

-- start tracing from this session

--specified Events number and level, please refer http://www.psoug.org/reference/trace_tkprof.html
Alter session set SQL_Trace = true ;
-- ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';
-- ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

-- 或者你想trace某个特定的session, 可以先查的这个sessionID, serial#, 然后调用语句DBMS_System.Set_Sql_Trace_In_Session()来开启trace

--select sid,serial# from v$session where audsid= userenv('SESSIONID');
--EXEC DBMS_System.Set_Sql_Trace_In_Session(sid, serial#, true )

-- also these can be used for tracing
-- execute DBMS_SESSION.SET_SQL_TRACE ({true|false});
-- execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (session_id, serial_id, {true|false});
-- execute DBMS_SYSTEM.SET_EV(session_id, serial_id, 10046, level, ");
-- for more information; http://www.ubtools.com/ubtools/products/itrprof/itrprof_user_manual.html
--for more information; http://www.gplivna.eu/papers/oracle_trace.htm
-- Run the application that you want to trace, any SQL(s) or any PL/SQL block(s)
select sysdate, user from dual;

-- stop tracing
Alter session set SQL_Trace = false ;
-- ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
-- ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

-- Than go to USER_DUMP_DEST to pick up the trace file. A query that gives your trace file name generated from your session
-- in windows without the tracefile_identifier would be as follows
--
-- select c.value || '\' || d.instance_name || '_ora_' || a.spid || '.trc' trace_file_is_here
-- from v$process a, v$session b, v$parameter c, v$instance d
-- where a.addr = b.paddr
-- and b.audsid = userenv('sessionid')
-- and c.name = 'user_dump_dest' ;
--
-- Format the trace file with TKPROF and interpret the output.
-- $ tkprof tracefile.trc output.txt [set the options as you like]
-- tkprof D:\oraclexee\app\oracle\admin\XE\udump\xe_ora_2220_bind.trc D:\temp\xe_ora_2220_bind.txt explain=hr/hr sys=yes waits=yes sort=prscpu 

This will create a formatted file named trace.txt from the data captured in the .trc file. You can then review the trace.txt file to see the SQL statements, execution plans and counts for statement parses, executes and fetches.
Syntax of TKPROF

TKPROF is run from the operating system prompt. The syntax is:

tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n]
[aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table]
[explain=user/password] [record=filename4] [width=n]

TKProf Usage:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm
http://www.psoug.org/reference/trace_tkprof.html