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.
Posts
1 comment:
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.
Post a Comment