Category Archives: Oracle DBA

Max PGA Research: Check that a process can allocate a large volume of memory

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');

Advertisements

Max PGA Research: Introduction

I am starting a set of blog posts focused on a Oracle database PGA related questions. In particular I would like to understand what is the maximum size of memory a single Oracle process could possibly allocate for sorting, hashing and other memory intensive operations. Today it is not rare to see Oracle systems running on servers with a lot of memory. Many Oracle systems have 64, 128, 512 GB or even several Terabytes of RAM today. In some cases it is beneficial of a single process could leverage a lot of memory. The question what I would like to find an answer to is: How much memory a single Oracle foreground/user’s process could possibly allocate?

I would like to make my research as practical as possible. Therefore I will use set of practical experiments that I hope will help us to make a fact base conclusions. As of now I think about the following steps:

  • Check that a process can allocate a large volume of memory.
    • Create a simple code that uses a much memory as we want, e.g. 10G.
    • Before we go into different advanced (non default) OS and Oracle parameters tuning we should make sure that a process could allocate memory using as simple test as possible.
    • If this test works, we switch to the next phase. If it doesn’t then we need to spent some time on the OS configuration.
  • Create a good test code that would allow to test the memory allocation for an Oracle session/process
    • In particular I am interested in memory allocated for sorting/hashing operations
  • Make an overview of the diagnostic tools that are available to diagnose memory allocation of an Oracle database process
    • As an example I would mention V$SQL_WORKAREA, V$SQL_PLAN, V$SESSTAT views
    • A good test case that we will create at the previous step will help us to see how to read the output from the different memory diagnostic tools and conclude if the output makes sense
  • Work through the configuration settings that have an impact on the maximum size of memory a single Oracle process could allocate.
    • As an example of such parameters I would mention: pga_aggregate_target, _pga_max_size, _smm_max_size, _realfree_heap_pagesize_hint, _use_realfree_heap and others

A few additional points I would like to mention before I finish this post:

  • I will focus on 11.2.0.4 Oracle database version running on the 64 bit Linux. In particular my sandbox runs 6.5 of Oracle Enterprise Linux.
  • I am not an expert in this field. I know the basics but can miss important stuff easily. Please feel free to correct me if I got it wrong. The only reason I have started to publish this set of blogs it to learn. Please feel free to learn together with me and contribute to the research.
  • There are many more knowledgeable people on the subject in the Oracle community and they helped me already giving some hints via Social Media. Just to mention a few:
  • I hope that all together we will find the right answers to the PGA related questions and those posts will be useful for me, you and others 🙂

Yury

Max PGA Research blog posts: