Tuesday 22 May 2018

ORA-04030 on a process with large memory requirements

Lately I've met with the following problem. The database in version 11.2.0.4, the application with an evening batch job. At some point the batch job started to fail constantly, reaching around 4500M of allocated PGA. All the efforts to increase PGA_AGGREGATE_TARGET were void, we increased 1st to 5G (from 3), then to 8, but still failed at pretty much the same allocation.
Top allocations look as follows:
TOP 10 MEMORY USES FOR THIS PROCESS
---------------------------------------
70% 3204 MB, 205630 chunks: "pmuccst: adt/record       "  PL/SQL
         koh-kghu call   ds=0x7f2e2ea3c938  dsprt=0xb8c04a0
30% 1356 MB, 87052 chunks: "pl/sql vc2                "  PL/SQL
         koh-kghu call   ds=0x7f2e2ea3c938  dsprt=0xb8c04a0
I've found an article on Metalink (ORA-04030 Error For Memory Allocation Type "pmuccst: adt/record" (Doc ID 1513623.1)), but there was stated that this allocation is common for PL/SQL collections and it is not possible to limit it with PGA_AGGREGATE_TARGET (or _pga_max_size) as this is a different allocation bound with the heap of the process and governed with other settings. Similarly pl/sql vc2 allocation is common for local variables like varrays (ORA-04030 Error For Memory Allocation Type "pl/sql vc2" (Doc ID 1315884.1)).

There are few solutions to the problem above - one is to rewrite the PL/SQL code to replace those large collections with GTT or another is limit collections to fetch only limited number of entries and process cursors in loops.
There is also (as it finally shows up) a possibility to deal with this threshold.
There are the following parameters, which govern this allocation:
  • vm.max_map_count, which is set to 65530 by default - on OS level
  • _use_realfree_heap - hidden db parameter (in 11.2.0.4 defaults to TRUE) - this one enables the whole feature apparently
  • _realfree_heap_pagesize_hint - hidden db parameter (in 11.2.0.4 defaults to 65536) - this one sets page size, so e.g. with increase to 262144 we can increase the memory allocation to around 16G
  • _realfree_heap_pagesize (since 12c)
  • _realfree_heap_max_size - hidden db parameter (in 11.2.0.4 defaults to 32768) - it is described as "minimum max total heap size", in Kbytes - not sure exactly what does that mean and what is controlled by that
The indication, that this is a problem with realfree heap, is present in the process map dump - in this case the dump displayed exactly maximal allowed number of entries.

In general it seems the memory available to the process is calculated as vm.max_map_count * _realfree_heap_pagesize_hint, thus one may affect one of multipliers or both of them, where max_map_count controls the number of allocations while realfree_heap_pagesize the amount of memory within one allocation.