Tuesday, June 4, 2013

Oracle 11gR2 PGA parameters

I primarily work with Oracle 11gR2 and beyond.  The following post is the best source I've found for 11gR2 PGA management parameters - there's lots of information out there that seems to be specific to earlier versions of Oracle.
http://blog.yannickjaquier.com/oracle/pga-and-sga-sizing.html

The parameter pga_aggregate_target will control the aggregate size of PGA workareas.  This will not limit other uses of PGA, which is why the total PGA usage can be considerably higher than pga_aggregate_target.

The hidden parameter _pga_max_size is the limit of all PGA work areea size for a single process.
The hidden parameter _smm_max_size is the limit of any single PGA work area size.
The hidden parameter _smm_px_max_size is the limit of any single PGA work area for parallel processes. 

For pga_aggregate_target <= 500MB
   _pga_max_size = 200MB
   _smm_max_size = pga_aggregate_target/5
   _smm_px_max_size = pga_aggregate_target/2

For 500MB < pga_aggregate_target <= 1GB
   _pga_max_size = 200 MB
   _smm_max_size = 100MB
   _smm_px_max_size = _pga_max_size/2

For pga_aggregate_target > 1GB
   _pga_max_size = pga_aggregate_target/5
   _smm_max_size = pga_aggregate_target/10
   _smm_px_max_size = _pga_max_size/2

No comments:

Post a Comment