AIX: Determining Oracle memory usage on AIX [ID 123754.1]

How To Troubleshoot Memory Leaks on UNIX [ID 477522.1]

PURPOSE
-------

To determine the amount of memory being used by Oracle processes
on an AIX system.


SCOPE & APPLICATION
-------------------

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.


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 seeNote:1008866.6 available on Metalink at...

http://metalink.oracle.com

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 seeNote:17094.1 available on Metalink.


------------------------------------------------------------------------
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.
------------------------------------------------------------------------


RELATED DOCUMENTS
-----------------Note:17094.1 TECH: Unix Virtual Memory, Paging & Swapping explainedNote:1008866.6 How to determine SGA Size (7.x, 8.0, 8i)
http://publibn.boulder.ibm.com/doc_link/en_US/a_doc_lib/aixbman/prftungd/2365c71.htm
http://publibn.boulder.ibm.com/doc_link/en_US/a_doc_lib/aixbman/prftungd/2365c75.htmNote:316533.1 AIX: Database performance gets slower the longer the database is running

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9225895/viewspace-1031777/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9225895/viewspace-1031777/