ORA-04030: out of process memory when trying to allocate 1432 bytes

Posted: June 23, 2015 in ORA Errors
Tags:

Over the last couple of months, I have been receiving ORA-04030 in my alert logs in my Exadata Development Database.

Last Friday, the out of memory error caused a critical ETL job to fail in development. Out of memory on Exadata? How can that be?

The trace file for the incident revealed the following…

Dump continued from file: /u01/app/oracle/diag/rdbms/******/******/trace/******_ora_102373.trc
ORA-04030: out of process memory when trying to allocate 1432 bytes (kxs-heap-w,kxfqlobf)

========= Dump for incident 141131 (ORA 4030) ========
—– Beginning of Customized Incident Dump(s) —–
=======================================
TOP 10 MEMORY USES FOR THIS PROCESS
—————————————

*** 2015-06-12 03:36:56.231
37% 1518 MB, 1111247 chunks: “kxfqlobf ” SQL
kxs-heap-w ds=0x7f111568b388 dsprt=0x7f11159361a0
33% 1334 MB, 851035 chunks: “permanent memory ” SQL
kxs-heap-w ds=0x7f111568b388 dsprt=0x7f11159361a

Top 10 processes:
————————-
(percentage is of 7696 MB total allocated memory)
53% pid 229: 4091 MB used of 4097 MB allocated <= CURRENT PROC
9% pid 100: 608 MB used of 716 MB allocated
3% pid 69: 47 MB used of 223 MB allocated (174 MB freeable)
3% pid 66: 48 MB used of 220 MB allocated (171 MB freeable)

Note 1325100.1 In My Oracle Support provided some insight on why the out of process memory occurred. This is just a summary of the pieces I used so please read the entire note and test it in your non prod environment prior to making this change.

  1. I am running 11.2.0.4 so the first suggestion to upgrade to 11.2.0.1 or higher was not necessary.
  2. Change the upper limit at either the OS or at the database level:
    1. Change the page count at the OS level:
      [root@*********]# more /proc/sys/vm/max_map_count
      [root@*********]# sysctl -w vm.max_map_count=262144 (for example)

       

    2. Adjust the realfree heap pagesize within the database by setting the following parameters in the init/spfile and restart the database. These are hidden parameters so use with caution.
      SQL> alter system set “_use_realfree_heap”=TRUE scope=spfile sid=’*’;
      SQL> alter system set “_realfree_heap_pagesize_hint”=262144 scope=spfile sid=’*’;
    3. I chose to make the change at the OS Level. Unfortunately, the changes were not seen in the database immediately. So I chose to change it at the database level as well and restart the database.
    4. That worked!! I haven’t seen an ORA-04030 error since. After a few days, I made the change in production as well.

Good Luck!!!

References Used:

  1. http://stepintooracledba.blogspot.com/2013/12/modify-hidden-parameter-in-oracle.html 
  2. https://support.oracle.com/ 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s