Trace analyzer - reading made easy; compared to tkprof

The following article on how to use TKPROF is useful
http://www.oracleutilities.com/OSUtil/tkprof.html Also have a look at the article on Trace Analyzer

http://www.oracle-base.com/articles/10g/SQLTrace10046TrcsessAndTkprof10g.php

ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
ALTER SESSION SET SQL_TRACE = TRUE;
DBMS_SESSION.SET_SQL_TRACE(TRUE);
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);

select c.value || '\' || lower(d.value)|| '_ora_' ||
to_char(a.spid, 'fm00000')|| '.trc' "TRACE FILE"
from v$process a, v$session b, v$parameter c, v$parameter d
where a.addr = b.paddr
and b.audsid = userenv('sessionid')
and c.name = 'user_dump_dest'
and d.name = 'db_name';

TRACE FILE
---------------------------------------------------------------
C:\oracle9i\admin\ORCL92\udump\ORCL92_ora_03164.trc

user_dump_dest is where the trace files are generated

To disable tracing

ALTER SESSION SET SQL_TRACE = FALSE;
DBMS_SESSION.SET_SQL_TRACE(FALSE);
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,false);


tkprof ORCL92_ora_3043.trc output.txt insert=tkprof.sql record=Allsql.sql

TRCSESS - in 10g
For analyzing and reporting from more than one Trace files at a go.

Prerequisite: dbms_session.set_identifier(‘client_id’ ) and dbms_application_info.set_module(‘module’, ‘action)
*** ACTION NAME:(DAVE_ACTION) 2006-01-10 20:06:22.396
*** MODULE NAME:(DAVE_MODULE) 2006-01-10 20:06:22.396
*** SERVICE NAME:(SYS$USERS) 2006-01-10 20:06:22.396
*** SESSION ID:(148.49932) 2006-01-10 20:06:22.386
*** CLIENT ID:(HERE IS THE CLIENTID) 2006-01-11 07:57:45.135

The following will create a single trace report from all trace files in the cur dir.

$ trcsess output=all.trc module=SQL*Plus *.trc