Oracle UNIX Administration and RAM Usage Oracle UNIX/Linux Tips by Burleson Consulting

Oracle and RAM usage

The interaction between Oracle and the RAM memory regions is largely transparent to the DBA.  At startup time, Oracle issues the UNIX malloc() command to allocate the RAM memory region for the System Global Area (SGA).  Also, Oracle will allocate individual Program Global Areas (PGA) for all dedicated connections to the UNIX Oracle server.

We can easily see the held memory segments in UNIX by issuing the UNIX ipcs command:

root> ipcs -pmb
IPC status from /dev/kmem as of Mon Sep 10 16:45:16 2001
T      ID     KEY        MODE     OWNER  GROUP  SEGSZ  CPID  LPID
Shared Memory:
m   24064 0x4cb0be18 --rw-r----- oracle    dba 28975104  1836 23847
m       1 0x4e040002 --rw-rw-rw-   root   root    31008   572   572
m       2 0x411ca945 --rw-rw-rw-   root   root     8192   572   584
m    4611 0x0c6629c9 --rw-r-----   root   root  7216716  1346 23981
m       4 0x06347849 --rw-rw-rw-   root   root    77384  1346  1361

At the Oracle level, we can also see the details about the SGA component sizes by viewing the Oracle alert log or by issuing the show sga command.

SQL> connect system/manager as sysdba;
SQL> show sga

Total System Global Area       4830836 bytes
Fixed Size                       46596 bytes
Variable Size                  3948656 bytes
Database Buffers                819200 bytes
Redo Buffers                     16384 bytes

We reviewed in Chapter 2 how we can measure overall RAM memory behavior for Oracle in UNIX, but we still have additional information that can be gathered.

On UNIX, the background processes attach to shared memory, one of the standard inter-process communication methods on UNIX. On NT, this is not necessary, as the Oracle threads all share the same virtual address space anyway.

One of the major concerns about RAM memory management for Oracle is the amount of RAM paging.  We deliberately over-simplified demand paging in Chapter 2 for illustration purposes and we are now ready to take a closer look at how RAM paging works in UNIX.

Understanding UNIX RAM memory paging

As we have noted in chapter 2, most Oracle DBA?s rely on the  pi column in vmstat to signal when the server is swapping RAM memory.  However, there is more to the story.

There are times when the pi column will be non-zero, even though there is no real RAM swapping.  To illustrate this let?s take a simple example. Suppose that we invoke a 20 Megabyte Oracle executable program, such as a Pro*C program. We don't need to load all 20 meg of the executable into RAM all at once.  Rather, we just want to load those pieces of the executable code that require immediate execution. Hence, UNIX will memory frames as necessary later and rely on the principle of spatial locality to minimize the amount of pages in our RAM working set.

To manage the memory segments, the UNIX kernel builds a memory map of the entire program when it starts. Included in this map is a note on whether the storage is ?in memory? or ?on swap disk?.  

As the program starts it begins accessing some of its pages that have never been loaded into RAM memory.   Hence, you may see vmstat page-in?s when a large number of programs are starting and allocating their RAM memory.

During normal operation we may see various points in time when paging in happens a lot and this is not always a cause for concern.  Remember, a UNIX process may page-in when the UNIX program is starting or is accessing parts of it?s code that it had not used before.

Paging out (the po column in vmstat) happens frequently as UNIX prepares for the possibility of a page-in. With UNIX virtual memory we are always anticipating running out of RAM memory, and a page-out is a method for being ready for a subsequent page-in. Also, as UNIX processes end they call the free() system call to free the RAM pages so they can be used by new processes.

Internals of RAM memory paging

So if RAM paging in ?pi? may be  acceptable and paging out ?po? may be  acceptable, how do we tell when the RAM on a server is overstressed and swapping? One answer is to correlate the UNIX scan rate with page-in operations.  When an Oracle server begins to run low on RAM memory, the page stealing daemon process awakens and UNIX begins to treat the RAM memory as a sharable resource, moving memory frames to the swap disk with paging operations.

The page stealing daemon operates in two modes.  When RAM memory shortages are not critical, the daemon will steal small chunks of least-recently-used RAM memory from a program.  As RAM resource demands continue to increase, the page stealing daemon escalates and begins to page-out entire programs RAM regions. In short, we cannot always tell if the page-in operations that we see are normal housekeeping or a serious memory shortage unless we correlate the activity of the page stealing daemon with the page-in output.

To aid in this, the vmstat utility gives the sr column to designate the memory page scan rate. If we see the scan rate rising steadily, we will have hit the page stealing daemons first threshold, indicating that entire programs RAM memory regions are being paged out to the swap disk.  Next, we will begin to see high page-in numbers as the entire process in paged back into RAM memory (Figure 6-2).

Figure 2: Interaction between scan rate, page-out and page-in

Carefully review the list below from HP/UX vmstat.  The scan rate is the furthest right column, and here we see the value of sr rising steadily as the page stealing daemon prepares for a page in.  As the sr value peaks, we see the page-in operation (pi) as the real RAM memory on the Oracle server is exceeded.

root> vmstat 2

         procs           memory                   page   
    r     b     w      avm    free   re   at    pi   po    fr   de    sr   
    3     0     0   144020   12778   17    9     0   14    29    0     3 
    3     0     0   144020   12737   15    0     1   34     4    0     8 
    3     0     0   144020   12360    9    0     1   46     2    0    13 
    1     0     0   142084   12360    5    0     3   17     0    0    21 
    1     0     0   142084   12360    3    0     8    0     0    0     8 
    1     0     0   140900   12360    1    0    10    0     0    0     0 
    1     0     0   140900   12360    0    0     9    0     0    0     0 
    1     0     0   140900   12204    0    0     3    0     0    0     0 
    1     0     0   137654   12204    0    0     0    0     0    0     0 

      As we have already noted, we can also use the glance utility to see details about memory consumption (Figure 6-3).

Figure 3: The glance screen for memory

In sum, that Oracle DBA must always be vigilant in their monitoring for RAM memory paging.  Next, let?s look at the techniques for pinning the Oracle RAM memory inside the UNIX server.

SGA memory pinning

In HP/UX and Solaris it is possible to ?pin? the SGA so that it will never experience a page-in.  This method is also known as memory fencing or memory pinning, depending on the UNIX vendor. Essentially, memory pinning marks the Oracle SGA as being non-swappable, and the memory region always resides at the most-recently used area of the RAM heap.  Only that memory above and beyond the Oracle SGA is eligible for paging.  On a dedicated Oracle UNIX server, this technique essentially prioritizes the Oracle SGA, telling UNIX to page-in only the RAM memory associated with individual connections to Oracle (PGA memory), and not the Oracle SGA region (Figure 6-4).

Figure 4: Memory fencing for Oracle

Please note that not all dialects of UNIX support RAM fencing and you cannot do RAM memory fencing on IBM-AIX, Linux and other dialects of UNIX. 

In Solaris and HP/UX, the pinning is done by setting the following init.ora parameters.

lock_sga=true  - for hp/ux
USE_ISM=true ? Sun Solaris ?Intimate Shared Memory?

(Note: In Oracle 8.1.5 and beyond, USE_ISM is a hidden parameter and defaults to true)

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.