Re: experience with undocumented pga parameters

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"

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