TKPROF - Oracle

TKPROF is the basic performance diagnostic tool that can help you
monitor and tune applications running against the Oracle Server.
 
Usage: tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ] table=schema.tablename   Use 'schema.tablename' with 'explain=' option. explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN. print=integer    List only the first 'integer' SQL statements. aggregate=yes|no insert=filename  List SQL statements and data inside INSERT statements. sys=no           TKPROF does not list SQL statements run as user SYS. record=filename  Record non-recursive statements found in the trace file. waits=yes|no     Record summary for any wait events found in the trace file.
sort=option      Set of zero or more of the following sort options:
    prscnt  number of times parse was called
    prscpu  cpu time parsing
    prsela  elapsed time parsing
    prsdsk  number of disk reads during parse
    prsqry  number of buffers for consistent read during parse
    prscu   number of buffers for current read during parse
    prsmis  number of misses in library cache during parse
    execnt  number of execute was called
    execpu  cpu time spent executing
    exeela  elapsed time executing
    exedsk  number of disk reads during execute
    exeqry  number of buffers for consistent read during execute
    execu   number of buffers for current read during execute
    exerow  number of rows processed during execute
    exemis  number of library cache misses during execute
    fchcnt  number of times fetch was called
    fchcpu  cpu time spent fetching
    fchela  elapsed time fetching
    fchdsk  number of disk reads during fetch
    fchqry  number of buffers for consistent read during fetch
    fchcu   number of buffers for current read during fetch
    fchrow  number of rows fetched
    userid  userid of user that parsed the cursor

Trace Output

SQL STATEMENTS PHASES

• Parse


• Execute


• Fetch

        count   cpu     elap    disk    query   current   rows

Parse:      1     1        2       0        0         0
Execute:    1     0        0       0        0         2      0
Fetch:      2    69      113     142      430         0     36

The output shows 142 disk reads and 430 memory reads (query + current). Having such a high number of disk reads compared to physical reads is certainly a potential problem. The execution path shows a full table scan confirming that we may have a potential problem.

count

The number of times this type of call was made.

cpu

The total CPU time for all of the calls of this type for this statement. If the TIMED_STATISTICS parameter in the init.ora is not set to TRUE, this statistic and the elapsed statistic will be 0.

elapsed

The total elapsed time for this call.

disk

The total number of data blocks retrieved from disk to satisfy this call.

query

The total number of data buffers retrieved from memory for this type SELECT statements usually retrieve buffers in this mode.

current

The total number of data buffers retrieved from memory for this type of call. UPDATE, INSERT, or DELETE the usual access buffers in this mode.

rows

The total number of rows processed by this statement. The rows statements will appear in the row of Fetch statistics. INSERTS, UPDATES, and DELETES will appear in the execute row.