ORA-04030 Error With High "kkoutlCreatePh"
(Doc ID 1618444.1)
Last updated on FEBRUARY 15, 2019
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.3 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A 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
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=<SQL_ID>) -----
SELECT /*+ USE_CONCAT */ COUNT(*) FROM <TABLE_NAME1> P INNER JOIN <TABLE_NAME2> CA ON CA.<COLUMN1> = P.<COLUMN1> WHERE 1=1 AND (( (P.<COLUMN2> like :1 OR P.<COLUMN2> like :2 OR P.<COLUMN2> like :3 OR P.<COLUMN2> like :4 OR P.<COLUMN2> like :5 OR P.<COLUMN2> like :6 ) )) AND (( (P.CUST_SITE_NAME like :7 ) )) AND (( (P.<COLUMN3> like :8 OR P.<COLUMN3> like :9 OR P.<COLUMN3> like :10 OR P.<COLUMN3> like :11 OR P.<COLUMN3> like :12 OR P.<COLUMN3> like :13 OR P.<COLUMN3> like :14 OR P.<COLUMN3> like :15 OR P.<COLUMN3> like :16 OR P.<COLUMN3> like :17 OR P.<COLUMN3> like :18 OR P.<COLUMN3> like :19 OR P.<COLUMN3> like :20 OR P.<COLUMN3> like :21 OR P.<COLUMN3> like :22 OR P.<COLUMN3> like :23 OR P.<COLUMN3> like :24 OR P.<COLUMN3> like :25 OR P.<COLUMN3> like :26 OR P.<COLUMN3> like :27 OR P.<COLUMN3> like :28 OR P.<COLUMN3> like :29 OR P.<COLUMN3> like :30 OR P.<COLUMN3> like :31 OR P.<COLUMN3> like :32 OR P.<COLUMN3> like :33 OR P.<COLUMN3> like :34 OR P.<COLUMN3> like :35 OR P.<COLUMN3> like :36 OR P.<COLUMN3> like :37 OR P.<COLUMN3> like :38 OR P.<COLUMN3> like :39 OR P.<COLUMN3> like :40 OR P.<COLUMN3> like :41 OR P.<COLUMN3> like :42 OR P.<COLUMN3> like :43 OR P.<COLUMN3> like :44 OR P.<COLUMN3> like :45 OR P.<COLUMN3> like :46 OR P.<COLUMN3> like :47 OR P.<COLUMN3> like :48 OR P.<COLUMN3> like :49 OR P.<COLUMN3> like :50 OR P.<COLUMN3> like :51 OR P.<COLUMN3> like :52 OR P.<COLUMN3> like :53 OR P.<COLUMN3> like :54 OR P.<COLUMN3> like :55 OR P.<COLUMN3> like :56 OR
...
SELECT /*+ USE_CONCAT */ COUNT(*) FROM <TABLE_NAME1> P INNER JOIN <TABLE_NAME2> CA ON CA.<COLUMN1> = P.<COLUMN1> WHERE 1=1 AND (( (P.<COLUMN2> like :1 OR P.<COLUMN2> like :2 OR P.<COLUMN2> like :3 OR P.<COLUMN2> like :4 OR P.<COLUMN2> like :5 OR P.<COLUMN2> like :6 ) )) AND (( (P.CUST_SITE_NAME like :7 ) )) AND (( (P.<COLUMN3> like :8 OR P.<COLUMN3> like :9 OR P.<COLUMN3> like :10 OR P.<COLUMN3> like :11 OR P.<COLUMN3> like :12 OR P.<COLUMN3> like :13 OR P.<COLUMN3> like :14 OR P.<COLUMN3> like :15 OR P.<COLUMN3> like :16 OR P.<COLUMN3> like :17 OR P.<COLUMN3> like :18 OR P.<COLUMN3> like :19 OR P.<COLUMN3> like :20 OR P.<COLUMN3> like :21 OR P.<COLUMN3> like :22 OR P.<COLUMN3> like :23 OR P.<COLUMN3> like :24 OR P.<COLUMN3> like :25 OR P.<COLUMN3> like :26 OR P.<COLUMN3> like :27 OR P.<COLUMN3> like :28 OR P.<COLUMN3> like :29 OR P.<COLUMN3> like :30 OR P.<COLUMN3> like :31 OR P.<COLUMN3> like :32 OR P.<COLUMN3> like :33 OR P.<COLUMN3> like :34 OR P.<COLUMN3> like :35 OR P.<COLUMN3> like :36 OR P.<COLUMN3> like :37 OR P.<COLUMN3> like :38 OR P.<COLUMN3> like :39 OR P.<COLUMN3> like :40 OR P.<COLUMN3> like :41 OR P.<COLUMN3> like :42 OR P.<COLUMN3> like :43 OR P.<COLUMN3> like :44 OR P.<COLUMN3> like :45 OR P.<COLUMN3> like :46 OR P.<COLUMN3> like :47 OR P.<COLUMN3> like :48 OR P.<COLUMN3> like :49 OR P.<COLUMN3> like :50 OR P.<COLUMN3> like :51 OR P.<COLUMN3> like :52 OR P.<COLUMN3> like :53 OR P.<COLUMN3> like :54 OR P.<COLUMN3> like :55 OR P.<COLUMN3> like :56 OR
...
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |