TKProf in 9i

A nice little addition to TKProf in 9i is that it now reports on wait level information as well. To capture the wait information in the trace file in the first place, you will need to enable a 10046 trace at level 8, or alternatively use the DBMS_SUPPORT package that now comes as standard on most recent versions of Oracle.

Here is a little demo:

SQL> alter session set 
  2   events='10046 trace name context forever, level 8';

SQL> select * from sec_test where z < 123;

and the formatted trace file now looks like

select * from sec_test where z < 123

call     count     cpu   elapsed    disk    query   current    rows
------- ------  ------ --------- ------- -------- ---------  ------
Parse        1    0.01      0.00       0        0         0       0
Execute      1    0.00      0.00       0        0         0       0
Fetch       10    0.04      0.19     109      126         0     122
------- ------  ------ --------- ------- -------- ---------  ------
total       12    0.05      0.19     109      126         0     122


Elapsed times include waiting on following events:
  Event waited on                     Times   Max. Wait  Total Waited
  --------------------------------   Waited  ----------  ------------
  SQL*Net message to client              10        0.00          0.00
  db file sequential read                 4        0.01          0.01
  db file scattered read                  7        0.02          0.11
  SQL*Net message from client            10        2.96          4.01