ora-4030

ORA-04030: out of process memory when trying to allocate 82456 bytes (pga heap,control file i/o buffer)
ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)

Patch 18384537: OPIPLS TAKES TOO MANY RETRIES TO LOAD CURSOR

ORA-04030 occurred while executing PLSQL procedure (Doc ID 1953999.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.4 to 12.1.0.1 [Release 11.2 to 12.1]
Information in this document applies to any platform.

SYMPTOMS

ORA-04030 occurred while executing PLSQL code

ORA-04030: out of process memory when trying to allocate 8528 bytes (pga heap,kgh stack)

Trace Files

The incident trace file confirms:

• The RDBMS version is 11.2.0.4

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

• A PLSQL object was executed

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
c000000c8f030c50       494  package body TC.PROC2
c000000c8f030c50       599  package body TC.PROC2
c000000cb45c1d18        92  package body TC.PROC1

• The Top 10 Memory used for this process is allocated by chunks labelled "kgh stack"

=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------

*** 2014-11-13 20:05:08.462
57%   18 GB, 2251612 chunks: "kgh stack                 "  
         pga heap        ds=6000000000059110  dsprt=0000000000000000
37%   12 GB, 11726744 chunks: "permanent memory          "
         callheap        ds=9ffffffffb4f0108  dsprt=600000000005eb70
 6% 1782 MB, 321681 chunks: "free memory               "
         pga heap        ds=6000000000059110  dsprt=0000000000000000
...

• There are multiple opipls() calls on the stack:

----- Call Stack Trace -----
...
opipls()+9424        call     kghstack_alloc()     6000000000059400 ?
...
opipls()+4256        call     opiexe()             9FFFFFFFFFFE3800 ?
...

Summary - The following must be true to confirm the bug:
 1. RDBMS release 11.2.0.4.0
 2. ORA-04030 due to "kgh stack" allocation.
 3. Multiple opipls() calls on the stack. 

CHANGES

Database was upgraded to 11.2.0.4

CAUSE

This is due to Bug 18160822 which was finally superseded by Bug 18328509 and finally by unpublished Bug 18384537.

Please refer to
Note 18160822.8 - Bug 18160822 - ORA-4030 after upgrade to 11.2.0.4 - superseded
Note 18328509.8 - Bug 18328509 - Fix for bug 18160822 can cause unexpected unconditional errorstack traces - superseded

NOTE:
Bug 18160822 as such was originally fixed in 12.1.0.1.
However, the fix for the superseding unpublished Bug 18384537 is not included until base release 12.2 and patch set 12.1.0.2

  

SOLUTION

To solve the issue, use any of below alternatives:

1. Upgrade to 12.1.0.2 or 12.2 (once this has been released)

- OR -

2. Apply interim Patch 18384537 if available for your platform and Oracle version.

    To check for conflicting patches, please use the MOS Patch Planner Tool
    Please refer to
    Note 1317012.1 - How To Use MOS Patch Planner To Check And Request The Conflict Patches?