SQL Trace in Oracle : Using TKPROF

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s