DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Negative Buffer Hit Ratio Value

  1. #1
    Join Date
    Jun 2001
    Posts
    243
    I found out that couple of users have negative percentage of buffer hit ratio....like -49% and it is keep decreasing....what can I do to resolve the problem? Users query is being hung up several times already....any suggestions?

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Mis-calculation, maybe?
    Jeff Hunter

  3. #3
    Join Date
    Jun 2001
    Posts
    243
    This is the query that I used to get the hit ratio.

    SELECT Username,OSUSER,Consistent_Gets,Block_Gets,Physical_Reads,
    100*( Consistent_Gets + Block_Gets - Physical_Reads)/
    ( Consistent_Gets + Block_Gets ) "Hit Ratio %"
    FROM V$SESSION,V$SESS_IO
    WHERE V$SESSION.SID = V$SESS_IO.SID
    AND ( Consistent_Gets + Block_Gets )>0
    AND username is not null
    ORDER BY Username,"Hit Ratio %";


    I don't think it's miscalculations..is it?

  4. #4
    Join Date
    Jan 2001
    Posts
    3,134
    Was the column previously formatted? Try issuing CLEAR COL and run it again.

    I've done this before, DOH!

    MH
    I remember when this place was cool.

  5. #5
    Join Date
    Jun 2001
    Posts
    243
    I cleared col and reran.....same result....now it's at -83.2%

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Yeah, looks OK to me.

    What are your values for consistent_gets, block_gets, & physical_reads?

    Jeff Hunter

  7. #7
    Join Date
    Jun 2001
    Posts
    243
    consistent_gets=2614
    block_gets=407
    physical_reads=5534

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by gaegoori
    consistent_gets=2614
    block_gets=407
    physical_reads=5534
    This is expected behavior :-) The problem you have faced is that after the DB has been up and running quite some
    time some of the component values becomes negative or extremely large. I did wonder abot that some time ago myself. I had a DB that was not shutdown for months.

    Oracle does not offer us any ways or methods to clear the statistics, except the SHUTDOWN one.

    You can rewrite your query (using decode for example) toshow negative values as 100 for I think it is very close to the truth.




  9. #9
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Doesn't this mean that buffer cache is way too small, thus most datafile blocks are read from disk rather than buffer cache ?

    I can't understand how the system's up time could influence figures so dramatically.

    Gaegoori....

    The best thing for you to do is run utlbstat/estat or statspack at peak time (not at time when activity is heavily increasing/decreasing)

    This will give you a definitive buffer cache hit ratio.

    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

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