Re: Looking for discussion on using large-ish PGA/SGA for PROD vs DEV/QA
- From: Hans Forbrich <fuzzy.graybeard@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 29 Aug 2012 16:58:01 -0300
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@xxxxxxxxxxxx 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@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
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
