A couple of months ago we hit the *ORA-04036*: PGA memory used by the
instance exceeds PGA_AGGREGATE_LIMIT during
a parallel INSERT/SELECT with DOP 4
The value of the PGA_AGGREGATE_LIMIT was initially set to 8GB
Thanks to the *_pga_limit_dump_summary* set to TRUE a dump trace file
has been generated where we can clearly see that each parallel slave
of the parallel server set n°2
consumes 2GB, reaching as such the 8GB limit.
So, the DBA thought that the hard pga limit a parallel (or serial)
process can’t go beyond without spilling to disk (TEMP) is 2G. He then
increased the value of the
PGA_AGGREGATE_LIMIT to 16GB
But the same parallel insert/select with DOP=4 failed again with the
same ORA-04036: PGA memory used by the instance exceeds
PGA_AGGREGATE_LIMIT
of 16GB
This occurred, this time, because each of the 4 parallel slaves of
server set 2 consumed up to 4GB of PGA memory without spilling to TEMP
disk
So I tried to understand what is the pga hard limit a parallel (or
serial) process can’t go beyond without spilling to TEMP
I ended up by finding the following formula:
max pga memory = 64k*max_map_count
max_map_count value comes from /proc/sys/vm/max_map_count
in my case max_map_count = 262144
max pga memory = 64k*262144 = 64*262144/power(1024,3) = 16GB
The max_map_count normally should be equal to the realfree_heap_pagesize
(65536)
SQL> _at_pd realfree
Show all parameters and session values from x$ksppi/x$ksppcv...
NAME VALUE DESCRIPTION
---------------------------- -------
---------------------------------------------
_realfree_heap_max_size 32768 minimum max total heap size, in Kbytes
_realfree_heap_pagesize 65536 hint for real-free page size in bytes
_realfree_pq_heap_pagesize 65536 hint for pq real-free page size in bytes
_realfree_heap_mode 0 mode flags for real-free heap
_use_realfree_heap TRUE use real-free based allocator
for PGA memory
In which case the max pga memory (per process) would have been equal
to 4GB according to the above formula
So my questions are:
- *is the above formula reliable to get the maximum of pga
memory per process (serial or parallel)?*
- *What this max_map_count parameter stands for? Should it
really be equal to realfree_heap_pagesize?*
