DB_Block-Buffers-help

[sazzadur]

Hi,

I need to change the db_block_buffers because i have found that the cach hit ratio is far too low.

I have enough memory available on the unix box.

My question is should the db_block_buffer be a multiple of db_block_size.

e.g. my block size is 8192K and my buffer size is 8192K, this gives me about 6mb of space. should i increase buffer size to 16384K(2*db_block_size) as the next figure for db_block_buffer.


look forward to reading the advice.

thanks

[pipo]

db_block_buffers is NOT in bytes, it just says how many database buffers are available in the buffer cache ...

[julian]

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

[sazzadur]

Hit ratio is currently 70-75 %

Thanks for your help.

makes more sense now.

[dknight]

To generalize Julian's comments:

db_buffer size == db_block_buffers * db_block_size

Even if you have lots of memory to spare, it is not always advisable to allocate more memory to Oracle because you can cause swapping and decrease performance.

Good luck.

[pipo]

70% ???
increase db_block_buffer !!!
try 11-12000 and see how your hit ratio improves ... generally speaking, it should never be lower than 90% (at least)

[julian]

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 :-)

[reydp]

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;
or
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.

[julian]

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.

[UNIX DBA]

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.

I think my total SGA is about 100 Meg.