Search This Blog

Friday, May 2, 2008

Various options with TKPROF

The post is in continuation to SQL Trace and TKPROF

Syntax of TKPROF

tkprof [filename1] [filename2] [WAITS] [SORT] [PRINT]
There are few more but these are more commonly used.

Argument Description
filename1 : The trace file name
filename2 : Filename to which TKPROF writes formatted output
WAITS : Flag to record summary for any wait events found in the trace file. Values are YES or NO
SORTS : Sorts traced SQL statements in descending order of specified sort option before listing them into the output file
PRSCNT Number of times parsed.
PRSCPU CPU time spent parsing.
PRSELA Elapsed time spent parsing.
PRSDSK Number of physical reads from disk during parse.
PRSQRY Number of consistent mode block reads during parse.
PRSCU Number of current mode block reads during parse.
PRSMIS Number of library cache misses during parse.
EXECNT Number of executes.
EXECPU CPU time spent executing.
EXEELA Elapsed time spent executing.
EXEDSK Number of physical reads from disk during execute.
EXEDSK Number of physical reads from disk during execute.
EXEQRY Number of consistent mode block reads during execute.
EXECU Number of current mode block reads during execute.
EXEROW Number of rows processed during execute.
EXEMIS Number of library cache misses during execute.
FCHCNT Number of fetches.
FCHCPU CPU time spent fetching.
FCHELA Elapsed time spent fetching.
FCHDSK Number of physical reads from disk during fetch.
FCHQRY Number of consistent mode block reads during fetch.
FCHCU Number of current mode block reads during fetch.
FCHROW Number of rows fetched.

PRINT : Number of SQL statements to be displayed

Example 1
If you are processing a large trace file using a combination of SORT parameters and the PRINT parameter, then you can produce a TKPROF output file containing only the highest resource-intensive statements. For example, the following statement prints the 10 statements in the trace file that have generated the most physical I/O:

tkprof abcd53269.trc abcd53269.txt SORT = (PRSDSK, EXEDSK, FCHDSK) PRINT = 10


Example 2
Print the SQL Query taking maximum CPU Usage
tkprof abcd53269.trc abcd53269.txt SORT = (EXECPU,FCHCPU) PRINT = 1

1 Comment:

uma said...

Hi suresh
please assist on the following issue.
in our oracle apps system the system is working dead slow for last few days and it is generating lot of trace files automatically and the whole process become slow..
how to find out the trace enabled in the form level /report level and how to solve the problem?
due to this issue, the business process became slow..
please assist on the same
thanks
umapathy
my mail id is
umapathy1549@gmail.com

Copyright (c) All rights reserved. Presented by Suresh Vaishya