Investigating Oracle Database Memory Pressure Behavior

Investigating Oracle Database Memory Pressure Behavior

Oracle database will grab all and release as the O/S sees memory pressure.

I came across the above statement recently during a discussion., which led to this blog post. I wasn’t very sure if it was true so I did not reply to it. I’m aware of the behavior when it face CPU pressure but not memory pressure. So, this has definitely piqued my curiosity to find out more.


In Oracle 11gR2 and later, we can choose to use have the following options:

  • Automatic Memory Management (AMM)
    • We configure MEMORY_TARGET and MEMORY_MAX _TARGET and let Oracle database manage the SGA and PGA for us
  • Automatic Shared Memory Management (ASMM)
    • We configure SGA_TARGET and SGA_MAX_SIZE and let Oracle manage SGA for us.
  • Automatic PGA Memory (Usually used together with ASMM)
    • We configure PGA_AGGREGATE_TARGET and let Oracle manage PGA for us.

So, we can be sure that the parameters below affect Oracle database memory usage on a system. (Of course, we are not referring to manual memory tuning.)

  1. MEMORY_MAX _TARGET
  2. MEMORY_TARGET
  3. SGA_TARGET
  4. SGA_MAX_SIZE
  5. PGA_AGGREGATE_TARGET

It is widely understood that the above parameters are merely a a target. It may exceed the optimum size due to other circumstances. For example, the number of users increases beyond limit or insane SQL queries that exceed PGA value. PGA size is the unknown factor here, since we do not know how many users or what kind of queries will be running in the database.

The question here is, when it uses more memory than the target value, does it start to swap or does it grab the free memory from the non-allocated memory from the O/S. I would think that it will use free memory then swap space since it will ask the memory from O/S until it has memory pressure which then, it will start to swap.

Let’s test it out!

Let’s login and check all the baseline numbers.

[oracle@primary ~]$ sqlplus / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter pga_aggregate_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 200M

#free -m

total used free shared buffers cached
Mem: 2002 721 1281 183 24 451
-/+ buffers/cache: 245 1757
Swap: 4031 0 4031


select a.name, to_char(b.value, '999,999,999') bytes,
to_char(round(b.value/1024/1024,1), '99,999.9' ) mbytes
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name like '%ga memory%';

NAME BYTES MBYTES
-------- ------------ ---------
session uga memory 2,515,088 2.4
session uga memory max 2,515,088 2.4
session pga memory 3,555,880 3.4
session pga memory max 22,889,000 21.8

The PGA_AGGREGATE_TARGET is configure to use 200MB. Currently it is only using 2.4MB. We have 4GB of swap space configured as well.

Let’s create a package and generate the workload

SQL> create or replace package demo_pkg
 as
 type array is table of char(2000) index by binary_integer;
 g_data array;
 end;
 /

SQL> begin
 for i in 1 .. 2000000
 loop
 demo_pkg.g_data(i) := 'x';
 end loop;
 end;
 /
 ERROR at line 1:
 ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu sessi,pl/sql vc2)

The above SQL script definitely uses more than 2GB of memory for sorting, which lead to ORA-04030 error.

From another separate SSH session, let’s monitor the memory usage via vmstat

[root@primary ~]# vmstat 1 6000000
 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r b swpd free buff cache si so bi bo in cs us sy id wa st
 0 0 0 1311424 24952 462624 0 0 1611 58 303 486 6 10 83 1 0
 0 0 0 1311392 24952 462628 0 0 0 16 151 282 0 1 99 0 0
 0 0 0 1311384 24952 462628 0 0 0 0 118 228 0 0 100 0 0
 0 0 0 1311384 24952 462628 0 0 0 32 151 287 0 0 100 0 0
 0 0 0 1311384 24952 462628 0 0 0 0 112 220 0 0 100 0 0
 0 0 0 1311384 24952 462628 0 0 0 0 141 263 0 1 99 0 0
 0 0 0 1311384 24960 462628 0 0 0 52 135 267 0 0 99 1 0
 0 0 0 1311384 24960 462628 0 0 0 0 141 271 0 0 100 0 0
 0 0 0 1311384 24960 462628 0 0 0 0 109 215 0 0 100 0 0
 0 0 0 1311384 24960 462628 0 0 0 32 150 283 0 0 100 0 0
 0 0 0 1311384 24960 462628 0 0 0 0 123 232 0 0 100 0 0
 0 0 0 1311384 24968 462624 0 0 0 20 144 276 0 1 99 0 0
 0 0 0 1311392 24968 462628 0 0 0 32 121 250 0 0 100 0 0
 0 0 0 1311136 24968 462628 0 0 0 0 167 329 1 1 98 0 0
 0 0 0 1311136 24968 462628 0 0 0 0 109 211 0 0 100 0 0
 1 0 0 226260 24968 463068 0 0 280 32 998 310 22 67 10 0 0
 2 4 86096 13744 60 105252 0 93124 12636 93144 2101 1698 13 86 0 1 0
 3 2 314548 14296 60 82612 1596 226304 29708 226304 2234 1695 9 91 0 0 0
 3 4 552488 13732 60 83128 9568 244640 35048 244664 2549 2140 8 90 0 2 0
 2 0 806904 13888 60 82228 1920 267312 34484 267344 2438 1904 9 89 0 2 0

The highlighted values above are the period where the SQL query executed. Noticed that the free memory was at 1.3GB until it drop to 200MB before it actually start to swap.

Summary

From the above, I have highlighted the important points:

  1. It is not possible to “overuse” SGA as it uses LRU algorithm to remove unnecessary objects under memory pressure.
  2. Although we configure PGA to only use a maximum of 200MB, it is possible to exceed this value due to user queries or sessions.
  3. Under memory pressure, the database will try to use available memory until it doesn’t have a choice, which it will choose to use swap space.
  4. When space space run out, the database will hit ORA-04030 error.

Regards,
Wei Shan

References: Expert Oracle Database Architecture -Thomas Kyte

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s