Oracle Instance Memory Usage

Dec 29, 2011 / By Marc Billette

Tags: ,

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 %10sn" "date" "ORACLE_SID" "instshmsz" "binlibsz" "tconprivsz" "instprivsz" "countcon" "avgcprivsz" "sumsz"
   echo "----------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------"
   printf "%17s %10s %10s %10s %10s %10s %10s %10s %10sn" "`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

24 Responses to “Oracle Instance Memory Usage”

  • Tom Kyle says:

    Hi Marc,

    Thanks for sharing. One small caveat: the output for pmap -x is different on older versions. For example, here’s the output for procps 3.23 (RHEL 4):

    28298: oracledev1 (LOCAL=NO)
    Address Kbytes RSS Anon Locked Mode Mapping
    —————- —— —— —— ——
    total kB 0 – – -

    From my (limited) testing, it looks like procps 3.27 in RHEL5 has the full output your script is expecting.

    Thanks,

    Tom

  • Fahd Mirza says:

    Super post, Marc. Thanks for sharing this.

  • Raivis says:

    Somehow I got really huge numbers of script executed on just installed database in OEL 5.2 64bit where there is Mem: 3866604k total configured. 50G is way too much for 9 default proceses, can You give me some hint?

    memory used by Oracle instance ORCL as of Tue Jan 3 10:43:53 EET 2012

    Total shared memory segments for the instance………………: KB
    Shared binary code of all oracle processes and shared libraries: 236988 KB
    Total private memory usage by dedicated connections…………: 14267160 KB
    Total private memory usage by instance processes……………: 36387736 KB
    Number of current dedicated connections……………………: 9
    Average memory usage by database connection………………..: 1585240 KB
    Grand total memory used by this oracle instance…………….: 50891884 KB

    SQL> sho sga
    Total System Global Area 1586708480 bytes
    Fixed Size 2228744 bytes
    Variable Size 956304888 bytes
    Database Buffers 620756992 bytes
    Redo Buffers 7417856 bytes

    SQL> sho parameter memor
    NAME TYPE VALUE
    ———————————— ———– ——————————
    memory_max_target big integer 1520M
    memory_target big integer 1520M

    ipcs -m | grep oracle
    0×00000000 1230929923 oracle 640 4096 0
    0×00000000 1230962698 oracle 640 4096 0
    0xc60e6588 1230995467 oracle 640 4096 0

    p.s. for HPUX pmap usage is without key -x, just pmap

  • Marc says:

    Raivis, something looks wrong with the output you posted. First, it did not find the shared memory segment. You should review the output of your pmap -x command on one oracle process and see if it shows large segments and if they are showing as “shmid” or “deleted”. If they have a different label, then adjust the greps in the script as appropriate.

    Second, if that output is right, you seem to have one or more sessions that are using lots of private memory. They may be running, or ran in the past, some massive queries/cursors.

  • Marc says:

    Raivis, I just clued in that if your pmap is not showing the sga’s shared memory segment as per regexp “shmid|deleted”, then it will get summed up as private data for each process, which would result in the type of bad figures you are seeing…

  • You could also ask Oracle http://rafudb.blogspot.com/2012/01/oracle-instance-memory-usage.html about the instance memory usage. If you have the diagnostics pack purchased you are able to get numbers from the past.

  • Austin Hackett says:

    Hi Marc

    I realize this is an old post now, but maybe the following info will be of use to someone in future…

    I too was getting huge values reported. The database was 11gR2 and used ASMM on OEL 5.4. The ” rw” regexp meant that addresses with a mapping to the POSIX shared memory segments in /dev/shm were included in the total. I added the following extra greps to exclude POSIX shared memory:

    tconprivsz: grep -v ” rwxs-”
    instprivsz: grep -v ” rwxs-”
    binlibsz: grep -v ” r-xs-”

    Then, instshmsz needs to be whatever sga_maz_size (or presumably memory_max_target for AMM) is set to in KBytes.

    • Marc says:

      Great Austin. Glad you could get it working for you. FYI, I had a case this week where the instance I was measuring had no connections to it. That breaks a few things in the script as it ends up with a countcon=0. It’s pretty easy to add a workaround if you ever need to do this…

      Cheers,
      Marc

  • Austin Hackett says:

    Hi Marc

    Thanks Marc.

    I referred this post to a colleague, and he suggested an alternative approach of using pmap -d, and summing the ‘private/writable’ total in the summary line to calculate the private memory used by the dedicated connections and instance processes.

    I was curious to know if there was a particular reason for using pmap -x instead of pmap -d?

    It would be great to get your thoughts if you had a moment…

    • Marc says:

      Hi Austin,

      well, basically I started this investigation for a database running on Solaris and the “-d” option is not available on that platform. I think it would work fine with Linux, however be aware that some versions of Linux do not print the totals at the end for some unknown reason and therefore it was more reliable for me to use pmap -x.

  • Gobinathan Manivel says:

    Hi,

    Thanks for this post, However I’m not able to run this script in bash shell ..Could you please tell me how to run this in bash shell on RHEL5..I’m getting below error

    [oracle@zw-rac-prd-01 tmp]$ ./m.sh ZWFCCUBS1 long
    ./m.sh: line 19: print: command not found
    ./m.sh: line 22: print: command not found
    Usage: pmap [-x | -d] [-q] pid…
    -x show details
    -d show offset and device number
    -q quiet; less header/footer info
    -V show the version number
    expr: syntax error
    expr: non-numeric argument
    memory used by Oracle instance ZWFCCUBS1 as of Fri Aug 24 14:09:32 CAT 2012

    Total shared memory segments for the instance………………: KB
    Shared binary code of all oracle processes and shared libraries: 0 KB
    Total private memory usage by dedicated connections…………: KB
    Total private memory usage by instance processes……………: KB
    Number of current dedicated connections……………………: 0
    Average memory usage by database connection………………..: KB
    Grand total memory used by this oracle instance…………….: KB

  • Hi Gobinathan,

    Sorry for the delay, I just came back from vacation…

    I take it that your system does not have ksh installed? In that case, you should be fine with updating the ‘print “$pids”‘ strings with ‘”echo “$pids”‘. Be sure to keep the double quotes around $pids.

  • Great blog post .. to the point and useful :)

    Thanks for sharing this information.

  • sumer says:

    Hi,

    its not displaying all the value for me in output …

    {ediras01}/home/oracle/USEDIPDB/scripts> ./memory.ksh USEDIPDB long
    expr: An integer value was expected.

    expr: An integer value was expected.

    memory used by Oracle instance USEDIPDB as of Thu Nov 8 07:52:29 EST 2012

    Total shared memory segments for the instance………………: KB
    Shared binary code of all oracle processes and shared libraries: 22248.1 KB
    Total private memory usage by dedicated connections…………: 3.4444e+06 KB
    Total private memory usage by instance processes……………: 751379 KB
    Number of current dedicated connections……………………: 148
    Average memory usage by database connection………………..: KB
    Grand total memory used by this oracle instance…………….: KB

    OS is HP-UX…

  • Steve C says:

    thanks, works a treat for me (Solaris).
    one of the most useful scripts i’ve come across. I had tried to do this once before with pmap manually, but this script will save a lot of time.

  • Hitung memory yang dibutuhkan oracle instance « Just Do It. Now. says:

    [...] source : http://www.pythian.com/news/29703/oracle-instance-memory-usage/ [...]

  • anonuser says:

    Hi !
    nice script.
     
    The calculation failed in case no connection exists. (no process like oracle$ORACLE_SID exist)
    In this case the following command will give no result and expr in next steps fail.
    export pids=`ps -elf|grep oracle$ORACLE_SID|grep -v grep|awk ‘{print $4}’`
     
    Regards

  • Praveen says:

    Hi Marc,

    Nice blog and great information. The script needs to be tweaked at few places as per requirement, however, its really nice and precise information.

    Thanks!!!

    Cheers,
    Praveen

  • balaji says:

    Hi Sumer,
    The reason for the error is that it is not able to make arithmatic operations on the variable as the values are expressed as exponents or floating point values.
    Please try
    eg:
    export instshmsz=`printf “%9.0f\n” $instshmsz`
    This must resolve the issue you are facing.

  • Andres Ramirez says:

    Hi
    You can use this program in C to find oracle process memory usage

    http://ejemplosprogramacionc.blogspot.com/2013/06/memoria-de-procesos-oracle-sobre.html

  • Lucas Lellis says:

    Hi Marc,

    I’ve included some tweaks suggested here and some of my own (e.g..: PGA memory). The source code is available at memory.sh.

    Cheers,
    Lucas

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>