Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

RE: Big SGA.......

From: Niall Litchfield <>
Date: Thu, 06 Mar 2003 11:19:09 -0800
Message-ID: <>

Now you are talking, at a prestigious English public school (although at least a hundred years ago) there used to be a headmaster with 3 boys. Periodically he would hide them under a tarpaulin and hit them at random
- the moral being 'life is unfair so get used to it'.

Um, I did have an on topic point once upon a time, but it seems to have gone the way of english cricket....    

-----Original Message-----

Sent: 04 March 2003 15:50
To: Multiple recipients of list ORACLE-L

... large burlap sack and a small bat ....

-----Original Message-----

Sent: Tuesday, March 04, 2003 7:39 AM
To: Multiple recipients of list ORACLE-L

duct tape

-----Original Message-----

Sent: Monday, March 03, 2003 5:10 PM
To: Multiple recipients of list ORACLE-L

Sybase, Schmybase, Oracle, Schmoracle -- the concepts are still the same. Developers create tables and indexes and then write SQL, thinking that the RDBMS is at fault if performance doesn't match expectations.  

They have to understand that the structures they have created or the queries they have written may simply be inefficient, expending too much work. I don't know how to measure that in Sybase, but I'm reasonably sure that there must be a way.  

I used to joke that I could get Oracle ERP/Apps to run on a Palm Pilot if I were permitted to really tune the SQL. The work performed by an application is not an immutable monolith, especially with the Oracle RDBMS and all of the performance statistics it keeps. It is very much susceptible to improvement.  

First, they must make a reasonable attempt to *fix* the problem (by making SQL more efficient). If that doesn't work, then they should *accomodate* the problem by buying more hardware, increasing buffer sizes, etc. The key with the latter approach is to realize that you haven't fixed anything, only accomodated it by throwing resources at it.  

Pop quiz: Think of a parent with a spoiled child who is making a scene in public. How do you quiet the child? :-)

one little piece of information..(considered critical probably:-) )  

There isn't an opportunity to use statspack... The current application is running on sybase:-)  

I do have other teams researching the questions you mention. its a real fun project...

-----Original Message-----

Sent: Monday, March 03, 2003 2:02 PM
To: Multiple recipients of list ORACLE-L

Please start using STATSPACK now to gather and keep statistics. You are certainly going to need "before" and "after" statistics to analyze.  

Some questions:

Gathering STATSPACK data and searching for the SQL statements generating the largest number of "physical I/O" requests might be illuminating for the developers. If you work with them on a one-by-one basis on tuning each of these SQL statements, you might see dramatic improvements in performance.  

Suggest to them that *after* you are confident that there are no tunable SQL statements, then you might consider increasing the size of the Buffer Cache. Doing so is a last resort, not a first response. This is because doing so does not fix the real problem, it only accomodates the real problem, which is inefficient SQL.  

Hope this helps...  


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:

Author: Niall Litchfield

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services

To REMOVE yourself from this mailing list, send an E-Mail message
to: (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 Thu Mar 06 2003 - 13:19:09 CST

Original text of this message