Oracle : TKPROF simple steps

1. Set the following parameters
TIMED_STATISTICS = TRUE
MAX_DUMP_FILE_SIZE = unlimited (also see metalink article 108723.1)
USER_DUMP_DEST = /oracle/admin/ora9i/udump


2. Enable SQL TRACE for a session
alter session set SQL_TRACE true;


3. Run the query


4.obtain the number included in the trace filename
Select spid, s.sid,s.serial#, p.username, p.program
from v$process p, v$session s
where p.addr = s.paddr
and s.sid = (select sid from v$mystat where rownum=1)

or

Select spid, s.sid,s.serial#, p.username, p.program
from v$process p, v$session s
where p.addr = s.paddr
and s.sid = (select userenv('sid') from dual)


5.Run TKPROF at the command line to put the TRACE file into readable format
tkprof ora_19554.trc rich2.prf explain=system/manager sort=FCHCPU,EXECPU,PRSCPU print=5


Related Posts:
- Alter System Set Events to Generate Trace Files on Ora Error
- Query Optimization Tips for Oracle
- How to Get Execution Plan and Statistics of Query
- DBMS_PROFILER: Overview and How to Install
- SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
- Oracle: DBMS_UTILITY.ANALYZE_SCHEMA
- DBMS_STATS Gather Statistics of Schema, Tables, Indexes
- Oracle: Analyze Table or Index
- Doubt: Truncate Command and Table Statistics