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
Tuesday, April 5, 2011
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment