Looking for discussion on using large-ish PGA/SGA for PROD vs DEV/QA

From: <Christopher.Taylor2_at_parallon.net>
Date: Wed, 29 Aug 2012 09:10:21 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E885153A290A_at_NADCWPMSGCMS10.hca.corpad.net>

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. ( 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_at_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

Received on Wed Aug 29 2012 - 09:10:21 CDT

Original text of this message