Database Reference
In-Depth Information
remains in effect for PX, such that the available memory is the lesser of
_SMM_MAX_SIZE
and
_SMM_PX_MAX_SIZE
/DOP. To sort entirely in memory, these two conditions must be met:
The data volume per PX process must be less than
_SMM_MAX_SIZE
.
The data volume per PX process must be less than
_SMM_PX_MAX_SIZE
/DOP.
Let's run some examples. The previous tests revealed that the
SELECT
from the test table
has a data volume of about 133 MB. Thus, at a DOP of four, each PX process requires a work
area size of around 133 MB divided by 4, or approximately 34 MB for an optimal sort. Rounding
up slightly to 40 MB to allow for fluctuations of the data volume among PX processes, we will
set
_SMM_MAX_SIZE=40960
, since the unit of
_SMM_MAX_SIZE
is KB. To avoid
PGA_AGGREGATE_TARGET
or
_SMM_PX_MAX_SIZE
becoming the limiting factor, we also set both parameters to DOP times
_SMM_MAX_SIZE
or 160 MB. To set these parameters, place the following three lines into a parameter
file and restart the instance with
STARTUP
PFILE
:
pga_aggregate_target=160m
_smm_px_max_size=163840 # in KB
_smm_max_size=40960 # in KB
Verifying the settings with the script
auto_pga_parameters.sql
gives this result:
NAME Value (KB) DESCRIPTION
-------------------- ---------- --------------------------------------------
pga_aggregate_target 163840 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 40960 maximum work area size in auto mode (serial)
_smm_px_max_size 163840 maximum work area size in auto mode (global)
Next, a
FULL
and a
PARALLEL
hint must be added to the
SELECT
statement to enable parallel
execution.
SQL> SELECT /*+ FULL(r) PARALLEL(r, 4) */ * FROM random_strings r ORDER BY 1;
Running the parallel query at a DOP of four and monitoring it with
sql_workarea_
active.pl
gives this:
SID TIME WORK_AREA_SIZE MAX_MEM_USED PASSES TEMPSEG_SIZE
143 06:36 1.7 1.6 0 0
144 06:36 1.7 1.6 0 0
145 06:36 2.6 2.1 0 0
146 06:36 1.7 1.6 0 0
…
145 06:43 32.3 39.6 0 0
…
146 06:46 31.6 31.6 0 0
…
144 06:48 31.4 31.4 0 0
…
143 06:50 31.2 31.2 0 0
