RE: Looking for discussion on using large-ish PGA/SGA for PROD vs DEV/QA
Date: Wed, 29 Aug 2012 15:12:27 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E88515443DEB_at_NADCWPMSGCMS10.hca.corpad.net>
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)
Chris
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hans Forbrich
Sent: Wednesday, August 29, 2012 2:58 PM
To: oracle-l_at_freelists.org
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.
/Hans
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 29 2012 - 15:12:27 CDT