Oracle Memory Management in UNIX Administration Oracle UNIX/Linux Tips by Burleson Consulting

Memory management in UNIX

Most operating systems today possess what is commonly called virtual memory.  In a virtual memory configuration it is possible to extend the existing RAM memory with the use of special swap disk areas.  Memory management in UNIX is critical to the performance of any Oracle database.  As we may know from out Oracle DBA 101 training, the RAM regions of Oracle are designed to improve the speed of data access by several orders of magnitude. 

In other words, RAM is more than 10,000 times faster to access that going to a disk device for the data.  Hence, we are very concerned that our RAM memory always stays within the actual RAM cache, and is not swapped out to the swap dis. Let?s take a close look at how this works.

Virtual memory in UNIX

Virtual memory is an internal ?trick? that relies on the fact that not every executing task is always referencing it?s RAM memory region.  Since all RAM regions are not constantly in-use, UNIX has developed a paging algorithm that move RAM memory pages to the swap disk when it appears that they will not be needed in the immediate future (Figure 2-5)

Figure 5: RAM demand paging in UNIX

As memory regions are created, UNIX will not refuse a new task whose RAM requests exceeds the amount of RAM. Rather, UNIX will page out the least recently referenced RAM memory page to the swap disk to make room for the incoming request. When the physical limit of the RAM is exceeded UNIX can wipe-out RAM regions because they have already been written to the swap disk. 

When the RAM region is been removed to swap, any subsequent references by the originating program require UNIX copy page in the RAM region to make the memory accessible.  UNIX page in operations involve disk I/O and are a source of slow performance. Hence, avoiding UNIX page in operations is an important concern for the Oracle DBA.

The page out operation

UNIX will commonly page out RAM pages in anticipation of additional demands on the RAM memory region.  This asynchronous writing of RAM pages is generally done for all memory regions that are marked as swappable.  For details on making the Oracle SGA non-swappable, please see the special init.ora parameters described later in this chapter.

In sum, a page-out does not cause the RAM memory region to be physically moved out of the RAM, and it is only a preparatory phase.  In case UNIX decides to flush the region from RAM, he will have already copied the RAM contents to the swap disk.

Now let?s look at what happens when a RAM memory page is purged from physical RAM.

The page-in operation

As we noted a page out is no cause for concern because UNIX has not yet decided to actually remove the region from RAM.  However, then UNIX performs a page in, disk I/O is involved, and the requesting tasks will have to wait a long time (milliseconds) while UNIX fetches the region from the swap disk and re-loads it into the RAM region.

Hence, RAM page in operations can be disastrous to the performance of Oracle tasks, and the Oracle DBA must constantly be on the lookout for page in?s and take appropriate action to remedy the problem.

A following section in this chapter on the vmstat utility will show you how to detect page in operations, and we can remedy page in operations in several ways:

1 ? Add additional RAM to the UNIX server.

2 ? Reduce the SGA size for our database by lowering the size of the data block buffers.

3 ? Mark the critical RAM regions (such as the Oracle SGA) as non-swappable

Now that we understand the basics of RAM management in Oracle, let?s take a close look at UNIX commands to manage processes in UNIX.

Jobs of the Oracle DBA and the UNIX Systems Administrator

In many Oracle shops, computer professionals called systems administrators are responsible for the setup, configuration, and tuning of the Oracle database server. The abilities of system administrators vary widely, from excellent support and cooperation to neophyte support and complete non-cooperation.

Because of the tight coupling between database performance and server performance, it is imperative that the Oracle DBA have access to the tools that we describe below. By themselves, the systems administrators will not have enough information about what is happening inside the database to properly tune the server. Conversely, the DBA cannot get the information they need to properly configure Oracle if they cannot get access to the server monitor utilities.

Many Oracle shops give root access to the DBA so they will have full control over their database server. The system administrator continues to be responsible for the configuration and system software on the server, but the DBA accepts responsibility for setting the kernel parameters and managing the interface layer between Oracle and the operating system environment.

A small minority of shops will restrict access to the root account, but these shops will provide the Oracle DBA with access to all of the server monitor tools and system utilities. In either case, it is imperative that the Oracle DBA have access to the system monitor tools.

Process Management Commands in UNIX

This section deals with common UNIX commands to manipulate executing UNIX processes.  Let?s start with the UNIX ps command.

The UNIX ps command

In Chapter 1, we showed how the ps command can be used to display UNIX processes, and we are now ready to look at some more advanced uses of the ps command.

This section is designed to provide a basic overview of management of Oracle processes.  The basic process management command is the ps command.  The ps command is commonly used to display active processes and the characteristics of active processes.

An inspection of the ps -ef command shows the following column values (Table 2-1).

root> ps -ef

UID    PID    PPID   C    STIME    TTY  TIME CMD
oracle 12231     1   0 05:33:06      -  3:15 oracletest (LOCAL=NO)
oracle 24634     1   0 12:57:10      -  4:54 oracletest (LOCAL=NO)

Col	Header	Description
1	UID	The user ID that owns the process
2	PID	The process ID for the task
3	PPID	The parent process.  If the parent is ?1?, the process was created by root.
4	TIME	The current CPU time used by the process.
5	CMD	The UNIX command that is being executed

Table 1: Columns in the ps ?ef command

Now that we see the general format of the ps command let?s look at additional uses of the ps command for Oracle.

Display top CPU consumers with ps

The following command can be used to display the top CPU consumers on any UNIX server.  Note that the sort +6 command displays the CPU column.  This is because the ps columns are counted from left-to-right, with the first column being column zero.

root> ps -ef|sort +6|tail
    root   512     1  0   Aug 10 ?        0:53 /usr/lib/osa/bin/arraymon
    root   460     1  0   Aug 10 ?        1:22 /usr/sbin/cron
    root     1     0  0   Aug 10 ?        2:26 /etc/init -
    root   565     1  0   Aug 10 ?        6:12 /usr/bin/perl /pixlog.pl
    root   471     1  0   Aug 10 ?        9:10 /usr/sbin/nscd
netsaint   765     1  0   Aug 10 ?       27:16 helpdesk/netsaint.cfg
    root   742   572  0   Aug 10 ?       32:04 opt/VRTSvmsa
    root   454     1  0   Aug 10 ?       44:09 /usr/sbin/syslogd
    root     3     0  0   Aug 10 ?       363:12 fsflush

Here we see the tasks in order of their total CPU consumption, with the largest value at the end of the list. In cases where a process has been running for more than one day, the display format changes.  In these cases, the time format changes and column 5 is the CPU column.

root> ps -ef|sort +5|tail

    root  5440  2094   0   Nov 21      -  0:47 /usr/sbin/syslogd
    root  9244     1   0   Nov 21      -  3:26 ./pdimapsvr.ip -D0
    root 10782     1   0   Nov 21      -  4:41 ./pdiconsvr.ip -D0       
    root  5990  2094   0   Nov 21      -  5:33 /usr/sbin/snmpd
    root  4312     1   0   Nov 21      -  7:14 /usr/sbin/cron
    root  4448  2094   0   Nov 21      -  9:25 /usr/sbin/rwhod
    root     1     0   0   Nov 21      - 198:59 /etc/init
    root  2450     1   0   Nov 21      - 438:30 /usr/sbin/syncd 60

In the real world, the Oracle DBA will issue this command twice, once with column 5 and again with column 6.

Using ps auxgw command in HP/UX and AIX

Another approach to finding top CPU consumers on your UNIX server is to use the ps auxgw command.  The third column of this listing (%CPU) shows the percentage of CPU used.  Hence, the following command will display the top CPU users:

Col	Header	Description
1	UID	The user ID that owns the process
2	PID	The process ID for the task
3	PPID	The parent process.  If the parent is ?1?, the process was created by root.
4	TIME	The current CPU time used by the process.
5	CMD	The UNIX command that is being executed

Using egrep to identify top CPU consumers

Yet another approach uses the extended grep command egrep command to display the top CPU consumers.  I learned this command from an older UNIX guru who is no longer with us, and I must confess that I do not understand the syntax. In the output below we see a root process called kproc using 88.3% of the CPU:

root> ps augxww|egrep "RSS| "|head

USER       PID %CPU %MEM   SZ  RSS  TTY STAT STIME  TIME COMMAND
root       516 88.3  0.0   16    4   - A      Nov 21 194932:05 kproc
oracle   41616  4.4  1.0 8312 6052   - A      07:00:59  4:57
oracle   20740  2.7  1.0 8140 5888   - A      08:52:32  0:02
oracle   17402  2.4  1.0 8296 6044   - A      07:27:04  2:06
oracle   25754  2.4  1.0 8640 6388   - A      08:10:03  1:03
oracle   13168  1.6  1.0 8196 5760   - A      05:33:06  3:15
oracle   20666  1.0  1.0 8304 6052   - A      08:15:19  0:22
oracle   14922  0.6  1.0 8300 5720   - A      01:01:46  2:57
oracle   44518  0.6  1.0 8080 5828   - A      08:47:47  0:02

Show number of active Oracle dedicated connection users

Another common command is to use ps to count the number of dedicated UNIX connections that have been spawned by the Oracle listener.  If you are not using the multi-threaded server, this command will give you an accurate count of the number of Oracle connections on the UNIX server.

root> ps -ef|grep $ORACLE_SID|grep -v grep|grep -v ora_|wc ?l

 

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.