Printable View
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 tablestorage (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
If the table is not frequently updated and inserted, keeping the data in BUFFER POOL KEEP is the right choice. Please remember you need to have more memory, because DB_BLOCK_LRU_LATCH needs memory to maintain the KEEP buffer cache.
RECYCLE is another place when the table is very rarely updated or inserted.
If you are in shortage of memory, ALTER the table with CACHE parameter. This will keep data buffer in MRU of LRU list.
Today memory board is not costly, buy as much memory as you can. That will help you in the long run.
Talisman,
I was not aware of the memory requirements for the LRU latch, is the amount of memory significant, or does that depend on, as you were saying, the amount of updates going on in the table? I have a couple of small lookup tables that very rarely change that I have set to cache and put them in the keep pool. Each time I bring the database down I run a script that does a select * from them to push them up into the cache.
Would you happen to know how to determine how many lru latches that you need for each pool or should I just monitor the v$ tables and see how affective they are? I know you need to tune them but I was wondering how one determines the initial setting of them.
Joe
Each LRU latch requires a minimum of 50 Oracle data blocks.
To figure out how many LRU latches are set up in the database, run the following SQL:
SELECT ID, NAME, SET_COUNT, BUFFERS, LO_BNUM, HI_BNUM
FROM V$BUFFER_POOL;
The column, SET_COUNT refers to the number of LRU Latch sets available to each buffer pool.
Any suggestions on how to best determine how many latches to assign to a particular pool up front or do you just need to watch the sleeps and spins and tune accordingly?