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 INSERT, UPDATE, 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 INSERT, UPDATE, 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:-)