USER HIT RATIO: -
Username - Name of the user
Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.
DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).
Physical Reads - The cumulative number of blocks read from disk.
Logical reads are the sum of consistent gets and db block gets.
The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.
Hit ratio should be > 90%
select USERNAME,
CONSISTENT_GETS,
BLOCK_GETS,
PHYSICAL_READS,
((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS)) Ratio
from v$session, v$sess_io
where v$session.SID = v$sess_io.SID
and (CONSISTENT_GETS+BLOCK_GETS) > 0
and USERNAME is not null
Output: -
Enjoy:-)
3 comments:
Could you please explain where 90% came from, and how you've used this metric to fix something?
This is oracle recommendation actually.
Sometimes Oracle recommendations are old, outdated or wrong. This is one that has been shown to be meaningless or worse (leading people to do unnecessary work trying to fix a problem that isn't there).
So, if someone uses this to adjust the buffer cache (as some older texts recommend), they are doing it wrong. The basic problem is, putting this into a ratio obscures all the information that would be useful to make such a decision. An additional problem is all the new features and changes to the way the buffering works, combined with concurrency issues, just wipes out any possible usefulness of this ratio.
Post a Comment