AIX: Determining Oracle Memory Usage On AIX (Doc ID 123754.1)

Applies to: 

Oracle Database - Enterprise Edition - Version 8.1.7.0 and later
IBM AIX on POWER Systems (64-bit)
IBM AIX on POWER Systems (32-bit)
***Checked for relevance on 02-Apr-2013***

Purpose

To better understand how memory is used with Oracle Databases on AIX.

Scope

This article is intended for AIX system administrators and DBAs working on AIX systems, but can be used by anyone who would like a better understanding of the way Oracle uses memory on an AIX system.

Details

UNDERSTANDING ORACLE PROGRAM TYPES
----------------------------------

The first program type you should be aware of are the Oracle background processes.
These processes are created when you start a database instance. Common examples of Oracle background processes are log writer (lgwr), database writer (dbw0), system monitor (smon), process monitor (pmon), recovery (reco), and check point (ckpt), but may include others. These processes run with the name ora_ProcessName_SID, where ProcessName is the name of the background process and SID is the value of ORACLE_SID.
For example, the process monitor background process for a database instance named DEV would be "ora_pmon_DEV". The second program type you should be aware of are the Oracle user processes. These processes are created when you start a program which will work with an Oracle database.
Common examples of Oracle user processes are sqlplus, imp, exp, and sqlldr, but may include many others. These user processes are frequently referred to as client processes and are usually named the same as the command you used to start the program.
For example, the sqlplus user process would be named "sqlplus". The third program type you should be aware of are the Oracle server processes.
Server processes work directly with the database instance to carry out the requests from the user processes. Server processes are often referred to as shadow processes, and this is the term that will be used in this article. Shadow processes may be dedicated to a single user process or part of a multi-threaded server (MTS) configuration. The shadow processes are named oracleSID, where SID is the value of ORACLE_SID. For example, any shadow process connected to the database instance "DEV" would be named "oracleDEV".

UNDERSTANDING ORACLE MEMORY USAGE
---------------------------------

Oracle memory usage can be broken down into 2 basic types, private and shared.
Private memory is used only by a single process. In contrast, shared memory is used by more than 1 process and this is where most confusion over memory usage happens. When determining how much memory Oracle is using, the shared memory segments should only be counted once for all processes sharing a given memory segment. The largest segment of shared memory with Oracle is usually the Shared Global Area (SGA). The SGA is mapped into the virtual address space for all background and shadow processes. Many programs which display memory usage, like "top" or "ps -lf" do not distinguish between shared and private memory and show the SGA usage in each background and shadow process. Subsequently, it may appear as though Oracle is using several times more memory than what is actually installed on the system.

To properly determine how much memory Oracle is using, you must use a tool which separates private and shared memory.
One such tool is "svmon". This program can be located on the IBM AIX CD-ROM as part of the AIX fileset "perfagent.tools". Use the command "smit install_latest" to install this fileset.
For information about using svmon and other tools to determine memory usage, please refer to the AIX "Performance Management Guide" from IBM,
chapter 7 "Monitoring and Tuning Memory Use", and the heading "Determining How Much Memory Is Being Used".
This guide is available online at the following IBM web site... http://publibn.boulder.ibm.com/doc_link/en_US/a_doc_lib/aixbman/prftungd/2365c71.htm
This article will not discuss the use of svmon, except to address a common misunderstanding with its output. The svmon command will associate memory used for buffering persistent file pages (also know as the Unix filesystem buffer cache) with the process that requested the file page. However, the physical memory used to buffer the persistent file pages is not allocated or controlled by Oracle. This type of memory is allocated and controlled exclusively by the AIX operating system. The memory used for this purpose should not be considered when determining how much memory Oracle is using since it is actually AIX that is allocating and controlling the persistent file pages buffer. That does not mean that the memory for buffering persistent file pages should be ignored. It is possible that this type of memory could account for the majority of physical memory used on the system and could lead to unnecessary paging.
This kind of memory can be identified in the output of commands like "svmon -Pau 10" by "pers" in the "Type" field and a disk device in the "Description" field. The AIX vmtune command can be used to modify the amount of physical memory used to buffer persistent file pages. In particular, the minperm, maxperm, and strict_maxperm parameters of vmtune.

For information about using vmtune, please refer to the AIX "Performance Management Guide" from IBM,
chapter 7 "Monitoring and Tuning Memory Use", and the heading "Tuning VMM Page Replacement with the vmtune Command".
This guide is available online at the following IBM web site... http://publibn.boulder.ibm.com/doc_link/en_US/a_doc_lib/aixbman/prftungd/2365c75.htm

There is another tool that can be used, and it is available by default on every AIX system. The command is "ps v" followed by the process id number of the process you are checking memory usage of.

Please note that there is no dash ( - ) before the "v".

Here is a comparison of output from the "ps -lf" and "ps v" command...

# ps -lfp 13288
F S UID PID PPID C PRI NI ADDR SZ WCHAN STIME TTY TIME CMD
240001 A oracle 13288 1 0 60 20 1ba2f 34032 Nov 03 - 0:06 ora_pmon_DEV

# ps v 13288
PID TTY STAT TIME PGIN SIZE RSS LIM TSIZ TRS %CPU %MEM COMMAND
13288 - A 0:08 225 5616 13904 32768 28420 13512 0.0 1.0 ora_pmon_DEV
The "ps v" fields that are of interest to us are RSS and TRS. The RSS number is equal to the sum of the number of working-segment pages in memory times 4 and the code-segment pages in memory times 4. The TRS number is equal to just the code-segment pages in memory times 4.

Please note that an AIX memory page is 4096 bytes, which is why the number of memory pages must be multiplied by 4 to get the value for RSS and TRS (which are each reported in kilobytes).
For example, if the actual memory used by the code-segment was 2 pages ( 2 pages * 4096 byte page size = 8192 bytes), then the TRS value would be reported as 8 ( 2 {number of pages} * 4 = 8 {kilobytes} ). Since RSS includes both working-segment and code-segment pages, if we subtract TRS, which is just the code-segment pages, from RSS, we are left with only the working-segment pages, or private memory. In the example above, the oracle pmon background process is using... 13904 (RSS) - 13512 (TRS) = 392 392 * 1024 = 401408 bytes The correct amount of memory used by the pmon background process is 392k (401408 bytes), not 34 MB as reported by the "ps -lf" command. The value of TRS will be approximately the same for all oracle background processes, since all of these processes are just different invocations of the same $ORACLE_HOME/bin/oracle executable.
To get a good estimate of memory usage for all oracle background processes, sum the private memory for each background process plus the value of TRS for only one of the background processes, plus the size of the SGA.

For information about determining the size of the SGA, please see...Note 1008866.6 How to determine SGA Size (7.x, 8.x, 9.x, 10g)

Determining memory usage for shadow processes is a little more complicated since the amount of memory used can fluctuate greatly from one moment to the next depending on what the user is doing. You should subtract TRS from RSS to get the private memory used by the shadow process, but remember this is only a snapshot and the value will change if the process is active. To get a good estimate of memory used for shadow processes, you should run the "ps v" command repeatedly at regular intervals while the process is under peak load to get an average value. You can now take this value and multiply it by the peak number of expected users to estimate how much memory will be needed on the system.

For more (generic) information about memory usage, please see...Note 17094.1 TECH: Unix Virtual Memory, Paging & Swapping explained

-------------------------------------------------------------------------------------------------------------------------------------

Accessibility of Links to External Web Sites This documentation may contain links to Web sites of other companies or organizations that Oracle Corporation does not own or control. Oracle Corporation neither evaluates nor makes any representations regarding the accessibility of these Web sites.

-------------------------------------------------------------------------------------------------------------------------------------

References

NOTE:1008866.6 - How to determine SGA Size (7.x, 8.x, 9.x, 10g)
NOTE:17094.1 - TECH:  Unix Virtual Memory, Paging & Swapping explained

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-1064306/，如需轉載，請註明出處，否則將追究法律責任。