Buffer Hit Ratio

[yongchoi75]

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?

[fiz911]

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.

[TimHall]

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

[gaegoori]

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?...

[fiz911]

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.

[yongchoi75]

This is on Sun Solaris 2.7...how does this make diff?

[julian]

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.

[BV1963]

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.

[fiz911]

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.

[BV1963]

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?