DBA Tips Archive for Oracle

  


Oracle Memory Usage in Sun Solaris

by Jeff Hunter, Sr. Database Administrator


Reference: Oracle MetaLink Note: 153655.1

Contents

  1. Overview
  2. Introduction to Oracle Processes
  3. Oracle Memory Usage
  4. Using "omemuse"
  5. Further Reading
  6. Example Code Repository


Overview

The following article should serve as an introduction to reporting Oracle memory usage within the Sun Solaris environment. This article is geared towards both Oracle DBAs and UNIX (Solaris) System Administrators.
Introduction to Oracle Processes
Before starting to talk about Oracle memory usage, you should first understand the three different types of processes within the Oracle architecure.

  1. Background Processes : All Oracle background processes get created when you startup the database instance. These background processes will have 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 system monitor background process for a database instance named PROD would be "ora_smon_PROD". Other common background processes are: system monitor (smon), process monitor (pmon), database writer (dbw0), log writer (lgwr), recovery (reco), and check point (ckpt), but may include others as well.

  2. User or Client Processes : These processes are created when you start an Oracle client program that will interact with an Oracle database. Some of the more common examples of client processes are programs like sqlplus, sqlldr, exp and imp. These processes are named the same name of the command you used to start them. (i.e. The process for the program "sqlldr" would be called "sqlldr").

  3. Shadow or Server Processes : The final type of Oracle process is called the shadow or server process. These processes work directly with the Oracle database to carry out requests from client processes (see above). Shadow processes can be either dedicated to a single client 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 "PROD" would be named "oraclePROD".
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 "/usr/proc/bin/pmap". This program can be located on the Sun Solaris cdrom as part of the Solaris Extended System Utilities package SUNWesu (32-bit) or SUNWesxu (64-bit). Use the "pkgadd" command to add this package if desired.

Using "omemus"
The UNIX shell script "omemuse" relies on /usr/proc/bin/pmap to help simplify the process of determining Oracle memory usage. For help with the different command options to omemuse, run the script with the "h" parameter or with no parameter at all:
  % omemuse h
The script will run pmap against Oracle processes that you specify, then report how much and what type of memory (private vs. shared) is being utilized.

The value of private memory will be approximately the same for all oracle background processes, since all of these processes are just different invocations of the same executable, $ORACLE_HOME/bin/oracle.

Determining memory usage for shadow processes can be 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. The value for private memory returned for shadow processes with the omemuse script 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 omemuse script 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.

To see a snapshot of memory usage for all Oracle background and shadow processes, invoke omemuse as follows:

  % omemuse SB
This will display the total private memory for all Oracle shadow processes, then the total private memory for all Oracle background processes, followed by the total shared memory used by all Oracle shadow and background processes, and finally, the grand total of all memory used by this Oracle instance.
Further Reading
For more information about the pmap command and determining process memory usage, please refer to the following articles from Sun...
Example Code Repository
UNIX Script: omemuse
    omemuse
The following UNIX script allows the DBA to report on Oracle Memory usage in the Solaris environment. By using the Solaris "pmap" utility, the omemuse script reports memory usage on all Oracle processes, (background processes as well as shadow processes) while breaking up the report into which type of memory it is utilizing (private vs. shared)
    omemuse.out
Several runs and output of omemuse


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Saturday, 18-Sep-2010 17:53:14 EDT
Page Count: 50786