FAQ: ORA-4030 [Video] [ID 399497.1]

原创 2017年01月03日 17:13:40
FAQ: ORA-4030 [Video] [ID 399497.1]

  修改时间 21-SEP-2011     类型 FAQ     状态 PUBLISHED  

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?
     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 MSwindows?
     Why do we still get ORA-4030 or ORA-12500 on MSwindows 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?
     4G Memory limitation in memory mapped systems using realfree allocator
     Configurations leading to excessive memory usage problems
  References




Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.5.0 to 11.2.0.2 - Release: 8.1.5 to 11.2
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
  • May get other errors such as ORA-12500
    ora-600 [729] (UGA memory) or ora-600 [730] (SGA or large pool)

Common Bugs

Notes:
Backport possibilities are only to indicate technical backport or patchset exception may be possible.
Actual availability of backports/PSEs are subject to backport policies as per
Note 209768.1 Pub Database, FMW, and OCS Software Error Correction Support Policy 


Bug
Reported
Fixed
Notes
Details
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.
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
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"
Bug 6052169
Versions < 11
11.1.0.6
Backports possible
Slave memory grows unbounded and finally fails with ORA-4030.
A heapdump of the memory shows 'Statement Alloc' string.
Bug 6061892
Versions >= 10.2.0.1 but < 10.2.0.4
10.2.0.4/11.1.0.6

Backports possible
It is possible to get error ORA-4030 and ORA-21780 when an application
that drops and/or recreates many plsql packages or top-level
Noteocedures or functions which are used in calls from SQL to PL/SQL.
The leak is found in the heaps:
pga heap -> session heap -> PLS non-lib hp.
Most of the hunks on heap 'PLS non-lib hp' are PEIDEF or peihstdep
Note:
This fix introduces the problem described in bug 6139254
Bug 6408917
Versions < 11
11.1.0.6
Backports possible
Excessive PGA memory consumption can be seen when using ref cursors returned
from Java stored procedures. Ultimately this can lead to out of memory
errors such as:
ORA-29532: java call terminated by uncaught java exception: java.lang.outofmemory
Bug 6414844
Versions < 11.2
11.2
Backports possible to 10.1
memory may be wasted in a subheap using kghu memory allocation.

A heapdump will show free chunks in each extent with a "free" chunk which is
not quite large enough to satisfy a request. This is not really a memory
leak but inefficient use of memory in certain circumstances.
eg:
EXTENT 0 addr=0xb3810008
Chunk b3810010 sz= 16272 free " "  Wasted space
Chunk b3813fa0 sz= 16416 freeable "koh-kghu call "
Chunk b3817fc0 sz= 16416 freeable "koh-kghu call " ds=0xb7a1daf0
Chunk b381bfe0 sz= 16416 freeable "koh-kghu call " ds=0xb7a1daf0






















































版权声明:本文为博主原创文章,未经博主允许不得转载。

ASMB process grows raising ora-4030 intermittently (Doc ID 735180.1)

1.1 常熟项目数据库打补丁 1.1.1 项目名称 常熟项目 1.1.2 问题描述 针对 11月19号早上9数据库故障修复 1.1.3 影响范围 申请时间凌晨停机维护 1.1.4 问题分...

一次ORA-4030问题诊断及解决【解决思路不错,说明了对象的统计信息与优化器的优化操作(即选择执行一个SQL语句在该优化参数环境下最佳的执行计划)间的关系】

在报表数据库的后台alert文件中发现了这个错误,简单记录一下问题的诊断和解决过程。数据库版本9204 for Solaris sparc64。   错误信息如下:   Errors in file ...
  • haiross
  • haiross
  • 2014年02月12日 09:13
  • 1390

How To Avoid ORA-04030/ORA-12500 In 32 Bit Windows Environment [Video] [ID 373602.1]

How To Avoid ORA-04030/ORA-12500 In 32 Bit Windows Environment [Video] [ID 373602.1]    In this Do...

MTK Video FAq 2

2. 23C不能实现无限制录影,录相只能10秒左右[Version]N/A[DESCRIPTION]N/A[SOLUTION]第一,23C的录相机制与25不相同,不能实现无限制录像,这是平台限制。第二...

[MySQL FAQ]系列 — 如何查看当前最新事务ID

写在前面:在个别时候可能需要查看当前最新的事务ID,以便做一些业务逻辑上的判断(例如利用事务ID变化以及前后时差,统计每次事务的响应时长等用途)。 通常地,我们有两种方法可以查看当前的事务ID: ...

Master Note: How to diagnose Database Performance - FAQ [ID 402983.1]

如何利用oracle工具诊断性能问题,这篇问题起到提纲的作用,可以根据文中提到的方法,顺藤摸瓜,确定并解决问题...

Highly Available IP (HAIP) FAQ for release 11.2 (Doc ID 1664291.1)

Highly Available IP (HAIP) FAQ for release 11.2 (Doc ID 1664291.1) To Bottom ...

[MySQL FAQ]系列 — 如何查看当前最新事务ID

[MySQL FAQ]系列 — 如何查看当前最新事务ID 发表回复 写在前面:在个别时候可能需要查看当前最新的事务ID,以便做一些业务逻辑上的判断(例如利用事务ID变化以及前后...

FAQ: 12c Grid Infrastructure Management Repository (GIMR) (文档 ID 1568402.1)

FAQ: 12c Grid Infrastructure Management Repository (GIMR) (文档 ID 1568402.1)

FAQ: OPEN INTERFACES - ASN Transactions (Doc ID 225233.1)

In this Document Purpose   Questions and Answers    What is an ASN?    What a...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:FAQ: ORA-4030 [Video] [ID 399497.1]
举报原因:
原因补充:

(最多只允许输入30个字)