Database Reference
In-Depth Information
SID TIME WORK_AREA_SIZE MAX_MEM_USED PASSES TEMPSEG_SIZE
148 31:38 51.2 51.2 0 16
148 31:39 51.2 51.2 0 48
148 31:40 51.2 51.2 1 73
148 31:41 21.4 51.2 1 100
148 31:42 25.8 51.2 1 130
…
148 31:56 2.9 51.2 1 133
The timestamps confirm that the statement completed after 18 seconds. The temporary
segment grew to 133 MB, somewhat less than the table's segment size. Obviously the entire
memory set aside with PAT is not available to a single session. Accordingly, additional undoc-
umented restrictions must be in place. Searching the Internet for “pga_aggregate_target tuning
undocumented”, one quickly realizes that several hidden parameters impact automatic PGA
memory management. The names of the hidden parameters are
_PGA_MAX_SIZE
,
_SMM_MAX_SIZE
,
and
_SMM_PX_MAX_SIZE
. Of these,
_PGA_MAX_SIZE
is in bytes and the other two in kilobytes (KB).
Descriptions and current values of these parameters are available by querying the X$ fixed
tables
X$KSPPI
and
X$KSPPCV
(see Part IV). The script
auto_pga_parameters.sql
, which queries
these X$ fixed tables and normalizes all four relevant parameters to kilobytes, is reproduced here:
SELECT x.ksppinm name,
CASE WHEN x.ksppinm like '%pga%' THEN to_number(y.ksppstvl)/1024
ELSE to_number(y.ksppstvl)
END AS value,
x.ksppdesc description
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND x.ksppinm IN ('pga_aggregate_target', '_pga_max_size',
'_smm_max_size', '_smm_px_max_size');
With the current settings, the script gives the following result:
C:> sqlplus -s / as sysdba @auto_pga_parameters
NAME Value (KB) DESCRIPTION
-------------------- ---------- --------------------------------------------
pga_aggregate_target 262144 Target size for the aggregate PGA memory
consumed by the instance
_pga_max_size 204800 Maximum size of the PGA memory for one
process
_smm_max_size 52428 maximum work area size in auto mode (serial)
_smm_px_max_size 131072 maximum work area size in auto mode (global)
