PGA Management in Oracle 10gR2

By | October 19th, 2008 | Category: 10g, Oracle Database | 1 Comment »

When researching PGA utilization on 10gR2, I collected a couple of important facts for automatic pga management:

Parameters (partially hidden)

  • pga_aggregate_target: total physical memory for all workareas of all processes system-wide
  • _smm_max_size: limit for single work area
  • _pga_max_size: limit for single process
  • _smm_px_max_size: limit for all parallel slaves of one workarea

pga_aggregate_target:

  • if p_a_t  <= 500 MB, then _smm_max_size = 20% of p_a_t
  • if p_a_t  between 500 and 1000 MB, then _smm_max_size = 100 MB
  • if p_a_t >= 1000 MB, then _smm_max_size = 10% of p_a_t

_pga_max_size:

  • _pga_max_size = 2 *  _smm_max_size

_smm_px_max_size:

  • _smm_px_max_size = 50% PAT
  • degree of parallelism (dop) <= 5, then _smm_max_size is used for parallel slaves instead of smm_px_max_size
  • degree of parallelism (dop) > 5, then _smm_px_max_size/DOP is used

References:

Richmond Shee: If your memory serves you right

The Pythian Group: Working with Automatic PGA

One comment
Leave a comment »

  1. Articles about _smm_max_size volume 1 « Article Directory June 15th, 2010 05:54 :

    […] PGA Management in Oracle 10gR2 | ora-solutions.net – Martin … if p_a_t between 500 and 1000 MB, then _smm_max_size = 100 MB … degree of parallelism (dop) <= 5, then _smm_max_size is used for parallel slaves instead of smm_px_max_size … […]

Leave Comment