Re: experience with undocumented pga parameters

  • From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx, Josh Collier <Josh.Collier@xxxxxxxxxxxx>
  • Date: Sat, 09 Mar 2013 10:30:02 +0100

Josh,

when you say "sorts", do you specifically refer to SORT ORDER BYs, or 
does this include other workarea based operations like WINDOW SORTS, or 
may be even HASH GROUP BYs or HASH UNIQUEs?

In particular with HASH GROUP BY and HASH UNIQUE there are some issues 
with how Oracle manages automatic PGA - if that applies to your 
situation you might read the summary at the bottom of that post: 
http://oracle-randolf.blogspot.com/2011/01/hash-aggregation.html

Can you check V$PGASTAT, just to make sure you don't have anything weird 
going on with how Oracle thinks it needs to manage PGA?

Note that Oracle only uses a certain amount of memory per process / 
workarea, so if you want to make use of such large PGA memory you would 
need to use an appropriate Parallel Degree of your SQL executions (DOP) 
- theoretically in recent releases a single PX execution should be 
allowed to consume up to 50% PGA_AGGREGATE_TARGET across all workareas 
of that execution provided the DOP is sufficiently high.

Even then, if your distribution of work is skewed, and only a few of the 
Parallel Worker processes need to do all the work, they still might need 
to spill to disk, even if you allow up to the maximum of 2GB per process 
/ workarea.

Instead of fiddling with undocumented parameters you could try to use 
manual workarea_size_policy for your larger queries and see if that 
changes the memory /temp usage significantly. This can be done on 
session level using ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL 
SORT_AREA_SIZE = 2000000000 HASH_AREA_SIZE = 2000000000 just as an example.

Hope this helps,
Randolf

> Does anyone on this list have experience with setting the following 
> parameters on 11gR2 linux?
> I have a server with 250G available for the PGA and am having a hard time 
> getting the database to stop spilling sorts to temp.  I have a ticket open 
> with support so that I can get into a support configuration with these 
> settings, however, I am having a hard time finding a combination of these 4 
> that will prevent even small queries from sending sorts to temp.
>
> "_smm_max_size"
> "_pga_max_size"
> "_smm_px_max_size"
> "_smm_isort_cap"

--
//www.freelists.org/webpage/oracle-l


Other related posts: