The integer you specify for db_block_buffer need not be a multiple of db_block_size. If db_block_buffer is 100, then you have 100*8192 bytes of Oracle memory. What is your buffer hit ratio? Is it less than 90%?
True. If your buffer Hit Ratio is very high (say more than 90%) you might want to decrease a bit DB_BLOCK_BUFFERS by 5% for example and monitor if the Hit Ratio decreases. At some point you get an optimal hit ratio. I personally like 95 :-)
Try to be propotion with your actual physical memory.
Your SGA size should be about 1/3 of physical memory.
Query first the SGA size:
select * from v$sga;
select sum(value) from v$sga;
Remember not to over size your db buffer, your system might
end up using swap file in which case performance suffer.
Try to maintain your indexes first before any adjustments you have to make in db_block_buffers. In this case, you are sure that the hit ratio
you want to achieve is because of db_block_buffers configuration and not indexes issues.
Originally posted by reydp Your SGA size should be about 1/3 of physical memory.
In Oracle 7 that used to be the best possible ratio.
In Oracle 8 and 9 SGA can be half of the OS memory.
The goal for the future is to get the complete DB in memory!
P.S. I keep 2 big tables in memory of all instances on an Oracle Parallel Server. It improved performance a lot. Especially with some reports we run from Node 2. I did increase DB_BLOCK_BUFFER to 50.000.
My db_block_buffers is 30,000 and the db_block_size is 2048.
So I have 61440000 (30,000*2048) bytes reserved for blocks. (approx 58 Megabytes).
This makes up part of the SGA. You need to know how much physical ram your machine has before you make drastic changes to the SGA.
I think the SGA is made up primarily of the Buffer Cache ( discussed earlier in my case it was 58 meg), the shared_pool_size, the large_pool_size, the log_buffer. Totalling these gives the SGA, make sure there is enough room for this in ram and then some as there are a few more uses for the SGA that are minimal but must be accounted for.