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
相关推荐
6. What does the `--cuda-parallel-hash` flag do? [@davilizh](https://github.com/davilizh) made improvements to the CUDA kernel hashing process and added this flag to allow changing the number of ...
Parallel Ports and Printers. Serial Ports and Modems. Network Adapters and LANs. CMOS RAM and Realtime Clock. Keyboard. Mice and Other Pointing Devices. The Power Supply. Documentation....
Google Chrome浏览器增强版,采用原版加入shuax便携式Dll劫持补丁打包而成,Chrome++增强软件模块,强制实现flash插件支持,解除Adobe Flash Player...chrome://flags/#enable-parallel-downloading //改为Enabled开启
资源分类:Python库 所属语言:Python 资源全名:parallel_wget-0.0.6-py2.py3-none-any.whl 资源来源:官方 安装方法:https://lanzao.blog.csdn.net/article/details/101784059
资源分类:Python库 所属语言:Python 资源全名:parallel_ssh-2.7.1-py3-none-any.whl 资源来源:官方 安装方法:https://lanzao.blog.csdn.net/article/details/101784059
资源分类:Python库 所属语言:Python 资源全名:python-wd-parallel-0.0.1.macosx-10.7-intel.tar.gz 资源来源:官方 安装方法:https://lanzao.blog.csdn.net/article/details/101784059
mha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm...perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm perl-rrdtool-1.4.7-1.el5.rf.x86_64.rpm rrdtool-devel-1.3.8-7.el6.x86_64.rpm
it covers topics in a number of the Knowledge Areas of the Guidelines, including Software Development Fundamentals, Software Engineering, Systems Fundamentals, Parallel and Distributed Computing, ...
并行:此项目现在位于https://gitlab.redox-os.orgredox-osparallel的重写中
资源分类:Python库 所属语言:Python 资源全名:parallel-es2csv-0.1.8.tar.gz 资源来源:官方 安装方法:https://lanzao.blog.csdn.net/article/details/101784059
mha4mysql-manager-0.56-0.el6.noarch.rpm/mha4mysql-node-0.56-0.el6.noarch.rpm/perl-Log-Dispatch-2.27-1.el6.noarch.rpm/perl-Mail-Sender-0.8.16-3.el6.noarch.rpm...Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
The pervasiveness of multi-core processors affects a large spectrum of systems, from embedded and general-purpose, to high-end computing systems. This book assists programmers in mastering the ...
4、 利用 Parallel Stream 并行执行,提高构建速度;5、利用线程池进行多线程处理提高索引保存速度 6、利用 AOP 切面计算索引保存时间 7、搜索模块利用 HTML+CSS+JS 实现前端搜索功能 8、为索引构建 搜索树 操作方法...
拷贝jmeter-parallel-0.9.jar到Jmeter/lib/ext上。 启动Jmeter。 根据需要添加Parallel Controller: 1)在Jmeter的线程组下面的逻辑控制器,选择bzm并行控制器; 2)把浏览器或者wireShark观察到的同一批次的并发...
eclipse-parallel-2023-09-R-linux-gtk-aarch64.tar.gz 适用于Linux Arm系统
Eclipse IDE for Scientific Computing(eclipse-parallel-2022-06-R-linux-gtk-aarch64.tar.gz) 适用于Linux aarch64: Tools for C, C++, Fortran, and UPC, including MPI, OpenMP, OpenACC, a parallel ...
Differential and Common Signals and Odd- and Even-Mode Voltage Components Section 11.11. Velocity of Each Mode and Far-End Cross Talk Section 11.12. Ideal Coupled Transmission-Line Model or an Ideal ...
Parallel-ForkManager-0.7.9.tar.gz ExtUtils-MakeMaker-7.10.tar.gz ExtUtils-CBuilder-0.280205.tar.gz Config-Tiny-2.12.tar.gz mha4mysql-manager-0.58.tar.gz rpm -ivh perl-Params-Validate-1.06-2.18.x86_64....
资源分类:Python库 所属语言:Python 资源全名:parallel_ssh-1.5.4-cp27-cp27m-win_amd64.whl 资源来源:官方 安装方法:https://lanzao.blog.csdn.net/article/details/101784059
(1) 版本匹配问题: 比如Opencv版本opencv_python-3.4.3(+contrib)-cp36-cp36m-win_amd64.whl 应对应python3.6.~版本;(下载地址http://www.lfd.uci.edu/~gohlke/pythonlibs/)。 (2) 缺少Python3.dll:详细...