Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Big SGA.......

Re: Big SGA.......

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 03 Mar 2003 12:25:04 -0800
Message-ID: <F001.0055ED8A.20030303122504@fatcity.com>

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

> 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: jonathan_at_jlcomp.demon.co.uk

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: ListGuru_at_fatcity.com (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).
Received on Mon Mar 03 2003 - 14:25:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US