Sunday, December 20, 2009

11gR2 PGA related hidden parameters

These parameter name with 'pga' are in bytes, and these parameter name with 'smm' are in kilobytes.

SQL> @pga_parameter.sql
SQL> SELECT x.ksppinm name,
2 y.ksppstvl value,
3 x.ksppdesc description
4 FROM x$ksppi x, x$ksppcv y
5 WHERE x.inst_id = userenv('Instance')
6 AND y.inst_id = userenv('Instance')
7 AND x.indx = y.indx
8 AND (x.ksppinm like '%pga%'
9 OR x.ksppinm like '%smm%')
10 /

------------------------- ---------- ------------------------------
_pga_large_extent_size 1048576 PGA large extent size
_use_ism_for_pga TRUE Use ISM for allocating large e

_ldr_pga_lim 0 pga limit, beyond which new pa
rtition loads are delayed

_kdli_sio_pga FALSE use PGA allocations for direct

_kdli_sio_pga_top FALSE PGA allocations come from topl
evel PGA heap

_pgactx_cap_stacks FALSE capture stacks for setting pga

pga_aggregate_target 0 Target size for the aggregate
PGA memory consumed by the ins

__pga_aggregate_target 188743680 Current target size for the ag
gregate PGA memory consumed

_pga_max_size 209715200 Maximum size of the PGA memory
for one process

_smm_auto_min_io_size 56 Minimum IO size (in KB) used b
y sort/hash-join in auto mode

_smm_auto_max_io_size 248 Maximum IO size (in KB) used b
y sort/hash-join in auto mode

_smm_auto_cost_enabled TRUE if TRUE, use the AUTO size pol
icy cost functions

_smm_control 0 provides controls on the memor
y manager

_smm_trace 0 Turn on/off tracing for SQL me
mory manager

_smm_min_size 184 minimum work area size in auto

_smm_max_size 36864 maximum work area size in auto
mode (serial)

_smm_px_max_size 92160 maximum work area size in auto
mode (global)

_smm_retain_size 0 work area retain size in SGA f
or shared server sessions (0 f
or AUTO)

_smm_bound 0 overwrites memory manager auto
matically computed bound

_smm_advice_log_size 0 overwrites default size of the
PGA advice workarea history l

_smm_advice_enabled TRUE if TRUE, enable v$pga_advice
_smm_freeable_retain 5120 value in KB of the instance fr
eeable PGA memory to retain

_smm_isort_cap 102400 maximum work area for insertio
n sort(v1)

23 rows selected.