I know of one system where it would have been beneficial to have an 8GB buffer_pool_keep (as you might guess, this was a specialised telecomms system); but I think the general rule should be to consider very large memory as a requirement that has to be proved, rather than an obvious easy option.
It is ALMOST inevitable that a larger buffer will have a beneficial impact on overall performance, especially if you set your MTTR correctly, but there is always a cost/benefit/risk triangle. How much effort do you want to waste proving you need it before you just whack it in ? How much does it cost ? What if it makes things worse (which it might - for example a small table is 2% of the buffer, and 2% of 8GB is 160MB - and small tables get special treatment) ? What if it doesn't really make things noticeable better when you've told everyone it's the answer ? What happens if a huge amount of the buffer is dirty when you crash - how long will your recovery time be ? Can you afford it ? But the one that always nags at me - If you solve all your current problems by putting in a lot of memory, are you going to encourage, or conceal, very bad code that gradually soaks up all that memory then suddenly explodes as a problem that has to be fixed urgently because you can't get the next 8 GB of memory for a couple of weeks ? And so on ... BTW - does anyone have any confirmed figures for the number of cache buffers chains latches for very large numbers of buffers ? Does it stick around the 128 buffers per latch (see www.ixora.com.au for the algorithm), or is there another step function. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) ____UK_______March 19th ____UK_______April 8th ____UK_______April 22nd ____USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 03 March 2003 17:59 > hey folks.. Hoping for a little feedback and opinion please. Having a > discussion with the development group ... > > The development group is thinking that a VERY LARGE SGA would solve some of > their I/O problems. For example, they believe that a SGA consisting of over > 8GB of db block buffers would resolve their multitude of issues. I feel that > they open another can of worms with something such as this.. And > granted-there hasn't really been an infrastructure evaluation-and the SA > group is currently performing that review of the environment. > > One could suggest that they could "cache" some very large tables in the SGA; > but there seems to be some sense of a down side to this.. Could you all > provide some input on "Extremely large SGA's"? In the area of 8GB or so.. > BUT, most of this would be the database blocks. Would you all be so kinds to > provide your thoughts please? > TIA > > > > Greg Loughmiller > Sr Manager - Enterprise Data Architecture > gloughmiller (IPS) > 678.893.3217 (office) > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
