Buffer Hit Ratio is 0
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Buffer Hit Ratio is 0

  1. #1
    Join Date
    Dec 1999
    Location
    Alpharetta, GA, US
    Posts
    192

    Thumbs down Buffer Hit Ratio is 0

    Hi,
    My instance is showing Buffer Hit Ratio = 0. it looks weared for me. you guys have any idea.

    Thanks

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well what's your query

    also post consistent gets, db block gets and physical reads

  3. #3
    Join Date
    Jul 2000
    Posts
    119
    and you are also sure that this is buffer hit ratio and not buffer cache miss ratio.. also if this is the case then have you identified the sql's that are performing poorly ??
    OCP 8.0, 8i, 9i

  4. #4
    Join Date
    Dec 1999
    Location
    Alpharetta, GA, US
    Posts
    192
    Here are the hitratios.
    DB BLOCK HIT RATIO
    Read-Buffer Hit ration
    ---------------------0
    Elapsed: 00:00:00.00

    DICTIONARY CACHE HIT RATIO
    Gets Misses HitRatio
    ---------- ---------- ----------
    40756073 3853 99.9905471

    Elapsed: 00:00:00.00

    LIBRARY CACHE HIT RATIO
    PINS PINHITS PHITRAT MISSES HITRAT
    ---------- ---------- ---------- ---------- ----------
    148947286 146576019 98.4079824 14724 99.9901156

    Elapsed: 00:00:00.00
    Thanks.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    please post the values I mentioned

    also the query

  6. #6
    Join Date
    Dec 1999
    Location
    Alpharetta, GA, US
    Posts
    192
    select * from history where phone='4569870092';

    i got a local index on phone;
    i check the explan plan it is showing the full table scan. i am seeing this change since last 2 days.

    Thanks.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hmm am I missing something here? What has that query to do with buffer ratio?

  8. #8
    Join Date
    Dec 1999
    Location
    Alpharetta, GA, US
    Posts
    192
    SQL> select lpad(name,20,' ')||' = '||value xn1,value xv1
    2 from v$sysstat where name='db block gets';

    XN1 XV1
    --------------------------------------------------------------- ----------
    db block gets = 1472728 1472728

    SQL> select lpad(name,20,' ')||' = '||value xn21,value xv2
    2 from v$sysstat where name='physical reads';

    XN21 XV2
    --------------------------------------------------------------- ----------
    physical reads = 2829324 2829324

    SQL> select lpad(name,20,' ')||' = '||value xn3,value xv3
    2 from v$sysstat where name='consistent gets';

    XN3 XV3
    --------------------------------------------------------------- ----------
    consistent gets = 2017164 2017164

    SQL>

  9. #9
    Join Date
    Jul 2000
    Posts
    119
    I am not sure about your post...
    what I am sure is :
    hit ratio = 1 - (physical reads) / (block gets + consistant gets)

    can you try something like this please ?

    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;
    I hope this will show u a better figure!
    OCP 8.0, 8i, 9i

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by Chandra
    SQL> select lpad(name,20,' ')||' = '||value xn1,value xv1
    2 from v$sysstat where name='db block gets';

    XN1 XV1
    --------------------------------------------------------------- ----------
    db block gets = 1472728 1472728

    SQL> select lpad(name,20,' ')||' = '||value xn21,value xv2
    2 from v$sysstat where name='physical reads';

    XN21 XV2
    --------------------------------------------------------------- ----------
    physical reads = 2829324 2829324

    SQL> select lpad(name,20,' ')||' = '||value xn3,value xv3
    2 from v$sysstat where name='consistent gets';

    XN3 XV3
    --------------------------------------------------------------- ----------
    consistent gets = 2017164 2017164

    SQL>
    well with those values it is impossible you get 0 unless your query is wrong

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