Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h242Cq215806 for ; Mon, 3 Mar 2003 20:12:52 -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 h242Cp315801 for ; Mon, 3 Mar 2003 20:12:51 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id OAA45021; Mon, 3 Mar 2003 14:48:08 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0055F040; Mon, 03 Mar 2003 14:09:59 -0800 Message-ID: Date: Mon, 03 Mar 2003 14:09:59 -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_030E_01C2E196.F8D94DD0" ------=_NextPart_000_030E_01C2E196.F8D94DD0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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? :-) ----- Original Message -----=20 From: Loughmiller, Greg=20 To: Multiple recipients of list ORACLE-L=20 Sent: Monday, March 03, 2003 2:28 PM Subject: RE: Big SGA....... 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----- From: Tim Gorman [mailto:Tim@SageLogix.com] Sent: Monday, March 03, 2003 2:02 PM To: Multiple recipients of list ORACLE-L Subject: Re: Big SGA....... 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?=20 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_030E_01C2E196.F8D94DD0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Sybase, Schmybase, Oracle, Schmoracle = -- the=20 concepts are still the same.  Developers create tables and indexes = and then=20 write SQL, thinking that the RDBMS is at fault if performance doesn't = match=20 expectations.
 
They have to understand that the = structures they=20 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=20 I'm reasonably sure that there must be a way.
 
I used to joke that I could get=20 Oracle ERP/Apps to run on a Palm Pilot if I were permitted to=20 really tune the SQL.  The work performed by an application is = not an=20 immutable monolith, especially with the Oracle RDBMS and all of the = performance=20 statistics it keeps.  It is very much susceptible to=20 improvement.
 
First, they must make a reasonable = attempt to *fix*=20 the problem (by making SQL more efficient).  If that doesn't work,=20 then they should *accomodate* the problem by buying more = hardware,=20 increasing buffer sizes, etc.  The key with the latter = approach is to=20 realize that you haven't fixed anything, only accomodated it by throwing = resources at it.
 
Pop quiz:  Think of a parent with = a spoiled=20 child who is making a scene in public.  How do you quiet the = child? =20 :-)
----- Original Message -----
From:=20 Loughmiller, Greg =
Sent: Monday, March 03, 2003 = 2:28=20 PM
Subject: RE: Big = SGA.......

one=20 little piece of information..(considered critical=20 probably:-)     )
 
There isn't an opportunity to use = statspack... The=20 current application is running on sybase:-)
 
I do=20 have other teams researching the questions you mention. its a real fun = project...
-----Original Message-----
From: Tim Gorman=20 [mailto:Tim@SageLogix.com]
Sent: Monday, March 03, 2003 = 2:02=20 PM
To: Multiple recipients of list = ORACLE-L
Subject: Re:=20 Big SGA.......

Please start using STATSPACK now to = gather and=20 keep 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=20 is 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?=20
  • Let's assume that there is an I/O=20 problem.  There are two ways to address I/O (as stated in the = YAPP=20 report of www.oraperf.com): =20 reduce the *cost* per I/O request or reduce the *number* of I/O=20 requests.  The former implies getting a better/faster I/O = subsystem,=20 redistributing I/O load to different volumes, etc.  Not=20 trivial.  The latter implies improving the Buffer Cache Hit = Ratio=20 (BCHR) by increasing the size of the Buffer Cache or it implies = making=20 queries more efficient, so that they simply don't issue so many = I/O=20 requests (either to the Buffer Cache or to the = disk).
Gathering STATSPACK data and = searching for the=20 SQL statements generating the largest number of "physical I/O" = requests=20 might be illuminating for the developers.  If you work with = them on a=20 one-by-one basis on tuning each of these SQL statements, you might = see=20 dramatic improvements in performance.
 
Suggest to them that *after* you = are confident=20 that there are no tunable SQL statements, then you might consider = increasing=20 the size of the Buffer Cache.  Doing so is a last = resort, not=20 a first response.  This is because doing so does not fix the = real=20 problem, it only accomodates the real problem, which is inefficient=20 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 for a little feedback and = opinion please.=20 Having a discussion with the development group = ...
 
The=20 development group is thinking that a VERY LARGE SGA would solve = some of=20 their I/O problems. For example, they believe that a SGA = consisting of=20 over 8GB of db block buffers would resolve their multitude of=20 issues. I feel that they open another can of worms with = something=20 such as this.. And granted-there hasn't really been an = infrastructure=20 evaluation-and the SA group is currently performing that review of = the=20 environment.
 
One could=20 suggest that they could "cache" some very large tables in the SGA; = but=20 there seems to be some sense of a down side to this.. Could you = all=20 provide some input on "Extremely large SGA's"?  In the area = of 8GB or=20 so..  BUT, most of this would be the database blocks. Would = you all=20 be so kinds to provide your thoughts please?
TIA
 
 

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

=
 
------=_NextPart_000_030E_01C2E196.F8D94DD0-- -- 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).