Oracle Database - ORA-4030 Trace Files Analysis (& Ulimits)
How to Debug ORA-4030 and understand if Process Limits are reached
Cause - Out of Private Memory / PGA
What all to see (this particular example is of AIX). Trace files excerpts are below and highlighted at the right points
Killed by OS - Verify the Unix Process ID from trace file and see system logs if process killed by kernel / failed
How to verify what is the maximum limit for PGA
From 11.2.0.4 - There is a section 'Dumping Process Limits' which tells the limits of the process when it was died due to 4030.
This section tells hard limit and soft limit of the PROCESS. Verify this from ulimit -a output and see which limit was effective for the process.
In this particular example it is 1024 MB and the process size is 973MB + overheads, that means I reached the soft limit and hence it should be increased
the soft limit is of Data Segment set in the limits.conf file in AIX/Linux. You can get this changed by help of your AIX administrator.
Oracle recommends to set it to '-1' so that it is set to unlimited value.
if the hard limit is not set, then it defaults to OS defaults - 8388608 TB in this case.
A PL/SQL procedure might be asking for excessive memory, you can reduce that by optimizing it. This (see in the end) SQL will help you, You can query from active_session_history(V$/dba_hist)
Excerpt from Incident Trace File
Verify amount of memory when this happened and usage for the Memory
Cause - Out of Private Memory / PGA
What all to see (this particular example is of AIX). Trace files excerpts are below and highlighted at the right points
Killed by OS - Verify the Unix Process ID from trace file and see system logs if process killed by kernel / failed
How to verify what is the maximum limit for PGA
From 11.2.0.4 - There is a section 'Dumping Process Limits' which tells the limits of the process when it was died due to 4030.
This section tells hard limit and soft limit of the PROCESS. Verify this from ulimit -a output and see which limit was effective for the process.
In this particular example it is 1024 MB and the process size is 973MB + overheads, that means I reached the soft limit and hence it should be increased
the soft limit is of Data Segment set in the limits.conf file in AIX/Linux. You can get this changed by help of your AIX administrator.
Oracle recommends to set it to '-1' so that it is set to unlimited value.
if the hard limit is not set, then it defaults to OS defaults - 8388608 TB in this case.
A PL/SQL procedure might be asking for excessive memory, you can reduce that by optimizing it. This (see in the end) SQL will help you, You can query from active_session_history(V$/dba_hist)
Excerpt from Incident Trace File
Verify amount of memory when this happened and usage for the Memory
Unix Process ID: 5469558 *** SESSION ID:(1135.18099) 2017-02-07 11:31:49.152
ORA-04030: out of process memory when trying to allocate 528408 bytes (kxs-heap-w,kllcqgf:kllsltba)
========= Dump for incident 177801 (ORA 4030) ========
----- Beginning of Customized Incident Dump(s) -----
=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------
*** 2017-02-06 21:20:28.265
32% 311 MB, 19991 chunks: "pmuccst: adt/record " PL/SQL
koh-kghu call ds=110a5e770 dsprt=110004cd8
23% 225 MB, 3615 chunks: "free memory "
pga heap ds=110004cd8 dsprt=0
22% 217 MB, 949 chunks: "kllcqas:kllsltba " SQL
QERHJ hash-joi ds=110ae9090 dsprt=110ad3358
16% 156 MB, 10000 chunks: "free memory " PL/SQL
koh-kghu call ds=110a5e770 dsprt=110004cd8
2% 18 MB, 1013 chunks: "free memory "
top uga heap ds=11011f0a0 dsprt=0
2% 16 MB, 18 chunks: "QERHJ list array " SQL
QERHJ hash-joi ds=110b46320 dsprt=110ad3358
1% 10 MB, 2 chunks: "HT buckets " SQL
QERHJ hash-joi ds=110b46320 dsprt=110ad3358
1% 8705 KB, 72 chunks: "QERHJ Bit vector " SQL
QERHJ hash-joi ds=110ae9090 dsprt=110ad3358
0% 3704 KB, 9 chunks: "kllcqc:kllcqslt " SQL
QERHJ hash-joi ds=110ae9090 dsprt=110ad3358
0% 2580 KB, 5 chunks: "kllcqgf:kllsltba " SQL
kxs-heap-w ds=111767dc8 dsprt=1109b4130
973 MB total: 729 MB commented, 414 KB permanent
243 MB free (225 MB in empty extents),
468 MB, 3 heaps: "koh-kghu call " 75 KB free held
278 MB, 1 heap: "session heap " 18 MB free held
------------------------------------------------------
Summary of subheaps at depth 1
728 MB total:
571 MB commented, 119 KB permanent
157 MB free (156 MB in empty extents),
311 MB, 19991 chunks: "pmuccst: adt/record "
258 MB, 3 heaps: "kxs-heap-w " 24 KB free held
=========================================
Dumping Work Area Table (level=1)
=====================================
Global SGA Info
---------------
global target: 4096 MB
auto target: 1911 MB
max pga: 480 MB
pga limit: 960 MB pga limit known: 1
pga limit errors: 0
pga inuse: 2406 MB
pga alloc: 3245 MB
pga freeable: 146 MB
pga freed: 214795848 MB
pga to free: 0
broker request: 0
----- Process Resource Limits -----
***************** Dumping Resource Limits(s/h) *****************
core file size 1024 MB/8388608 TB
data seg size 1024 MB/8388608 TBfile size 8388608 TB/8388608 TB
max memory size 2048 MB/8388608 TB
open files 98 KB/8388608 TB
stack size 32 MB/4096 MBcpu time 8388608 TB/8388608 TB
virtual memory 8388608 TB/8388608 TB
ulimit -a
core file size (blocks, -c) 1048575
data seg size (kbytes, -d) softfile size (blocks, -f) unlimited
max memory size (kbytes, -m) soft
open files (-n) 2000
pipe size (512 bytes, -p) 64
stack size (kbytes, -s) 66560cpu time (seconds, -t) unlimited
max user processes (-u) 2500
virtual memory (kbytes, -v) unlimited
Another quick tip is to verify what SQL caused 4030, or was it really due to excessive use of PGA.
Use the below query, this is helpful in identifying the top_level_sql_id which is generally the pl/sql call. In general it comes out to be a PL/SQL not nicely written :)
set lines 500 pages 500
select sql_id, top_level_sql_id, sample_time, PGA_ALLOCATED/(1024*1024)
from V$active_session_history
where session_id = 1135
and session_serial#=18099
order by sample_time;
**The below output might be distorted, good idea to copy paste and change the font to Courier new to see exact output***
SQL_ID TOP_LEVEL_SQL SAMPLE_TIME PGA_ALLOCATED/(1024*1024)------------- ------------- --------------------------------------------------------------------------- -------------------------frkqm4ftak0ua 80vfy05czs2ad 07-FEB-17 11.31.25.121 AM 902.31543frkqm4ftak0ua 80vfy05czs2ad 07-FEB-17 11.31.26.123 AM 902.31543frkqm4ftak0ua 80vfy05czs2ad 07-FEB-17 11.31.27.146 AM 902.31543frkqm4ftak0ua 80vfy05czs2ad 07-FEB-17 11.31.28.150 AM 902.31543frkqm4ftak0ua 80vfy05czs2ad 07-FEB-17 11.31.29.154 AM 902.31543frkqm4ftak0ua 80vfy05czs2ad 07-FEB-17 11.31.30.159 AM 902.31543bxu9yrunxqf9b 80vfy05czs2ad 07-FEB-17 11.31.31.163 AM 902.565439y2nswtj645rr 80vfy05czs2ad 07-FEB-17 11.31.32.168 AM 905.252939y2nswtj645rr 80vfy05czs2ad 07-FEB-17 11.31.33.170 AM 906.940439y2nswtj645rr 80vfy05czs2ad 07-FEB-17 11.31.34.173 AM 906.940439y2nswtj645rr 80vfy05czs2ad 07-FEB-17 11.31.35.177 AM 906.940439y2nswtj645rr 80vfy05czs2ad 07-FEB-17 11.31.36.179 AM 903.440439y2nswtj645rr 80vfy05czs2ad 07-FEB-17 11.31.37.184 AM 903.440439y2nswtj645rr 80vfy05czs2ad 07-FEB-17 11.31.38.189 AM 903.440439y2nswtj645rr 80vfy05czs2ad 07-FEB-17 11.31.39.193 AM 903.6904327vjfwrhybqda 80vfy05czs2ad 07-FEB-17 11.31.40.195 AM 903.127933pw83kug4ymvk 80vfy05czs2ad 07-FEB-17 11.31.41.199 AM 905.502933pw83kug4ymvk 80vfy05czs2ad 07-FEB-17 11.31.42.213 AM 911.815433pw83kug4ymvk 80vfy05czs2ad 07-FEB-17 11.31.43.218 AM 919.565433pw83kug4ymvk 80vfy05czs2ad 07-FEB-17 11.31.44.222 AM 927.815433pw83kug4ymvk 80vfy05czs2ad 07-FEB-17 11.31.45.225 AM 936.565433pw83kug4ymvk 80vfy05czs2ad 07-FEB-17 11.31.46.227 AM 945.502933pw83kug4ymvk 80vfy05czs2ad 07-FEB-17 11.31.47.231 AM 955.565433pw83kug4ymvk 80vfy05czs2ad 07-FEB-17 11.31.48.234 AM 965.81543bzj97s57xrv1g 80vfy05czs2ad 07-FEB-17 11.31.49.234 AM 974.87793
