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: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Mon, 03 Mar 2003 10:40:56 -0800
Message-ID: <F001.0055EB19.20030303104056@fatcity.com>


This will help end-user performance *only* if a significant proportion of their response time is presently consumed doing "physical" disk I/O
(e.g., either 'db file scattered read' or 'db file sequential read'
events).  

It's possible that your strategy might help, but in my estimation, it's not probable. In over 1,000 trace files that we've analyzed in the past couple of years, less than 1% would have benefited from what your development group is suggesting.  

The downside of using trial-and-error to find out is the cost (both time and money) of putting that much memory into service. You can find out today whether it will make any difference by tracing (10046 level 8) some of your key user actions. See what percentage of total response time is consumed by OS read calls. If it's a miniscule percentage to begin with, then making those PIOs into LIOs will gain you at best only a miniscule percentage of positive impact in return for the investment.  

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- RMOUG Training Days 2003 <http://www.rmoug.org> , Mar 5-6 Denver - Hotsos Clinic <http://www.hotsos.com/training/clinic101> 101, Mar 25-27 London

-----Original Message-----
Loughmiller, Greg
Sent: Monday, March 03, 2003 11:59 AM
To: Multiple recipients of list ORACLE-L  

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: Cary Millsap
  INET: cary.millsap_at_hotsos.com

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 - 12:40:56 CST

Original text of this message

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