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

From: <>
Date: Wed, 29 Aug 2012 15:12:27 -0500
Message-ID: <>

The PGA portion that has me scratching my head a bit is that a 5G PGA increases the *_area_size parameters (hash_area_size, sort_area_size etc). I was thinking (now I'm wondering if I'm mistaken) that by increasing hash_area_size, Oracle will favor hash joins, but now I'm wondering if that's actually true. I'm thinking maybe it's not and that value only matters when Oracle chooses to apply a hash join?

(The reason I went down this path a little bit was because I was noticing a Nested Loops preference in the other environments and a hash join preference in production. I checked the db parameters and objects and all the objects matched but the sga/pga parameters were significantly different - and the data is different too so it was kind of a rabbit trail really)

I'm with you on examing the SQL and access paths as the primary goal of tuning before tweaking parameters.

Thanks for the input Hans - always appreciated. (Any other thoughts are always welcome)


-----Original Message-----
From: [] On Behalf Of Hans Forbrich Sent: Wednesday, August 29, 2012 2:58 PM To:
Subject: Re: Looking for discussion on using large-ish PGA/SGA for PROD vs DEV/QA

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.


Received on Wed Aug 29 2012 - 15:12:27 CDT

Original text of this message