DomainKey-Signature:a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.com;
  h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type;
  b=i/YivadZa8xB0AogsNxdImkb68De559frDvupnCOLe8YYmQgmvxhQVR82DvOJ1vahwD+EskBR/MUR/lrohrHQUERFLr8vU3Z2ctN9/vhcqapk+Cdixm0l8HzWbhvRGuCzZqZ55nu1t2xDzO3R//fXzh78xLrvMTZobNeoWrGqe4=;
References: <F05D8DF1FB25F44085DB74CB916678E885153A290A@NADCWPMSGCMS10.hca.corpad.net>
Message-ID: <1346264980.73463.YahooMailNeo@web121601.mail.ne1.yahoo.com>
Date: Wed, 29 Aug 2012 11:29:40 -0700 (PDT)
From: David Fitzjarrell <oratune@yahoo.com>
Subject: Re: Looking for discussion on using large-ish PGA/SGA for PROD vs DEV/QA
To: "Christopher.Taylor2@parallon.net" <Christopher.Taylor2@parallon.net>,
  "oracle-l@freelists.org" <oracle-l@freelists.org>
In-Reply-To: <F05D8DF1FB25F44085DB74CB916678E885153A290A@NADCWPMSGCMS10.hca.corpad.net>
MIME-Version: 1.0
Content-type: text/plain
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: oratune@yahoo.com

I use Tanel Poder's sgastatx.sql script (http://blog.tanelpoder.com/files/scripts/sgastatx.sql) to report on the memory allocations and usage in the SGA subheaps; it can certainly help you decide if the SGA is oversized or not.  And, no, I don't believe it's an idiotic concept to check the SGA size.

David Fitzjarrell


________________________________
From: "Christopher.Taylor2@parallon.net" <Christopher.Taylor2@parallon.net>
To: oracle-l@freelists.org 
Sent: Wednesday, August 29, 2012 8:10 AM
Subject: Looking for discussion on using large-ish PGA/SGA for PROD vs DEV/QA

Guys/Gals,
As a few of you know I have taken a new job and one of the applications I'm responsible for has multiple dev and qa environments and one prod environment. (10.2.0.4 RAC - 3 nodes in QA/Prod, 2 nodes in Dev)

We have a federal report that has started performing poorly in production and I have a QA environment with less volume where it returns reasonably well.

One of the challenges I'm having is that the SGA in Prod is 40 GB, and PGA = 5GB.  Dev GB/5GB, QA@GB/1 GB (PGA much different). (Challenges include coming up to speed at breakneck pace on environments, environment usage, statistics methodology (there isn't one), indexing methodology (there isn't one)...)

I think Jonathan Lewis or someone posted recently about large SGA sizes and PGA sizes - that having gobs of memory available doesn't *necessarily* mean automatically setting your databases up to use that large amount of memory (or perhaps I was dreaming).

I'm wondering if I should be much concerned about the SGA sizes currently and come back to them later, or if I should be looking at them in relation to query performance - that perhaps the SGAs are "oversized" or if that is an idiotic concept to begin with.  I'm going to research it further but wanted to throw that out there.

Chris Taylor


--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l