Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Posts
    78

    Unanswered: showing differnt buffer hit ratio

    Hai

    I had run the statspackrep.sql ,as given the below report .In that report Buffer Hit Ratio: 88.33,
    but when query v$sysstat table buffer hit ration:.934261392.My question is why it showing differnt buffer hit ratio

    Thanks in advance
    mohan



    STATSPACK report for

    DB Name DB Id Instance Inst Num Release OPS Host
    ---------- ----------- ---------- -------- ---------- ---- ----------
    DEMO 3229116766 demo 1 8.1.6.0.0 NO MOHAN-2K

    Snap Length
    Start Id End Id Start Time End Time (Minutes)
    -------- -------- -------------------- -------------------- -----------
    312 313 09-Apr-03 14:59:09 09-Apr-03 15:15:13 16.07


    Cache Sizes
    ~~~~~~~~~~~
    db_block_buffers: 5048
    db_block_size: 8192
    log_buffer: 32768
    shared_pool_size: 152428800


    Load Profile
    ~~~~~~~~~~~~
    Per Second Per Transaction
    --------------- ---------------
    Redo size: 384.33 52,928.00
    Logical reads: 6.12 843.14
    Block changes: 0.85 117.00
    Physical reads: 0.71 98.43
    Physical writes: 0.05 7.43
    User calls: 0.56 77.57
    Parses: 0.67 92.29
    Hard parses: 0.07 10.00
    Sorts: 0.28 39.00
    Transactions: 0.01

    Rows per Sort: 42.09
    Pct Blocks changed / Read: 13.88
    Recursive Call Pct: 94.43
    Rollback / transaction Pct: 0.00


    Instance Efficiency Percentages (Target 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Buffer Nowait Ratio: 100.00
    Buffer Hit Ratio: 88.33
    Library Hit Ratio: 92.53
    Redo NoWait Ratio: 100.00
    In-memory Sort Ratio: 97.80
    Soft Parse Ratio: 89.16
    Latch Hit Ratio: 100.00





    SQL> select * from v$sysstat where name in ('db block gets','physical reads','co
    nsistent gets');
    ?STATISTIC# NAME
    ---------- ----------------------------------------------------------------
    CLASS VALUE
    ---------- ----------
    38 db block gets
    8 6548

    39 consistent gets
    8 44670

    40 physical reads
    8 3367


    SQL> select (1-3367/(6548+44670)) from dual;
    ?(1-3367/(6548+44670))
    ---------------------
    .934261392

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Mohan,

    The act of obtaining these values can affect the values but with the values you've got I wouldn't think this was the case.

    The big difference in numbers (ie 88 and 0.93) is just how the number is represented (as a percentage or as a part of 1, 88% and 0.93 = 93%).

    Try the running the query following repeatedly and see if it's coming up with any different numbers?

    Does the server have other sessions querying the database while you're getting the numbers?

    select co.value as consistent_gets,
    db.value as db_block_gets,
    ph.value as physical_reads,
    (1-ph.value/(db.value+co.value)) as buffer_hit_ratio
    from v$sysstat co,
    v$sysstat db,
    v$sysstat ph
    where co.name = 'consistent gets' and
    db.name = 'db block gets' and
    ph.name = 'physical reads'

    HTH
    Bill

  3. #3
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Originally posted by billm
    Hi Mohan,

    The act of obtaining these values can affect the values but with the values you've got I wouldn't think this was the case.

    The big difference in numbers (ie 88 and 0.93) is just how the number is represented (as a percentage or as a part of 1, 88% and 0.93 = 93%).

    Try the running the query following repeatedly and see if it's coming up with any different numbers?

    Does the server have other sessions querying the database while you're getting the numbers?

    select co.value as consistent_gets,
    db.value as db_block_gets,
    ph.value as physical_reads,
    (1-ph.value/(db.value+co.value)) as buffer_hit_ratio
    from v$sysstat co,
    v$sysstat db,
    v$sysstat ph
    where co.name = 'consistent gets' and
    db.name = 'db block gets' and
    ph.name = 'physical reads'

    HTH
    Bill
    Another, and more subtle, difference between the 88% and 93% is what they mean. The values in v$sysstat are cummulative since the instance started. The numbers from the Statspack report are for the specific 16 minute window for which you ran the report. So, for that 16 minute window, the ratio was 88%. The average ratio since the database has been running is 93%. Make sense? Let me know.

    HTH,
    Patrick

Posting Permissions

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