Data Dictionary   «Prev 

Computing Data buffer hit ratio in Oracle

Consider the following two queries to compute and display the data buffer hit ratio. This is an excellent example of how the DBA can write the same query in two different ways.

Buffer Hit Ratio
  1. The first query sums the various statistics value in a single select from V$sysstat.
  2. The second query joins the V$sysstat table against itself (4 times).
  3. Both queries use the V$sysstat view ad look at physical reads as a percentage of consistent_get plus db_block_gets, but do this in different ways. In this example, the second query will run faster than the first query, because it joins the V$sysstat table against itself.

Buffer hit ratio

Buffer Hit Ratio
  1. The first query sums the various statistics value in a single select from V$SYSSTAT.
  2. The second query joins the V$SYSSTAT table against itself (4 times).
PROMPT **************************************
PROMPT  HIT RATIO SECTION
PROMPT **************************************
PROMPT
PROMPT    ========================= 
PROMPT    BUFFER HIT RATIO 
PROMPT    ========================= 
prompt (should be > 70, 
else increase db_block_buffers in init.ora)

select trunc(
 (1-(sum(decode(name,'physical reads', value,0))/
 (sum(decode(name,'db block gets',value,0))+
 (sum(decode(name,'consistent gets',value,0)))))
)* 100) " Buffer hit ratio "
FROM v$sysstat;
-----------------------------------------------

select a.value + b.value "logical_reads",
 c.value            "phys_reads",
 d.value            "phy_writes",
 round(100*((a.value + b.value)-c.value)/(a.value+b.value))  
 " buffer hit ratio "
from v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d
WHERE
  a.statistic# = 37
AND
  b.statistic# = 38
AND
  c.statistic# = 39
AND
  d.statistic# = 40;

Both queries use the V$sysstat 1) view and 2) look at physical reads as a percentage of consistent_gets plus db_block_gets, but do this in different ways.
In this example, the second query will run faster than the first query, because it joins the V$SYSSTAT table against itself.