ORA-04030 Error With High "kkoutlCreatePh" (Doc ID 1618444.1)

Last updated on FEBRUARY 17, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

1. ORA-4030 error encountered. The alert log reports next error details:

ORA-04030: out of process memory when trying to allocate 140192 bytes (kkoutlCreatePh,apppred : kkotbalp)


2. The trace file shows memory allocations like:

*** 2013-10-26 03:09:49.145
70% 5670 MB, 135978 chunks: "permanent memory          "  SQL
        kkoutlCreatePh  ds=0x2b7f3d3085e0  dsprt=0x2b7f3cc6da28
15% 1206 MB, 140500 chunks: "permanent memory          "  SQL
        kxs-heap-c      ds=0x2b7f3cc6da28  dsprt=0xbafa4e0
6%  461 MB, 33687 chunks: "free memory               "  
        top call heap   ds=0xbafa4e0  dsprt=(nil)
2%  195 MB, 1823970 chunks: "optdef: qcopCreateOptInte "  
        TCHK^92764db7   ds=0x2b7f3cafff48  dsprt=0xbaf95c0
2%  136 MB, 1979074 chunks: "logdef: qcopCreateLog     "  
        TCHK^92764db7   ds=0x2b7f3cafff48  dsprt=0xbaf95c0
2%  123 MB, 2682059 chunks: "travNode:qkspmTravInsertP "  SQL

Note the very large memory allocation for "kkoutlCreatePh"

3. The current SQL has many inlist predicates, e.g.:

----- Current SQL Statement for this session (sql_id=1trjsma97cmdr) -----
SELECT /*+ USE_CONCAT */ COUNT(*) FROM  PPFF_COLLAB P INNER JOIN PPFF_COLLAB_ATTRIBUTE CA ON CA.COLLAB_ID = P.COLLAB_ID  WHERE 1=1  AND (( (P.SUPPLIER_NAME like :1  OR P.SUPPLIER_NAME like :2  OR P.SUPPLIER_NAME like :3  OR P.SUPPLIER_NAME like :4  OR P.SUPPLIER_NAME like :5  OR P.SUPPLIER_NAME like :6 ) )) AND (( (P.CUST_SITE_NAME like :7 ) )) AND (( (P.CUST_ITEM_NAME like :8  OR P.CUST_ITEM_NAME like :9  OR P.CUST_ITEM_NAME like :10  OR P.CUST_ITEM_NAME like :11  OR P.CUST_ITEM_NAME like :12  OR P.CUST_ITEM_NAME like :13  OR P.CUST_ITEM_NAME like :14  OR P.CUST_ITEM_NAME like :15  OR P.CUST_ITEM_NAME like :16  OR P.CUST_ITEM_NAME like :17  OR P.CUST_ITEM_NAME like :18  OR P.CUST_ITEM_NAME like :19  OR P.CUST_ITEM_NAME like :20  OR P.CUST_ITEM_NAME like :21  OR P.CUST_ITEM_NAME like :22  OR P.CUST_ITEM_NAME like :23  OR P.CUST_ITEM_NAME like :24  OR P.CUST_ITEM_NAME like :25  OR P.CUST_ITEM_NAME like :26  OR P.CUST_ITEM_NAME like :27  OR P.CUST_ITEM_NAME like :28  OR P.CUST_ITEM_NAME like :29  OR P.CUST_ITEM_NAME like :30  OR P.CUST_ITEM_NAME like :31  OR P.CUST_ITEM_NAME like :32  OR P.CUST_ITEM_NAME like :33  OR P.CUST_ITEM_NAME like :34  OR P.CUST_ITEM_NAME like :35  OR P.CUST_ITEM_NAME like :36  OR P.CUST_ITEM_NAME like :37  OR P.CUST_ITEM_NAME like :38  OR P.CUST_ITEM_NAME like :39  OR P.CUST_ITEM_NAME like :40  OR P.CUST_ITEM_NAME like :41  OR P.CUST_ITEM_NAME like :42  OR P.CUST_ITEM_NAME like :43  OR P.CUST_ITEM_NAME like :44  OR P.CUST_ITEM_NAME like :45  OR P.CUST_ITEM_NAME like :46  OR P.CUST_ITEM_NAME like :47  OR P.CUST_ITEM_NAME like :48  OR P.CUST_ITEM_NAME like :49  OR P.CUST_ITEM_NAME like :50  OR P.CUST_ITEM_NAME like :51  OR P.CUST_ITEM_NAME like :52  OR P.CUST_ITEM_NAME like :53  OR P.CUST_ITEM_NAME like :54  OR P.CUST_ITEM_NAME like :55  OR P.CUST_ITEM_NAME like :56  OR
...

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms