Message-ID: <F001.0055ED8A.20030303122504@fatcity.com>
Date: Mon, 03 Mar 2003 12:25:04 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: "Jonathan Lewis" <jonathan@jlcomp.demon.co.uk>
Subject: Re: Big SGA.......
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain;	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit


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" <ORACLE-L@fatcity.com>
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: jonathan@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@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).