Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h23NGwR17558 for ; Mon, 3 Mar 2003 17:16:58 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h23NGv317553 for ; Mon, 3 Mar 2003 17:16:57 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id LAA27998; Mon, 3 Mar 2003 11:38:34 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0055EBCF; Mon, 03 Mar 2003 11:01:38 -0800 Message-ID: Date: Mon, 03 Mar 2003 11:01:38 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Tim Gorman" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Tim Gorman" Subject: Re: Big SGA....... Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0166_01C2E17C.F8EB4E00" ------=_NextPart_000_0166_01C2E17C.F8EB4E00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Please start using STATSPACK now to gather and keep statistics. You are = certainly going to need "before" and "after" statistics to analyze. Some questions: a.. Why does the development group think that I/O is the problem? = Have they been gathering data? Have you seen it? Do you concur that = their data proves that I/O is a performance problem belonging to the = Oracle database? b.. Let's assume that there is an I/O problem. There are two ways to = address I/O (as stated in the YAPP report of www.oraperf.com): reduce = the *cost* per I/O request or reduce the *number* of I/O requests. The = former implies getting a better/faster I/O subsystem, redistributing I/O = load to different volumes, etc. Not trivial. The latter implies = improving the Buffer Cache Hit Ratio (BCHR) by increasing the size of = the Buffer Cache or it implies making queries more efficient, so that = they simply don't issue so many I/O requests (either to the Buffer Cache = or to the disk). 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... -Tim ----- Original Message -----=20 From: Loughmiller, Greg=20 To: Multiple recipients of list ORACLE-L=20 Sent: Monday, March 03, 2003 10:59 AM Subject: Big SGA....... 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=20 Sr Manager - Enterprise Data Architecture=20 gloughmiller (IPS)=20 678.893.3217 (office)=20 ------=_NextPart_000_0166_01C2E17C.F8EB4E00 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Please start using STATSPACK now to = gather and keep=20 statistics.  You are certainly going to need "before" and "after"=20 statistics to analyze.
 
Some questions:
  • Why does the development group think = that I/O is=20 the problem?  Have they been gathering data?  Have you seen=20 it?  Do you concur that their data proves that I/O is a = performance=20 problem belonging to the Oracle database?
  • Let's assume that there is an I/O = problem. =20 There are two ways to address I/O (as stated in the YAPP report of www.oraperf.com):  reduce the = *cost*=20 per I/O request or reduce the *number* of I/O requests.  The = former=20 implies getting a better/faster I/O subsystem, redistributing I/O load = to=20 different volumes, etc.  Not trivial.  The latter implies = improving=20 the Buffer Cache Hit Ratio (BCHR) by increasing the size of the Buffer = Cache=20 or it implies making queries more efficient, so that they simply don't = issue=20 so many I/O requests (either to the Buffer Cache or to the=20 disk).
Gathering STATSPACK data and searching = for the SQL=20 statements generating the largest number of "physical I/O" requests = might be=20 illuminating for the developers.  If you work with them on a = one-by-one=20 basis on tuning each of these SQL statements, you might see dramatic=20 improvements in performance.
 
Suggest to them that *after* you are = confident that=20 there are no tunable SQL statements, then you might consider increasing = the size=20 of the Buffer Cache.  Doing so is a last resort, not a = first=20 response.  This is because doing so does not fix the real problem, = it only=20 accomodates the real problem, which is inefficient SQL.
 
Hope this helps...
 
-Tim
----- Original Message -----
From:=20 Loughmiller, Greg =
Sent: Monday, March 03, 2003 = 10:59=20 AM
Subject: Big SGA.......

hey = folks..=20 Hoping=20 for a little feedback and opinion please. Having a discussion with the = development group ...
 
The = development=20 group is thinking that a VERY LARGE SGA would solve some of their I/O=20 problems. For example, they believe that a SGA consisting of over 8GB = of db=20 block buffers would resolve their multitude of issues. I feel = that they=20 open another can of worms with something such as this.. And = granted-there=20 hasn't really been an infrastructure evaluation-and the SA group is = currently=20 performing that review of the environment.
 
One = could suggest=20 that they could "cache" some very large tables in the SGA; but there = seems to=20 be some sense of a down side to this.. Could you all provide some = input on=20 "Extremely large SGA's"?  In the area of 8GB or so..  BUT, = most of=20 this would be the database blocks. Would you all be so kinds to = provide your=20 thoughts please?
TIA
 
 

Greg = Loughmiller=20
Sr = Manager -=20 Enterprise Data Architecture
gloughmiller (IPS)
678.893.3217 = (office)=20

 
------=_NextPart_000_0166_01C2E17C.F8EB4E00-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: Tim@SageLogix.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@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).