| SGA - Buffer cache size [message #273924] |
Fri, 12 October 2007 04:39  |
mwansalovewell
Messages: 71
Registered: October 2007
Location: uk
|
Member |
|
|
I have a solaris system with 12G ram.
running addm suggests increasing the db_cache_size = 7G.
The current size is 4G and hit ratio = 98%
PGA = 800
Is there any issue in having a buffer cache that large (7G). Infact oracle recommends 80% of physical memory to be allocated to SGA. ( no other applications/programs running apart from database on this server)
|
|
|
|
| Re: SGA - Buffer cache size [message #291723 is a reply to message #273924] |
Sun, 06 January 2008 04:28  |
Frank Naude
Messages: 4581
Registered: April 1998
|
Senior Member |
|
|
I've seen many databases with buffer caches larger than 7 Gig - mostly a waste. However, if you're not using the memory for anything else, you might as well give it to Oracle.
PS: Use SGA_TARGET and set memory areas like DB_CACHE_SIZE to 0. This will allow Oracle to automatically size those areas.
|
|
|
|
|
|
| Re: SGA - Buffer cache size [message #291733 is a reply to message #291728] |
Sun, 06 January 2008 06:15 |
Frank Naude
Messages: 4581
Registered: April 1998
|
Senior Member |
|
|
I'm not entirely sure that a large cache is a bad idea. See http://www.orafaq.com/node/52
| Quote: |
Have you noticed that all Oracle10g "world record" benchmarks use over 50 gig data caches? I worked with one of these TPCC benchmarks and ran repeatable timings. Up to the point where the working set was cached, the benefit of a larger data cache outweighed the LIO overhead.
|
|
|
|
|
|
|
| Re: SGA - Buffer cache size [message #291768 is a reply to message #291739] |
Sun, 06 January 2008 12:44 |
Frank Naude
Messages: 4581
Registered: April 1998
|
Senior Member |
|
|
Great link - thanks Michel!
To summarize Jonathan response (mostly for my own benefit):
1) Long checkpoints was addressed in 8i (checkpoint queue)
2) Scans for CLEAN blocks were addressed in 10g (object queue)
3) Only problem left MIGHT be longer waits on the cache buffers chains latch (most likely the LIO overhead Don is referring to)
So, increasing the cache is most likely going to help, but monitor for (3).
|
|
|
|
|
|
| Re: SGA - Buffer cache size [message #291771 is a reply to message #291769] |
Sun, 06 January 2008 13:29 |
Frank Naude
Messages: 4581
Registered: April 1998
|
Senior Member |
|
|
Both Jonathan and Don recognize the LIO overhead. However, the quesion still is: "will overall performance be better or not". My bet is it will (though, not by a great margen).
Lovewell, is it possible for you to baseline the system, increase the cache and let us know if it helped or not?
|
|
|
|
|
|
| Re: SGA - Buffer cache size [message #291775 is a reply to message #291772] |
Sun, 06 January 2008 14:03 |
Frank Naude
Messages: 4581
Registered: April 1998
|
Senior Member |
|
|
| Michel Cadot wrote on Sun, 06 January 2008 21:42 |
v$db_cache_advice can give us if the cache is oversize or not.
|
We already know that ADDM thinks the cache is too small and that it should be increased from 4 to 7 GB. So, it will allocate more memory to it if it can.
| Michel Cadot wrote on Sun, 06 January 2008 21:42 |
But, as we said before, if SGA_TARGET is used, the question is of lesser interest as Oracle should be able to correctly size the components.
|
Question is: will Oracle consider factors like "buffers chains"? Most likely not... The OP still needs to be aware of it. That said, overall performance are almost sure to increase.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
