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
 
Search WWH ::




Custom Search