Parallel Execution: Large/Shared Pool and ORA-4031 (文档 ID 238680.1)

Applies to
Oracle Database - Enterprise Edition - Version 8.1.7.0 and later
Information in this document applies to any platform.

Purpose
Give some information what can be check if we get and ORA-4031 and we use Parallel Execution

Scope
Support Analysts and DBA's

Details
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.
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.  
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.

参考至:https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=80dsfjl8t_4&_afrLoop=446004159656844

如有错误，欢迎指正

邮箱:czmcj@163.com