Tuesday, August 23, 2011

Interpreting TKPROF Output:


We shall see the below output and understand each bit of information of it one by one:

select * 
from
 tab

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        6      0.04       0.04         21       1884          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.04       0.04         21       1884          0           6

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54  

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  NESTED LOOPS OUTER (cr=628 pr=21 pw=0 time=25030 us)
      2   TABLE ACCESS FULL OBJ$ (cr=621 pr=20 pw=0 time=21996 us)
      2   TABLE ACCESS CLUSTER TAB$ (cr=7 pr=1 pw=0 time=3051 us)
      2    INDEX UNIQUE SCAN I_OBJ# (cr=4 pr=1 pw=0 time=3033 us)(object id 3)

CALL Value
Meaning
PARSE
Translates the SQL statement into an execution plan, including checks for proper security authorization and checks for the existence of tables, columns, and other referenced objects.
EXECUTE
Actual execution of the statement by Oracle. For INSERTUPDATE, and DELETE statements, this modifies the data. For SELECT statements, this identifies the selected rows.
FETCH
Retrieves rows returned by a query. Fetches are only performed for SELECT statements.


SQL Trace Statistic
Meaning
COUNT
Number of times a statement was parsed, executed, or fetched.
CPU
Total CPU time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) if TIMED_STATISTICS is not turned on.
ELAPSED
Total elapsed time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) if TIMED_STATISTICS is not turned on.
DISK
Total number of data blocks physically read from the datafiles on disk for all parse, execute, or fetch calls.
QUERY
Total number of buffers retrieved in consistent mode for all parse, execute, or fetch calls. Usually, buffers are retrieved in consistent mode for queries.
CURRENT
Total number of buffers retrieved in current mode. Buffers are retrieved in current mode for statements such as INSERTUPDATE, and DELETE.


Library Cache Misses in TKPROF: TKPROF also lists the number of library cache misses resulting from parse and execute steps for each SQL statement.

Deciding Which Statements to Tune:

You need to find which SQL statements use the most CPU or disk resource.

Also a large gap between CPU and elapsed timings indicates Physical I/Os.

Enjoy:-)