Re: Looking for discussion on using large-ish PGA/SGA for PROD vs DEV/QA
Date: Wed, 29 Aug 2012 16:58:01 -0300
Message-ID: <503E7449.4080701_at_gmail.com>
If you are having problems with one report in particular, then it's a good idea to get to understand the report and the SQL that is involved.
While SGA adjustments may help, in the case of a single report, I'd be looking at execution paths and PGA first - if there is a lot of sorting (in PGA) or joining (largely in PGA), remembering that PGA uses TEMP tablespace effectively like a kind of swap space may help. And understanding the path should help decide whether indexes are used [appropriately]. As long as you aren't forced into swapping or paging, from a purely reporting stand point an oversized SGA should not hurt much. (It might hurt transactional operations - used to be a problem, but not so much any more.) SO I would put the SGA issue as a lower priority for investigation.
On the other hand, if you experience performance issues across the board or in a lot of different areas, then I'd be looking at 'common' things such as SGA as a higher priority.
Either way, David pointed you to Tanel's excellent script.
/Hans
On 29/08/2012 11:10 AM, Christopher.Taylor2_at_parallon.net wrote:
> 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_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
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 29 2012 - 14:58:01 CDT