ORA-4030 With PGA Leak Using Aggregation In A Query Under 12.1 (Doc ID 1967583.1)

Last updated on APRIL 24, 2015

Applies to:

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

Symptoms

Alert log reports ORA-4030 errors like:

Tue Sep 02 09:56:45 2014
Errors in file /media/DATA/DATABASE/oradata/diag/rdbms/sbx14sdh/SBX14SDH/trace/SBX14SDH_ora_10135.trc  (incident=25355):
ORA-04030: out of process memory when trying to allocate 31784 bytes (sort subheap,sort key)
Incident details in: /media/DATA/DATABASE/oradata/diag/rdbms/sbx14sdh/SBX14SDH/incident/incdir_25355/SBX14SDH_ora_10135_i25355.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /media/DATA/DATABASE/oradata/diag/rdbms/sbx14sdh/SBX14SDH/trace/SBX14SDH_ora_10135.trc  (incident=25356):
ORA-04030: out of process memory when trying to allocate 32792 bytes (callheap,kdbmal allocation)
ORA-04030: out of process memory when trying to allocate 31784 bytes (sort subheap,sort key)
Incident details in: /media/DATA/DATABASE/oradata/diag/rdbms/sbx14sdh/SBX14SDH/incident/incdir_25356/SBX14SDH_ora_10135_i25356.trc


Trace file shows next information:

*** 2014-09-09 13:43:03.087
87%   28 GB, 3620597 chunks: "permanent memory          "  SQL
        kxs-heap-w      ds=0x7fdbce47ef68  dsprt=0x7fdbcf48cf60


The call stck in the trace file is similar to the next one:

kghnospc           KGH: There is no space available in the heap, signal an error.
kghalp             KGH: Allocate permanent memory
stsAllocFromSubheap  
smbalo             SMBIMA: allocate an smkdef from specified storage bin
smbaloIS            
smbput             SMBIMA: put a record into the memory buffer.
sorputqb           SORt PUTQB:  Insert record into sort
qergsRowP          
qervwRowProcedure  
qerwnFetch          
qervwFetch         View row source - fetch
qergsFetch         group by sort row source fetch


The heap dump should show that the chunks are allocated from qesaQBInit():

 HEAP DUMP heap name="kxs-heap-w"  desc=0x2b7247a713d8 
 extent sz=0x1000 alt=32767 het=32767 rec=0 flg=2 opc=2 
 parent=0x2b724763d740 owner=(nil) nex=(nil) xsz=0x2008 heap=(nil) 
  fl2=0x22, nex=(nil), dsxvers=1, dsxflg=0x0 
  dsx first ext=0x2b7247cfeec8 
  dsx empty ext bytes=0  subheap rc link=0x2b7247d06250,0x2b7247d06250 
  pdb id=0 
 EXTENT 0 addr=0x2b727bd71fd8 
   Chunk     2b727bd71fe8 sz=     8184    perm      "perm           "   
 alo=8184 
 ... 
 PERMANENT CHUNKS: 
   Chunk     2b727bd71fe8 sz=     8184    perm      "perm           "   
 alo=8184 
             2b727bd71fe8 sz=     8184    cpmlst    "qesaQBInit:buff" 
   Chunk     2b727bd74008 sz=     8184    perm      "perm           "   
 alo=8184 
             2b727bd74008 sz=     8184    cpmlst    "qesaQBInit:buff" 
   Chunk     2b727bd57f48 sz=     8184    perm      "perm           "   
 alo=8184 
             2b727bd57f48 sz=     8184    cpmlst    "qesaQBInit:buff" 
   Chunk     2b727bd59f68 sz=     8184    perm      "perm           "   
 alo=8184 

 

Changes

 Upgraded to 12.1.0.1 

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