oracle sga 4031,并行执行与ORA-4031

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.

阅读(844) | 评论(0) | 转发(0) |