1. Start session trace
To start a SQL trace for the current session, execute:
ALTER SESSION SET sql_trace = true;
You can also add an identifier to the trace file name for later identification:
ALTER SESSION SET sql_trace = true;
ALTER SESSION SET tracefile_identifier = mysqltrace;
2. Stop session trace
To stop SQL tracing for the current session, execute:
ALTER SESSION SET sql_trace = false;
3. Go to udump directory and do :
Your udump is generally located in ORACLE_BASE/admin/<sid>/udump
$ls -ltrh
The last file with mysqltrace tagged to its endis your session trace file
4. TKPROF the trace file
TKProf is an Oracle database utility used to format SQL Trace output into human readable format. The TKProf executable is located in the ORACLE HOME/bin directory.
tkprof input.trc output.prf [options]
eg: $ tkprof orcl102_ora_3064.trc output.prf EXPLAIN=scott/tiger SYS=NO
Sample output
Tkprof: Release 9.2.0.1.0 – Production on Tue Dec 24 15:32:43 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: ORCL102_ora_3064.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
select *
from
employee where emp_id = 3737
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 10 0.00 0.03 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 20 0.34 0.35 72 4730 0 10
——- —— ——– ———- ———- ———- ———- ———-
total 40 0.34 0.39 72 4730 0 10
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
Rows Row Source Operation
——- —————————————————
1 TABLE ACCESS FULL EMPLOYEE