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:
IPC status from /dev/kmem as of Mon Sep 10 16:45:16 2001
T ID KEY
MODE OWNER GROUP SEGSZ
m 24064 0x4cb0be18 --rw-r----- oracle
dba 28975104 1836 23847
m 1 0x4e040002 --rw-rw-rw-
root root 31008 572
m 2 0x411ca945 --rw-rw-rw-
root root 8192 572
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.
connect system/manager as sysdba;
SQL> show sga
System Global Area 4830836 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
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
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
r b w
avm free re at
pi po fr de
3 0 0
144020 12778 17 9
0 14 29 0
3 0 0
144020 12737 15 0
1 34 4 0
3 0 0
144020 12360 9 0
1 46 2 0
1 0 0
142084 12360 5 0
3 17 0 0
1 0 0
142084 12360 3 0
8 0 0 0
1 0 0
140900 12360 1 0
10 0 0 0
1 0 0
140900 12360 0 0
9 0 0 0
1 0 0
140900 12204 0 0
3 0 0 0
1 0 0
137654 12204 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.
- 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
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.