pga_aggregate_target & workarea_size_policy and Some Hidden Parameters

With "workarea_size_policy='AUTO'" oracle internally manages amount memory needed to sort and other task.
But using below hidden parameters oracle puts limit for the amount of memory for a single process, so that, a single process cannot eat up all tha PGA.

"_smm_max_size" maximum work area size in auto mode (serial)
"_pga_max_size" Maximum size of the PGA memory for one process

Value of above parameter depends on the size of "pga_aggregate_target".
The default value varies for versions and for value of "pga_aggregate_target" (or "memory_target" for 11g, if AMM in enabled).

We can use below queries to find values for above parameters:

1. Find the current value for "_smm_max_size"
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm = '_smm_max_size';

2. Find the current value for "_pga_max_size"
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm = '_pga_max_size';

3. Calculate value of "_smm_max_size" & "_pga_max_size" from "pga_aggregate_target"
select a.name, a.value, a.DESCRIPTION,a.value*.20 "_pga_max_size",(a.value*.20)*.5/1024 "_smm_max_size"
from v$parameter a
where name = 'pga_aggregate_target';

I have tested above calculation with default values for 10.2.0.1 & 11.2.0.1. (I don't know the accurate calculation It may vary).
Below is the result:

Oracle version : 10.2.0.1 :
pga_aggregate_target=2447M
_smm_max_size 250570
_pga_max_size 513167360

Calculated value from Query 3:
_pga_max_size _smm_max_size
------------- -------------
513173094 250572.8

Oracle version : 11.2.0.1 :

pga_smm_max_size=419430
_smm_max_size 419430
_pga_max_size 858992640

Calculated value from Query 3:
_pga_max_size _smm_max_size
------------- -------------
858993459.2 419430.4


For most of the systems default configuration works fine.
Under some very special cases if anybody needs more memory (to avoid disk sort), below can be used at session level:

alter session set workarea_size_policy = 'MANUAL';
alter session set sort_area_size=512000;

But be careful, if too many sessions use this and also in case of parallel query, each slave/process consumes memory up to SORT_AREA_SIZE for each sort.

Below links are very useful regarding this issue:
http://christianbilien.wordpress.com/2007/05/01/two-useful-hidden-parameters-_smm_max_size-and-_pga_max-size/
http://hoopercharles.wordpress.com/2009/12/20/sort_area_size-affecting-sorts-to-disk-with-pga_aggregate_target-set/