Parallel Execution slave have to exchange data and messages that they can work together.
For this we have to allocate memory from the shared pool or large pool.
This decision depends to which value PARALLEL_AUTOMATIC_TUNING is set.
If PARALLEL_AUTOMATIC_TUNING = TRUE than we take memory from the large pool
otherwise we use the shared pool. In this case we increase automatically
the large pool that it should big enough.
The hidden parameter _PX_use_large_pool can also be used to control
from which pool we allocate the memory.
At startup of the database we allocate some memory for the "PX msg pool"
to avoid fragmentation and get faster memory for the PX buffers,
when PARALLEL_AUTOMATIC_TUNING or PARALLEL_MIN_SERVERS is set.
In 10g, PX message buffers are allocated from large pool if
a.) parallel_automatic_tuning = true (deprecated)
or
b.) _PX_use_large_pool = true
or
c.) sga_target is set
In 11g, PX message buffers are allocated from large pool if
a.) parallel_automatic_tuning = true (deprecated)
or
b.) _PX_use_large_pool = true
or
c.) SGA memory is auto tuned (sga_target or memory_target)
You can monitor from which area we allocate memory for PX msg buffer when
we query V$SGASTAT and look for the "PX msg pool"
The PX message buffer can become very large. Another area in the shared pool where
we allocate memory for PX operation is the "PX subheap". This heap is
small when we compare it with the "PX msg pool" and always in the shared pool.
SQL> select * from v$sgastat;
POOL NAME BYTES
----------- -------------------------- ----------
fixed_sga 453632
log_buffer 656384
shared pool enqueue 179220
..
shared pool PX subheap 167104 <<<
..
shared pool event statistics per sess 1889720
shared pool fixed allocation callback 184
large pool PX msg pool 2949120 <<<<
large pool free memory 5439488
java pool free memory 25165824
If PARALLEL_AUTOMATIC_TUNING = FALSE the shared pool will not be increased.
The user is responible to find a adequate size for the shared pool.
The size of the PX msg depends also on size of the parameter
PARALLEL_EXECUTION_MESSAGE_SIZE and the degree of parallelism.
To obtain more memory usage statistics, execute the following query:
SELECT * FROM V$PX_PROCESS_SYSSTAT WHERE STATISTIC LIKE 'Buffers%';
Your output should resemble the following:
STATISTIC VALUE
------------------- -----
Buffers Allocated 23225
Buffers Freed 23225
Buffers Current 0
Buffers HWM 3620
4 Rows selected.
The amount of memory used appears in the Buffers Current and Buffers HWM
statistics. Calculate a value in bytes by multiplying the number of buffers
by the value for PARALLEL_EXECUTION_MESSAGE_SIZE.
The formula is not 100% correct. To avoid fragmentation of the shared/large pool
we allocate in large chunks. This means the value of 'PX msg pool' can be larger
than 'Buffers HWM' * PARALLEL_EXECUTION_MESSAGE_SIZE.
More information about the Message Buffers and adjusting memory you find in
Data Warehousing Guide
Chapter Parallel Execution
Tuning General Parameters for Parallel Execution
Recommendation:
When you receive an ORA-4031 and you have set PARALLEL_AUTOMATIC_TUNING = FALSE
then consider to set it to true that Oracle can tune the large pool.
When you have PARALLEL_AUTOMATIC_TUNING = TRUE than increase the large pool.
In both cases please also check the value of PARALLEL_EXECUTION_MESSAGE_SIZE.
