Home » RDBMS Server » Performance Tuning » SGA - Buffer cache size
SGA - Buffer cache size [message #273924] Fri, 12 October 2007 04:39 Go to next message
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)

Report message to a moderator

Re: SGA - Buffer cache size [message #291723 is a reply to message #273924] Sun, 06 January 2008 04:28 Go to previous messageGo to next message
Frank Naude
Messages: 4580
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.

Report message to a moderator

Re: SGA - Buffer cache size [message #291728 is a reply to message #273924] Sun, 06 January 2008 05:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Is there any issue in having a buffer cache that large (7G).

Yes, cache buffer chains are larger so latch time is longer so latch waits are more likely and longer.
In addition, checkpoints are also longer.

As Frank said, use SGA_TARGET and let Oracle size the different components of SGA.
If you have a hit ratio to 98% it is likely you are wasting RAM.
Increase your PGA_AGGREGATE_TARGET instead (but follow what the advisor said V$PGA_TARGET_ADVICE).

Regards
Michel

Report message to a moderator

Re: SGA - Buffer cache size [message #291733 is a reply to message #291728] Sun, 06 January 2008 06:15 Go to previous messageGo to next message
Frank Naude
Messages: 4580
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.

Report message to a moderator

Re: SGA - Buffer cache size [message #291739 is a reply to message #291733] Sun, 06 January 2008 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Given the author I tend to be very reluctant in trusting the given information. He talked about a monosession instance as there is no reference to latch and access concurrency and this is where big cache fails.
Anyway TPCC is a very specific benchmark and is biaised so that using a big cache allows to get better performances that could never happen in real cases.

Have a look at this thread http://forums.oracle.com/forums/thread.jspa?threadID=543877&tstart=135, especially J. Lewis' answer.

Regards
Michel

[Edit: add a missing word]

[Updated on: Sun, 06 January 2008 12:55]

Report message to a moderator

Re: SGA - Buffer cache size [message #291768 is a reply to message #291739] Sun, 06 January 2008 12:44 Go to previous messageGo to next message
Frank Naude
Messages: 4580
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).

Report message to a moderator

Re: SGA - Buffer cache size [message #291769 is a reply to message #291768] Sun, 06 January 2008 13:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, 10g greatly enhance object checkpoint with the object block chain but having a big cache may lead to more object dirty blocks and so increase the time of object checkpoint although incremental checkpoint (what J. Lewis calls "checkpoint queue") decreases the likelyhood and you don't drop or truncate objects very often (I hope).

And point (3) (which is my first point) is a very important one as it impacts latches which mean waits, cpu overhead, serialization and non-scability.

Regards
Michel

Report message to a moderator

Re: SGA - Buffer cache size [message #291771 is a reply to message #291769] Sun, 06 January 2008 13:29 Go to previous messageGo to next message
Frank Naude
Messages: 4580
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?

Report message to a moderator

Re: SGA - Buffer cache size [message #291772 is a reply to message #291771] Sun, 06 January 2008 13:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
v$db_cache_advice can give us if the cache is oversize or not.
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.

Regards
Michel

Report message to a moderator

Re: SGA - Buffer cache size [message #291775 is a reply to message #291772] Sun, 06 January 2008 14:03 Go to previous message
Frank Naude
Messages: 4580
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.

Report message to a moderator

Previous Topic: slow query
Next Topic: Deletion script doesnt completes
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jun 28 05:52:31 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.