Oracle Instance Memory Usage

How much memory does my Oracle instance use? How much memory do my database connections use?

These are questions that can help with capacity planning of your server’s Physical and Virtual memory. There are several write ups out there on the web that attempt to address these questions. From what I could gather from them, there is only one truly good way to tell exactly how much memory is currently in use by an oracle instance (or any other system, http, mysql, etc), as well as the average memory usage for oracle dedicated connection processes.

This technique makes use of the “pmap” command. This command displays the real current memory usage of a process broken down by memory segment types. By parsing the output of pmap, we can make several useful calculations. Note that this command is available for Linux, Solaris and HP-UX servers. It is also apparently available on AIX as “procmap”.

I have tested the below script on Lunix and Solaris only. It should be noted that pmap on Solaris is very slow. It can take several seconds per process (15+ seconds during my tests), which could translate into a very slow execution when running pmap on lots of oracle processes (I’ve had a case that took 1.25 hours for only 201 connections). On Linux, pmap is pretty much instantaneous.

My interest in pmap was really to determine the real current memory usage of each process and aggregate this information in a useful manner for capacity planning as well as trend and monitoring purposes. Based on the description of pmap in the following post: Memory Areas, I have come to the conclusion that we need the following information to size the current memory usage of an oracle instance:

1- The total memory used by the SGA’s shared memory segments;

This is what we see with the “ipcs” command. The size of the shared memory segment is always, from my experience, slightly larger than what the “show sga” command would show in sqlplus. These shared memory segments need to be accounted only once in our calculations.

These shared memory segments are clearly identified by pmap. They are shown as “shmid”, and sometimes as “deleted” on Linux. I have not been able to check on HP-UX nor AIX and I have not checked every versions of Linux and Solaris so you should verify this on your platform and adjust the below script as appropriate.

2- The size of each shared object code (oracle binary plus shared libraries);

This is the “fixed” portion of the binary. This portion of the binary is “shared” amongst all running instances of the binary. For oracle, the main binary is $ORACLE_HOME/bin/oracle. As most of you may know, the oracle binary also loads several shared library objects, including system libraries. Those objects also have their own “shared” sections that need to be accounted only once in our calculations.

This section of the binaries can be seen using the “size” command. The pmap command also shows them very clearly as they are the memory segments that are set as “read-only”. Note that this can be displayed differently on different platforms and versions. Again, adjust the script as appropriate for your platform.

3- The size of private (variable) memory segments for each process;

This is the variable portion of the binary. As the process runs, these variable sections will use more memory for each instance of the process running. These variable sections are therefore private memory and need to be summed up.

These sections are clearly shown by pmap as well as they are all the memory segments that are set as “Read-Write”. Note that this can be displayed differently on different platforms and versions. Again, adjust the script as appropriate for your platform.

Note that the example in the post Memory Areas (see at the bottom of the page) actually shows the total writable/private memory segments and shared memory segments, but the pmap commands that I ran on Linux and Solaris did not print those totals so I need to do the calculations myself, which are pretty simple to do.

In the below script, I have decided to break down the memory usage in four components so that it can also give us the average memory consumption by dedicated connections. This could be useful for capacity planning, however, keep in mind that it could be skewed significantly if one or multiple oracle sessions are running some abnormally large queries and/or have a very large cursor opened.

Also, parallel operations and queries will “mostly” not be accounted in the dedicated connection stats as they do all most of their work via the instance parallel background processes and therefore will be summed up in the instance private memory usage.

Similarly, any shared database connections (MTS, DRCP) memory are mostly part of the SGA and are therefore not counted as distinct db processes but as part of the instance shared and private memory.

The output of the script can be in one of two formats. A “long” format, which has long descriptive labels for each value and one value per line. And a “columnar” format, which shows the same calculated values in a columnar format. It can be used to capture this information in a similar fashion as sar or other OS type monitoring tools. This can be useful to see the memory usage trends and highs and lows over time (you probably don’t want to do that on Solaris if pmap is as slow as the one I used).

Here’s a sample output of each format:

$ ./m.ksh sid long

memory used by Oracle instance sid as of Wed Dec 28 19:51:49 GMT 2011

Total shared memory segments for the instance..................: 26216448 KB
Shared binary code of all oracle processes and shared libraries: 233920 KB
Total private memory usage by dedicated connections............: 3329516 KB
Total private memory usage by instance processes...............: 5530812 KB
Number of current dedicated connections........................: 52
Average memory usage by database connection....................: 64029 KB
Grand total memory used by this oracle instance................: 35310696 KB
$ ./m.ksh sid columnar

             date ORACLE_SID  instshmsz   binlibsz tconprivsz instprivsz   countcon avgcprivsz      sumsz
----------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
11/12/28_19:51:27        sid   26216448     233920    3331868    6617188         52      64074   36399424

Here is the korn shell script:

#!/bin/ksh
#based on explanations of pmap in http://www.makelinux.co.il/books/lkd2/ch14lev1sec2

#Verify the parameter count
if [ $# -lt 2 ]; then
   echo "Usage: $0 ORACLE_SID [long|columnar]
   echo " e.g.: $0 PROD columnar
   exit 1
fi

#Set variables
export ORACLE_SID=$1
output_type=$2

#running calculations...

export pids=`ps -elf|grep oracle$ORACLE_SID|grep -v grep|awk '{print $4}'`

export countcon=`print "$pids"|wc -l`

if [ "`uname -a|cut -f1 -d' '`" = "Linux" ]; then
   export tconprivsz=$(pmap -x `print "$pids"`|grep " rw"|grep -Ev "shmid|deleted"|awk '{total +=$2};END {print total}')
else
   export tconprivsz=$(pmap -x `print "$pids"`|grep " rw"|grep -v "shmid"|awk '{total +=$2};END {print total}')
fi

export avgcprivsz=`expr $tconprivsz / $countcon`

if [ "`uname -a|cut -f1 -d' '`" = "Linux" ]; then
   export instprivsz=$(pmap -x `ps -elf|grep ora_.*_$ORACLE_SID|grep -v grep|awk '{print $4}'`|grep " rw"|grep -Ev "shmid|deleted"|awk '{total +=$2};END {print total}')
else
   export instprivsz=$(pmap -x `ps -elf|grep ora_.*_$ORACLE_SID|grep -v grep|awk '{print $4}'`|grep " rw"|grep -v "shmid"|awk '{total +=$2};END {print total}')
fi

if [ "`uname -a|cut -f1 -d' '`" = "Linux" ]; then
   export instshmsz=$(pmap -x `ps -elf|grep ora_pmon_$ORACLE_SID|grep -v grep|awk '{print $4}'`|grep -E "shmid|deleted"|awk '{total +=$2};END {print total}')
else
   export instshmsz=$(pmap -x `ps -elf|grep ora_pmon_$ORACLE_SID|grep -v grep|awk '{print $4}'`|grep "shmid"|awk '{total +=$2};END {print total}')
fi

export binlibsz=$(pmap -x `ps -elf|grep ora_pmon_$ORACLE_SID|grep -v grep|awk '{print $4}'`|grep -v " rw"|  awk '{total +=$2};END {print total}')

export sumsz=`expr $tconprivsz + $instprivsz + $instshmsz + $binlibsz`

if [[ "$output_type" = "long" ]]; then
   echo memory used by Oracle instance $ORACLE_SID as of `date`
   echo
   echo "Total shared memory segments for the instance..................: "$instshmsz KB
   echo "Shared binary code of all oracle processes and shared libraries: "$binlibsz KB
   echo "Total private memory usage by dedicated connections............: "$tconprivsz KB
   echo "Total private memory usage by instance processes...............: "$instprivsz KB
   echo "Number of current dedicated connections........................: "$countcon
   echo "Average memory usage by database connection....................: "$avgcprivsz KB
   echo "Grand total memory used by this oracle instance................: "$sumsz KB
   echo
elif [ "$output_type" = "columnar" ]; then
   printf "%17s %10s %10s %10s %10s %10s %10s %10s %10s\n" "date" "ORACLE_SID" "instshmsz" "binlibsz" "tconprivsz" "instprivsz" "countcon" "avgcprivsz" "sumsz"
   echo "----------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------"
   printf "%17s %10s %10s %10s %10s %10s %10s %10s %10s\n" "`date +%y/%m/%d_%H:%M:%S`" $ORACLE_SID $instshmsz $binlibsz $tconprivsz $instprivsz $countcon $avgcprivsz $sumsz
fi;

Hopefully this will be useful to you as much as it has been for me. It allowed me to demonstrate to a client that they were over allocating their memory by having too many dedicated connections and an overly large SGA for the capacity of the server it was running on.

Enjoy!
Marc Billette

References:

http://www.makelinux.co.il/books/lkd2/ch14lev1sec2,

Tanel Poder has an interesting post about using pmap and an interesting script as well to summarize the memory used by segment types. At the time of this writeup, it is available here: Using Process Memory Matrix script for understanding Oracle process memory usage