Monday, January 05, 2009

determine hit ratio from tkprof output

when, trace file was executed by tkprof command-line. 
How can find "Hit Ratio" from tkprof output?

Hit Ratio =

Logical Reads - Physical Reads     or       (Query + Current) - Disk 
    ------------------------------                        ------------------------        
           Logical Reads                                      (Query + Current)

1 -  Physical Reads/  Logical Reads  or 1 - Sum(Disk) /(Sum(Query) + Sum(Current))
disk: This indicates the number of blocks read from disk. Generally you want to see blocks being read from the buffer cache rather than disk. 

query : This column is incremented if a buffer is read in Consistent mode. A Consistent mode buffer is one that has been generated to give a consistent read snapshot for a long running transaction. The buffer actually contains this status in its header. 

current: This column is incremented if a buffer found in the buffer cache that is new enough for the current transaction and is in current mode (and it is not a CR buffer). This applies to buffers that have been read in to the cache as well as buffers that already exist in the cache in current mode. 

Look at  =>

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.02          0         36          0           0
Execute   2      0.00       0.00       [P]0       [C]0       [D]0        0
Fetch   108      0.00       0.00       [P]0     [C]138       [D]0    1579 
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      112      0.00       0.03          0        174          0        1579


Hit Ratio = ?

Logical Read = Consistent Get + DB Block Gets
Logical Read = query + current
Logical Read = Sum [C] + Sum [D]
Logical Read =  [0 + 138] + [0 + 0]
Logical Read =  138

Hit Ratio = 1 - (Physical Reads / Logical Reads)
Hit Ratio = 1 - (Sum[P] / Logical Reads)
Hit Ratio = 1 - (0 /  138)
Hit Ratio = 1 = 100%


call     count       cpu    elapsed       disk      query    current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ---------
Parse        2      0.02       0.02          0          0          0            0
Execute      2    239.39    1003.16     [P]274981  [C]3792129     [D]534        242
Fetch            0      0.00       0.00         [P]0                 [C]0          [D]0          0
------- ------  -------- ---------- ---------- ---------- ----------  ---------
total        4    239.41    1003.18     274981    3792129        534        242


Hit Ratio = ?

Logical Read = Consistent Get + DB Block Gets
Logical Read = query + current
Logical Read = Sum [C] + Sum [D]
Logical Read =  [3792129 + 0] + [534 + 0]
Logical Read =  3792663

Hit Ratio = 1 - (Physical Reads / Logical Reads)
Hit Ratio = 1 - (Sum[P] / Logical Reads)
Hit Ratio = 1 - ([0 + 274981] /  3792663)
Hit Ratio = 0.92 = 92%

If hit ratio is lower than 99%,So should check how many extents on each of objects.

1 comment:

Stephen Andert said...

Some would question the validity of using Hit Ratios on their own as a tuning guideline. Many DBA's now say that the hit ratio can best be used by noting a sudden change in conjunction with a user-reported problem. A 99% HR is not in of itself the goal of a DBA.