data is off by 1% between v$buffer_pool_statistics and v$systat, WHY?

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 %"
FROM v$buffer_pool_statistics
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
where
a.statistic# = 38
and
b.statistic# = 39
and
c.statistic# = 40;


Sam :confused:

Did you run the query immediatley after the DB is started up?

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).

Quote:

---

Originally posted by sambavan
I wonder where each gather their data from?

---

You can find out easily by querying the view V$FIXED_VIEW_DEFINITION.

SELECT * FROM v$fixed_view_definition
WHERE UPPER(view_name) IN ('V$SYSSTAT', 'V$BUFFER_POOL_STATISTICS');

hth,

In response to tamilselvans question, no, the instance had been up for a while.

In response to Jurij, that was quiet interesting to know. I'll try the query that you had posted on this thread.

Sam

The out put of

SELECT * FROM v$fixed_view_definition
WHERE UPPER(view_name) IN ('V$SYSSTAT', 'V$BUFFER_POOL_STATISTICS');


V$SYSSTAT
select STATISTIC# , NAME , CLASS , VALUE from GV$SYSSTAT where inst_id = USEREN
V('Instance')

V$BUFFER_POOL_STATISTICS
select id, name, set_msize, cnum_repl, cnum_write, cnum_set, buf_got, sum_write,
sum_scan, free_buffer_wait, write_complete_wait, buffer_busy_wait, free_buffer_
inspected, dirty_buffers_inspected, db_block_change, db_block_gets, consistent_g
ets, physical_reads, physical_writes from gv$buffer_pool_statistics where inst_i
d = USERENV('Instance')


The output of :
SELECT * FROM v$fixed_view_definition
2 WHERE UPPER(view_name) IN ('GV$BUFFER_POOL_STATISTICS', 'GV$SYSSTAT');

GV$SYSSTAT
select inst_id,indx,ksusdnam,ksusdcls,ksusgstv from x$ksusgsta

GV$BUFFER_POOL_STATISTICS
select kcbwbpd.inst_id, kcbwbpd.bp_id, kcbwbpd.bp_name, sum(kcbwds.cnum_set),sum
(kcbwds.cnum_repl), sum(kcbwds.cnum_write), sum(kcbwds.cnum_set), sum(kcbwds.buf
_got), sum(kcbwds.sum_wrt), sum(kcbwds.sum_scn), sum(kcbwds.fbwait), sum(kcbwds.
wcwait), sum(kcbwds.bbwait), sum(kcbwds.fbinsp), sum(kcbwds.dbinsp), sum(kcbwds.
dbbchg), sum(kcbwds.dbbget), sum(kcbwds.conget), sum(kcbwds.pread), sum(kcbwds.p
write) from x$kcbwds kcbwds, x$kcbwbpd kcbwbpd where kcbwds.set_id >= kcbwbpd.bp
_lo_sid and kcbwds.set_id <= kcbwbpd.bp_hi_sid and kcbwbpd.bp_size != 0 group by
kcbwbpd.inst_id, kcbwbpd.bp_id, kcbwbpd.bp_name


Which to trust the most?

Sam

Sambavan,
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.

Tamilselvan,

In fact those parameters are set to true. Though it is 1%, isn't it wiered :)

Sam

Sam asks about trusting v$sysstat vs. v$buffer_pool_statistics. My experience is that v$sysstat is more reliable, at least in Oracle 10g. I don't know if the issue is resolved in 11g.

It appears that the columns in v$buffer_pool_statistics are stored as 31-bit
integers, and will wrap around and become useless in a high volume
environment.

Here are two queries against v$buffer_pool_statistics, one minute apart, in a
high-volume production environment:

15:39:39
Physical Reads Block Gets Consistent Gets Buffer Cache Ratio
---------------- ---------------- ---------------- ------------------
35,861,830,327 49,043,612,339 40,462,164,249 .599

15:40:39
Physical Reads Block Gets Consistent Gets Buffer Cache Ratio
---------------- ---------------- ---------------- ------------------
35,862,926,655 49,043,761,695 40,468,697,761 .599

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.
-tdc

replying to a 10 year old thread in the forum archives - why?

1. Google search for v$buffer_pool_statistics returns this discussion in the first page
2. The original question was never answered.
3. The question and answer are still valid.
-tdc