Seeing ORA-4030 and ORA-04036 Errors in 12.1.0.2 Database During Select (Doc ID 2017430.1)

Last updated on NOVEMBER 07, 2016

Applies to:

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

Symptoms

You are running a select query in 12.1.0.2 and it fails with the following errors:

ORA-04030: out of process memory when trying to allocate 4272 bytes (kxs-heap-c,temporary memory)
and
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

 

The trace file shows a large consumption of the kxs-heap-c area of the Permanent Memory for the process:

FOR EXAMPLE:

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

*** 2015-05-08 05:19:22.303
66%   21 GB, 1657545 chunks: "permanent memory          "  SQL 
         kxs-heap-c      ds=0x7fa257d500e8  dsprt=0x7fa257ef3b60
25% 8114 MB, 522345 chunks: "free memory               "  
         top call heap   ds=0x7fa257ef3b60  dsprt=(nil)
 9% 2845 MB, 1513535 chunks: "free memory               "  SQL
         kxs-heap-c      ds=0x7fa257d500e8  dsprt=0x7fa257ef3b60
 0%  792 KB, 20025 chunks: "chedef : qcuatc           "  
         TCHK^c724de48   ds=0x7fa257d5feb8  dsprt=0x7fa257ef2960
 0%  693 KB,  39 chunks: "permanent memory          "  
         pga heap        ds=0x7fa257eed960  dsprt=(nil)
 0%  479 KB, 3358 chunks: "opndef: qcopCreateOpnViaM "  
         TCHK^c724de48   ds=0x7fa257d5feb8  dsprt=0x7fa257ef2960
 0%  281 KB, 474 chunks: "ckydef : kkdlcky          "  
         TCHK^c724de48   ds=0x7fa257d5feb8  dsprt=0x7fa257ef2960
 0%  261 KB, 1115 chunks: "kkqsucfe2p                "  
         TCHK^c724de48   ds=0x7fa257d5feb8  dsprt=0x7fa257ef2960
 0%  248 KB, 1513 chunks: "coldef: qcopCreateCol     "  
         TCHK^c724de48   ds=0x7fa257d5feb8  dsprt=0x7fa257ef2960
 0%  224 KB,   3 chunks: "free memory               "  
         top uga heap    ds=0x7fa257ef3d80  dsprt=(nil)
 
=======================================

 

And the stack trace maybe similar to:

kghnospc <- kghalp <- ksmcat <- kkqsmem <- kpp_concatun <- qsme_revpolish_terminal <- qsme_revpolish_operator <- qsme_revpolish_operator <- qsme_revpolish_operator <- qsme_revpolish_operator <- qsme_normalize_opn <- kkqspgf <- kkqsMeasureRollupCk <- kkqsPrepareMeasureCk <- kqstmc  <- kkqsGeneralInstNoMapping <- kkqsMapInstanceNumbers <- kkqsResetInstanceNumbers <- kkqsechk <- kkqspsum <- kkqsedrv  <- kkqseqb <- kkqsrqb <- kkqsrmav <- kkqsRewriteCurrentQB ...

 



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