当前位置:首页 > 开发 > 数据库 > 正文

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

发表于: 2014-07-17   作者:czmmiao   来源:转载   浏览次数:
摘要: Applies toOracle Database - Enterprise Edition - Version 8.1.7.0 and laterInformation in this document applies to any platform.PurposeGive some information what can be check if we get and ORA-4031 and

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

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

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
一. Shared Pool 概述 在之前的blog对Oracle 的内存架构也做了一个概述,参考: Oracle 内存 架构
原博客地址:http://blog.csdn.net/robinson1988/article/details/6037925 由于shared pool中最重要
shared pool的概述 oracle数据库作为一个管理数据的产品,必须能够认出用户所提交的管理命令(通常
一. Shared Pool 概述 在之前的blog对Oracle 的内存架构也做了一个概述,参考: Oracle 内存 架构
shared pool主要由保存数据字典的data_dictionary和保存SQL和PL/SQL代码和执行计划的library cache组
Oracle shared pool 主要分为library cache 、dictionary cache 和control structure 三个部分。 第
shared pool主要由保存数据字典的data_dictionary和保存SQL和PL/SQL代码和执行计划的library cache组
  Shared pool物理层面上由许多内存块(chunck)组成。从逻辑功能划分,Shared pool主要由三部分组
关于shared pool的深入探讨(四) <!-- #BeginDate format:wfcIS1m -->link: http://www.eygle.
关于shared pool的深入探讨(四) <!-- #BeginDate format:wfcIS1m -->link: http://www.eygle.
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号