DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

  1. #1
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925

    Question

    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:
    Thanx
    Sam



    Life is a journey, not a destination!


  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Did you run the query immediatley after the DB is started up?

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    [QUOTE][i]Originally posted by sambavan [/i]
    [B]I wonder where each gather their data from? [/B][/QUOTE]

    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,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.



  8. #8
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Tamilselvan,

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

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  9. #9
    Join Date
    Jan 2011
    Posts
    2
    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&#37;. 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

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    replying to a 10 year old thread in the forum archives - why?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width