Database Reference
In-Depth Information
NAME Value (KB)
-------------------- ----------
pga_aggregate_target 1536000
_pga_max_size 307200
_smm_max_size 153600
_smm_px_max_size 768000
C:> sql_workarea_active_hash.pl
SID TIME HASH_VALUE TYPE WORK_AREA_SIZE MAX_MEM_USED PASSES TMP_SIZE
159 57:46 1705656915 SORT (v2) 133.7 133.7 0 0
159 57:46 3957124346 HASH-JOIN 16.2 15.7 0 105
. . .
159 57:52 1705656915 SORT (v2) 133.7 133.7 0 0
159 57:52 3957124346 HASH-JOIN 108.3 96.1 1 138
Output from the Perl script
sql_workarea_active_hash.pl
includes the columns
HASH_VALUE
and
TYPE
from
V$SQL_WORKAREA_ACTIVE
. Both work areas combined exceed
_SMM_MAX_SIZE
, but not
_PGA_MAX_SIZE
.
_SMM_PX_MAX_SIZE
The setting of
_SMM_PX_MAX_SIZE
is always 50% of
PGA_AGGREGATE_TARGET
. There is no limit on
_SMM_PX_MAX_SIZE
(at least not within the tested range of
PGA_AGGREGATE_TARGET
of 10 MB to
32 GB). In Oracle9
i
,
_SMM_PX_MAX_SIZE
was 30% of
PGA_AGGREGATE_TARGET
.
Shared Server
In Oracle10
g
, Shared Server was recoded to use automatic PGA memory management. Oracle9
i
Shared Server uses the
*_AREA_SIZE
Parameters, i.e., it behaves as if
ALTER
SESSION
SET
WORKAREA_
SIZE_POLICY=MANUAL
had been executed. Hence it is valid to leave
SORT_AREA_SIZE
inside an
Oracle9
i
PFILE
or
SPFILE
and to set it to a more useful value, such as 1048576, than the default
65536. Of course it is still valid to set meaningful values for
SORT_AREA_SIZE
,
HASH_AREA_SIZE
,
and so on in Oracle10
g
, for sessions that might run with manual work area sizing (
WORKAREA_
SIZE_POLICY=MANUAL
).
Parallel Execution
The hidden parameter
_SMM_PX_MAX_SIZE
applies to parallel execution, but exactly how needs
to be revealed by further tests. Regarding parallel execution (PX), it is important to bear in
mind that a parallel full scan of a table at degree
n
divides the work among
n
parallel execution
processes, such that the volume of data handled by each process equates to approximately one
n
th of the entire data volume. The figure
n
is commonly called the degree of parallelism or DOP.
Each parallel execution process allocates its own work area(s). Since each process handles
merely a fraction of the data, the work areas required by individual processes in parallel mode
are smaller than a single work area in serial mode.
It turns out that
_SMM_PX_MAX_SIZE
places an additional restriction on the maximum work
area size, which is exercised on parallel execution processes. Each PX process may not use
more than
_SMM_PX_MAX_SIZE
/DOP memory. The per process restriction of
_SMM_MAX_SIZE