ORA-4030.pdf
免费下载
2021/9/13 Document 399497.1
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=fkw3gtoxm_264&id=399497.1 1/20
Copyright (c) 2021, Oracle. All rights reserved. Oracle Confidential.
FAQ: ORA-4030 (Doc ID 399497.1)
In this Document
Purpose
Common Bugs
Questions and Answers
What is an ORA-4030?
What is difference between 4030 and 4031?
What are the contents of Program Global Area memory?
Why do I see processes growing larger than the
PGA_AGGREGATE_TARGET/MEMORY_TARGET/MEMORY_MAX/TARGET?
Can you control the size of a process?
Can you limit the size of a process?
What information needs to be gathered to diagnose?
Why does my code give ORA-4030 when run through listener connection, but not local connection?
What to look at in RDA?
What kernel or shell limits need to be checked?
How to monitor pga usage from within the database?
How to monitor memory usage from the OS on UNIX/Linux?
How to monitor memory usage from the OS on MS-Windows?
Why do we still get ORA-4030 or ORA-12500 on MS-Windows 32 bit database after adding more RAM?
How to create a heapdump?
What heapdump level to gather?
See high amount of 'perm' allocations. When to set 10235 event?
Platform Specific Configurations leading to memory problems
All 64 bit unix/linux platforms - 16G pga size limit with realfree memory allocator
Community discussion
References
APPLIES TO:
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 8.1.5.0 and later
Information in this document applies to any platform.
PURPOSE
This article is intended to
Help the reader understand causes of the ORA-4030
Gather the diagnostics needed to narrow down the errors
Answer some of the common questions asked about ORA-4030
This is valid not only for ORA-4030 errors, but for any occurrence when the oracle database processes (user or
background PGA) are suspected of consuming large amount of memory or potential memory leak.
You may not get errors and just see high memory usage – ulimit on some platforms/versions no longer cause ORA-
4030 to occur
2021/9/13 Document 399497.1
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=fkw3gtoxm_264&id=399497.1 2/20
Bug Reported Fixed Notes Details
Unpublished
Bug:3130972
Versions <
10.1.0.2
9.2.0.6/10.1.0.2.0 Backports
possible
The realfree allocator on Unix systems imposes a cap at
1Gb of memory per process. This fix relaxes that limit as
in some cases it is desirable to allow a process to use over
1Gb of private memory. If the limit is reached an
ORA-4030 occurs even though the system may have
plenty of free memory available and can map that
memory into the
process address space.
(The realfree allocator is used on Unix systems where
PGA_AGGREGATE_TARGET is in use)
Workaround:
Configure the system to allow less than 1Gb of memory
per process to avoid ORA-4030 errors.
Bug:3565920 Versions <
10.1
9.2.0.8 and
higher
Backports
no longer
If the shared pool is resized then subsequeunt queries
against views based on
X$KSMSP, such as V$SHARED_POOL_RESERVED, result in
a memory leak in the cursor work heap which then fails
with ORA-4030.
Bug:4475206 Versions <
10.2.0.4
10.2.0.4/11.1.0.6 Backports
possible
The PGA memory consumed becomes significantly higher
than the value of the
parameter pga_aggregate_target if a query has a very
long chain of
hash-join operations. This chain must be right-deep, ie.
the build is
performed on a base table and the probe is produced by
an hash-join sub-tree.
This is not really a memory leak but excess memory use
compared to what
should be used.
Unpublished
Bug:4625938
Versions <
10.2
10.2 10.1.x A memory leak involving 'peihstdep' and 'PEIDEF' can
occur when TABLE functions are used and JDBC
connection pooling is enabled.
Workaround:
Disable connection pooling.
Bug:5118748 Versions <
10.2.0.3
10.2.0.3/11.1.0.6 Backports
possible
ORA 4030 or a memory leak can occur when using a lot of
collections in PLSQL. Heapdumps of the "koh-kghu call "
heap include multiple chunks of type "pmucp2upkl korfp
May get other errors such as ORA-12500
ora-600 [729] (UGA memory) or ora-600 [730] (SGA or large pool)
**************************************************************************************
There is now a Diagnostic Tool available through My Oracle Support to help diagnose ORA-4030 issues.
This tool will provide suggestions to resolve ORA-4030 issues by analyzing uploaded RDA files. Individual
processing of trace files or IPS packages is not currently implemented, but please check back since this
functionality is planned for in an upcoming release.
Select this link ORA-4030 Troubleshooting Tool to open a new browser window.
How to use the ORA-4030 Troubleshooting Tool on My Oracle Support Document: 1511048.1
**************************************************************************************
Note:
If you would like to explore this topic further, please join the Community discussion 'Diagnosing and Resolving ORA-
4030 errors' where you can ask questions, get help from others, and share your experiences with this specific article.
Common Bugs
2021/9/13 Document 399497.1
https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=fkw3gtoxm_264&id=399497.1 3/20
Bug:5220562 Versions <
10.2.0.4
10.2.0.4/11.1.0.6 Backports
possible
An insert select DML session's process size may increases
/ ORA-4030
(with sort sub heap chunks) when there is concurrent
DDL on the
partitioned tables / objects involved in the DML.
Workaround:
Avoid concurrent execution of the DML and DDL, if
possible.
Bug:5389854 Versions <
10.2.0.4
10.2.0.4/11.1.0.6 Backports
possible
Execution of a procedure using bulk insert with save
exceptions will
consume large amounts of PGA memory during execution.
If run with
extremely large number of rows or for a long period of
time this can
lead to ORA-4030.
The memory shows up on the "callheap,DARWIN"
subheap as "koh-kghu call" memory
Bug:5391505 Versions <
10.2.0.4
10.2.0.4/11.1.0.6 Backports
possible
PGA memory may keep on increasing during query
parsing and can reach a large
amount (sometimes even over 1G) when OR expansion
occurs.
Ultimately ORA-4030 may be encountered as memory
runs out.
The memory shows as "perm" space in the "kxs-heap-c"
subheap.
Workaround:
alter session set "_no_or_expansion" = true
Bug:5464834 Versions <
10.2.0.4
10.2.0.4/11.1.0.6 Backports
possible
ORA-4030 (kxs-heap-c,temporary memory) can occur
when using EXPDP
Bug:5866410 Versions < 11 11.1.0.6 Backports
possible
Bulk insert in PLSQL can consume a large amount of PGA
memory
which can lead to ORA-4030 errors.
A heapdump will show lot of free memory in the free lists
which
is not used but instead fresh allocations are made.
Workaround:
Chunk the FORALL loop. Do a hybrid of FOR & FORALL so
that the
bulk_rowcount arrays doesnt grow abnormally large
Bug:5947623 Versions >=
10.2.0.1 but
< 11.1.0.7
10.2.0.4/11.1.0.7 Backport
possible
it is possible for a query to allocate too much memory
executing a hash join
over large volumes of data with few distinct join key
values.
The impact on 64-bit systems is greater.
This is not really a memory leak as the fix only makes the
query to spill to disk earlier.
Workaround: set "_pga_max_size"
Bug:6011182 Versions >=
10.2.0.1 but
< 10.2.0.4
10.2.0.4 Backport
possible
High elapsed time and high memory consumption during
parse
can occur for queries with large numbers of query blocks.
If you see high elapsed times and/or memory
consumption during parse for a
query, and the query has a large number of query blocks
(eg many views,
subqueries or UNION ALL branches) you may be hitting
this bug.
For error ORA-04030
The path of the leak is: top call heap -> call heap ->
typecheck
largest memory allocations w/comments:
"logdef: qcopCre", "kkoFroAnn: qksf", "frodef: qksfroC"
of 20
免费下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
相关文档
评论