SQL Performance (MOSC)

MOSC Banner

Is there a way to calculate the buffer hit ratio for an individual SQL statement

edited Sep 26, 2013 7:49AM in SQL Performance (MOSC) 9 commentsAnswered
 Background:
A predecessor put a process in place that reports the 'top 5 SQL statements' for the last 24 hours.   They used  (verbatim)  a process they pulled from the interwebs (if it's on the internet it must be true right?).  

 The script reports the SQL statement and the following stats:

:           Disk           Buffer Cache Hit                 DR Per      BG Per        CPU Per        Ela Per

:           Reads            Gets     Ratio       Runs         Run         Run            Run            Run

:           -----          ------ ---------       ----      ------      ------         ------         ------

:     235,970,373     236,773,166     0.34%        735     321,048     322,140           3.35          56.27

:       (54.168%)        (2.352%)

Well the .34% cache hit ratio has raised some flags (did I mention this report was forwarded to the director?).    The calculation they are using for the BCHR is:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center