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

Thread: Buffer Hit Ratio

  1. #1
    Join Date
    Feb 2001
    Posts
    44
    I've noticed that my Buffer hit ratio is less than 90%.

    In the morning, it started as about 45% and
    In the afternoon, the ratio increased gradually up to 75-80%?!!?...don't know why it's doing this way. But anyhow, still less than 90%..Should I increase my DB_BLOCK_BUFFER. Currently db_block_buffer=16384. db_block_size=8192, and db_file_multiblock_read_count=32. What should I do?


  2. #2
    Join Date
    Jan 2002
    Posts
    6

    Cool

    Hi,

    My first question is did you restart your database this morning. This would explain why your hit ratio was low to start with.

    Secondly Oracle recommends that your buffer cache hit ratio should be greater than 90%. If you have memory left on your machine, try increasing the buffer cache by increasing the value for the db_block_buffers parameter.

    Also which operating system are you using. If it's windows NT, then windows NT reads in 64 K chunks, so if your db_block_size is 8k, then your db_file_multiblock_read_count should be set to 8, not 32.

    Regards

    Fiz.
    Fiz
    OCP Oracle DBA 7, 8, 8i

  3. #3
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Low hit ratios in the morning sound like one of two things to me:

    1) Bouncing your instance to perform cold backups will empty your cache etc. Since you cache is empty all hits will initially involve disk access. This result in low hit ratio initially, improving over time.

    2) Heavy batch processing may result in a lower ratio depending on the nature of the processing.

    Increase you DB_BLOCK_BUFFER parameter repeatedly until the value become more accptable in normal running.

    Remember, don't increase the value to the point where you run out of real memory and start using excessive swap space.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  4. #4
    Join Date
    Jun 2001
    Posts
    243
    alright, I see my buffer hit ration gradually increasing. I don't have enough physical memory to increase my DB_BLOCK_BUFFER, so I'm just gonna leave as it is. if my db_block_size=8k my db_file_multiblock_read_count should be 8 instead of 32?..why is that?...

  5. #5
    Join Date
    Jan 2002
    Posts
    6
    Hi,

    The reason is as follows:

    Because NT reads in 64K chunks, then if your db_block_size is 8K, then setting db_file_multiblock_read_count to 32 means that when you wish to read from disk, you wish to do it in
    8K x 32 = 256K chunks, while NT will only do it in 64K chunks. This is why I suggest setting the db_file_multiblock_read_count to 8, because 8k x 8 = 64 K.

    Regards

    Fiz.
    Fiz
    OCP Oracle DBA 7, 8, 8i

  6. #6
    Join Date
    Feb 2001
    Posts
    44
    This is on Sun Solaris 2.7...how does this make diff?

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by yongchoi75
    This is on Sun Solaris 2.7...how does this make diff?
    Max I/O is also 64K. Reduce the db_file_multiblock_read_count to 8.

  8. #8
    Join Date
    Nov 2001
    Posts
    335
    It is true that max cluster size on NTFS is 64K. However, it does not mean that Oracle can read more than 64K per 1 IO operation. It can read 256K per one IO and you still save a lot of time by doing so.Assuming that you have striping in place you can read even more than that. Oracle can handle up to 1G worth of data in one IO request. Of course it all makes sense only if you are doing full scan, so I would say leave you db_file_multiblock_read_count the way it is.
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  9. #9
    Join Date
    Jan 2002
    Posts
    6
    Hi,

    I wasn't sure which operating system you were using, which is why I asked in my initial question, which operating system you were using.

    Find out the size that unix reads when it performs I/O to disk, and set the db_file_multiblock_read_count accordingly.

    Regards

    Fiz.
    Fiz
    OCP Oracle DBA 7, 8, 8i

  10. #10
    Join Date
    Nov 2001
    Posts
    335
    In your posting you said:

    Because NT reads in 64K chunks ....

    So I was talking about NT as well .

    Just want to calrify that specifically on NT , you can set
    db_blocks * db_file_multiblock_read_count up to 1Gb, given that your disks are striped.
    So, final formula that I would recomend is:

    db_blocks * db_file_multiblock_read_count = stripe width < 1Gb

    Do you agree?
    One, who thinks that the other one who thinks that know and does not know, does not know either!

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