As mentioned in the Max PGA Research: Introduction post I would like to understand how much memory a single Oracle foreground (user) process can allocate for sorting, hashing or other memory intensive operations.
The first step that I am suggesting is using a simple PL/SQL code check that your OS/DB are configured to allocate a large volumes of memory.
For that purpose I have created a simple PL/SQL code that could “eat” as much memory as we want. The code consist of three parts. The first one is a PL/SQL package that contains the mem_tab structure that allows to store a character strings array. Note that I use a PL/SQL package to make the memory allocation persistence across SQL calls. Alternatively you can use anonymous PL/SQL block. However in that case the memory will be deallocated as the call is finished:
create or replace package pgares_mem_sort
is
type t_mem is record(txt varchar2(4000));
type t_mem_tab is table of t_mem index by binary_integer;
mem_tab t_mem_tab;
end;
/
The second part is a code that based on an input parameter value fills package’s array with values:
declare
c_count number := 1024*&1;
BEGIN
— Memory consumption part
FOR f IN 1..c_count LOOP
pgares_mem_sort.mem_tab(f).txt := substr(lpad(f,1000,’-‘),0,1024);
END LOOP;
END;
/
The third part are the few lines of code that gathers a few important statistics. The SQL script that consumes as much memory as we want is available here.
The following is the execution example. The intention here is to let the session to consume 10GB+ of RAM.
SQL> @Desktop/SQL/mem_killer_c.sql 10240
declare
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu
sessi,pl/sql vc2)
Elapsed: 00:01:15.65
SQL>
In my default Linux sandbox environment the code has failed. It is useless to hope that an Oracle process will be able to allocate 10G of memory if this simple PL/SQL code fails. We need to put some efforts to configure Linux to allow the larger memory allocation.
The following is the pmap Linux utility’s output executed against the Oracle foreground process. It appears there is a 4GB memory limit by default (thanks to Kevin Closson and Freek D’Hooge for the hints).
root@host:~ # pmap -x 23831 | grep "rw--- zero" | awk '{print $3}' | sort | uniq -c | sort -k1n
1 56
1 60
1 88
3 0
65419 64
root@host:~ # bc <<< 65419*64/1024
4088
root@host:~ #
A single Linux process can allocate 65419 chunks of 64k memory regions by default. This limits total memory that a process can allocated to ~4GB.
It appears that it is relatively simple to increase the limit. You just adjust the vm.max_map_count linux kernel parameter and set _realfree_heap_pagesize_hint init.ora hidden parameter:
root@host:~ # cat /proc/sys/vm/max_map_count
65530
root@host:~ # echo vm.max_map_count = 262144 >> /etc/sysctl.conf
root@host:~ # sysctl -p
...
vm.max_map_count = 262144
root@host:~ # cat /proc/sys/vm/max_map_count
262144
SQL> alter system set “_realfree_heap_pagesize_hint”=262144 scope=spfile;
System altered.
After an instance restart repeat the simple memory allocation test.
SQL> @Desktop/SQL/mem_killer_c.sql 10240
var size = 10240Mb
UGA 0 4095 4095 207
PGA 2 4095 4093 207
PL/SQL procedure successfully completed.
Elapsed: 00:03:27.64
SQL>
The following is pmap utility’s output
root@host:~ # pmap -x 37590 | grep "rw--- zero" | awk '{print $2}' | sort | uniq -c
48510 256
1 49664
root@host:~ # bc <<< 48510*256/1024/1024
11
As you noticed the process has successfully allocated 10+ GB of memory.
Before is finish this blog post I would like to make the following comments:
- Now we have a simple test to execute within an Oracle Instance to check how much memory an Oracle process could possibly allocate. If the test fails we should focus on tuning you OS before taking the next steps (see Max PGA Research: Introduction post).
- It looks like as alternative to setting _realfree_heap_pagesize_hint parameter we can set _use_realfree_heap=TRUE parameters. In such case an Oracle process will use 64k regions but would be able to use more than 64k of those. The vm.max_map_count kernel parameter would need to be set anyway.
- The other misery that is not clear for me as of now is the fact that Oracle reports that there is just 4095MB of PGA allocated by the process. However the pmap utilities output clearly indicates that 10GB of memory has been allocated. My script uses the following SQL to retrieve the PGA size for the session
select value INTO V_PGAE from v$mystat where statistic#=(select STATISTIC# from v$statname where name = 'session pga memory max');
Yury's Blog 