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
The first query sums the various statistics value in a single select from V$sysstat.
The second query joins the V$sysstat table against itself (4 times).
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
The first query sums the various statistics value in a single select from V$SYSSTAT.
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.