Buffer cache hit ratio low

Hi,,

My bufer cache hit ratio is about 89%. How can I increase it? Adding more db_block_buffrs I think won't help much as this low cache hit ratio is caused by a full-table scan of one table (about 200,000 rows). I have 12000 blocks buffer and the block size is 8K. I have 392 MB RAM on the server, so increasing the buffered blocks would cause swapping.

So what do I do?

Thanx.

Buy more RAM,
i think this is the best solution for you.

i should probably get 512 MB RAM?

if you know that table will always be scanned fully then you could consider recreating the table and place it to a recycle buffer pool, this could work if you dont want to buy RAM.

Pando,

Can you explain the pros / cons of using recycle as opposed to buffer pool keep. I'm considering using this with a particularly large table, but I'm unsure of which to use.

cheers

chris.

well I dont know about pros and cons but if you place a table in recycle then after a full scan or any scan of that table it will be flushed out of buffer immediately and thus avoid placing unuseless data in buffer. Keep is just oppsite if you know you will do full scan or any scan of a table often then obviously we want to keep it in buffer thus avoiding useless access to disk...

in other words Oracle gives us the power to manage the buffer with these two pools whereas the default one is managed by Oracle...

Pando,

Thanks for that. Until we get the front-end app upgraded, we can't avoid full table scans, so I'll go for the keep option.

cheers

chris.

I agree with Pando. It sounds to me like the best thing you could do is create your keep and recycle pools. Make sure that you read the information in the Oracle Tuning Guide that goes over these two pools. Remember that the values from here do not add to the size of your SGA but rather are taken from the current value. The keep pool is great for small, frequently accessed tables that you don't want flushed out of the buffer pool due to large table scans.

Is there any possibility that you can partition the table so that you are only scanning a partition instead of the whole table, that would help as well.

Pando,

Why did you say "recreate the table"?

You can do an

alter table storage (buffer_pool keep);

to change the pool that the table is loaded into. I wasn't
sure if you realized this or if there is anothe reason you
were suggesting the rebuild.

Joe