Showing posts with label PGA. Show all posts
Showing posts with label PGA. Show all posts

Tuesday, April 5, 2011

Sort Area in PGA

If parallel servers are disabled, than the max size of the sort area is determined by _smm_max_size parameter. The value is in KB. The _pga_max_size is the max size of a single session pga. It is expressed in bytes.

You can view this paramter as under:
select a.ksppinm name, b.ksppstvl value from sys.x$ksppi a,sys.x$ksppcv b where
a.indx = b.indx and a.ksppinm='_smm_max_size';
select a.ksppinm name, b.ksppstvl value from sys.x$ksppi a,sys.x$ksppcv b where
a.indx = b.indx and a.ksppinm='_pga_max_size';
show parameters 'pga%';
For 10gR2:

When max_parallel_servers is set to 0:

If pga_aggr_target < 500mb , _smm_max_size is set to 20% of PAT (pga_aggr_target)
if PAT is between 500mb and 1gb , _smm_max_size is set to 100M
if PAT > 1GB, _smm_max_size is set to 10% of PAT

When parallel operations are enabled

Upper bound limited to 50% of PAT/DOP

when DOP <=5, _smm_max_size is used
when DOP > 5 _smm_px_max_size/DOP limits the max memory usuage

_pga_max_size defaults to 2* _smm_max_size