Wednesday, February 27, 2013

Solution to ORA-04030: out of process memory

Today I encountered following error, while trying to run "Purge Logs and Closed System Alerts" to purge data from "FND_LOG_MESSAGES" table.

ORA-04030: out of process memory when trying to allocate 82456 bytes (pga heap,control file i/o buffer)
ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu sessi,pmuccst: adt/record)

The values for the _PGA_MAX_SIZE and PGA_AGGREGATE_TARGET has been set to values even greater than the 4GB, but yet the same errors persist when 4GB is used.

The trace file also confirms that the process is limiting at 4GB.

=======================================
PRIVATE MEMORY SUMMARY FOR THIS PROCESS
---------------------------------------
******************************************************
PRIVATE HEAP SUMMARY DUMP
4074 MB total:                                      
4072 MB commented, 192 KB permanent
1131 KB free (768 KB in empty extents),
4071 MB, 1 heap: "session heap "

These errors usually show up because of running out of map entries from the OS.
There are only 65536 memory map entries per process.

Solution

Change the upper limit at either the OS or at the database level:

  • Change the page count at the OS level:
by root user,
$ more /proc/sys/vm/max_map_count
$ sysctl -w vm.max_map_count=200000 (for example)
OR at database level,
  • Adjust the realfree heap pagesize within the database by setting the following parameters in the init/spfile and restart the database.

_use_realfree_heap=TRUE
_realfree_heap_pagesize_hint = 262144
The default realfree allocator pagesize is 64 KB (65536) , so 64K entries take up 4GB. With 256KB (262144) pages, the limit goes to 16GB.


2 comments:

  1. Forgiving does not erase the bitter past. A healed memory is not a deleted memory. Instead, forgiving what we cannot forget creates a new way to remember. We change the memory of our past into a hope for our future. See the link below for more info.


    #memory
    www.matreyastudios.com


    ReplyDelete
  2. I have a great fun reading your blogs. You are really a great writer. Thank you for making this beautiful and awesome blogs. Hope to read more post from you in the future. Please dont forget to visit me in my site @ www.imarksweb.org. Thank you.


    Bess

    ReplyDelete