I ran the following query on my system an notice that the data values for the HIT% is off by 1%. I wonder where each gather their data from? There does not seems to be a consistency? Any idea of why? :confused:
SELECT (consistent_gets + db_block_gets) "LOGICAL_READS",
physical_reads "PHYSICAL READS",
((consistent_gets + db_block_gets) - physical_reads)/
(consistent_gets + db_block_gets) * 100 "Hit Ratio %"
WHERE physical_reads > 0;
column "LOGICAL READS" format 99,999,999,999
column "PHYSICAL READS" format 999,999,999
select a.value + b.value "LOGICAL READS",
c.value "PHYSICAL READS",
100 * ((a.value+b.value)-c.value) /
(a.value+b.value) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
a.statistic# = 38
b.statistic# = 39
c.statistic# = 40;
This discrepance between v$buffer_pool_satistics and v$sysstat is a known issue from the times when v$buffer_pool_satistics was introduced in release 8.0. I personaly rely more on v$sysstat, since it is available and used for much longer time (at least from release 6.0, almost 15 years ago).
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
I think if the TIMED_STATISTICS is not set to TRUE in the init.ora file, then V$BUFFER_POOL_STATISTICS is not updated correctly. I ran your query and I got a different result when compared with V$SYSSTAT.
The queries say the hit ratio (1-(physical reads/(block gets + consistent
gets))) is 59%. However, if you take the differences between the two
measurements, and compute the ratio, you get 83%, which agrees with the v
$sysstat numbers for this instance.
Notice how consistent gets climbed by 8 million during the minute, while
block gets climbed by 150,000. Yet the consistent gets values are lower than
the block get values. My best guess is that these counters have wrapped
around many times.
One more caution: when you start up an instance, all gets will require
physical reads. The hit ratio will be 0% at first, then slowly climb to a
useful value. So these statistics are meaningless unless a lot of data has
moved through the buffer pool.