Printable View
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
db_block_buffers is NOT in bytes, it just says how many database buffers are available in the buffer cache ...
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%?
Hit ratio is currently 70-75 %
Thanks for your help.
makes more sense now.
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.
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)
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;
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.
In Oracle 7 that used to be the best possible ratio.Quote:
Originally posted by reydp
Your SGA size should be about 1/3 of physical memory.
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.
I think my total SGA is about 100 Meg.
I increased the buffer size by 50% to 12888.
When there were a couple of users the hit ratio was 96%.
Now it has gone down to 60-705 as more users have logged on. Is this normal ?
I am planning on re-building the indexes over the weekend. will this help ?
quote:
---------------------------------------------
Originally posted by julian:
In Oracle 7 that used to be the best possible ratio.
In Oracle 8 and 9 SGA can be half of the OS memory.
-----------------------------------------------------
For as long as all Oracle Processes don't utilize swap file
then fine I agree on that. But try to consider other memory utilization used by oracle such as sort_area_size.
Let me make an example, correct me If I am wrong, In a DSS/DW system let's say 10 concurrent user all together generate a report, it utilizes 10 times of the sort_area_size because a value of sort_area_size was allocate to each user and you know how large value should have for sort_area_size with this kind of setup. This is one aspect of memory utilization that is usually overlooked.
------------------------------------------------------
The goal for the future is to get the complete DB in memory!
---------------------------------------------
I don't know, for me this can only be a wishful thinking for the next 5-10 years.
Keep it cool.
You will have to monitor it over a day or two, at the start the buffers will be empty and therefore the cache hit ratio will suffer.
You are aboslutely right. These are just basic headlines. One should take into account sort_area_size.
1-2 years!Quote:
I don't know, for me this can only be a wishful thinking for the next 5-10 years.Keep it cool.