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

No comments:

Post a Comment