Pythian Blog: Technical Track

Oracle Instance Memory Usage

by Pythian Marketing on Dec 29, 2011 12:00:00 AM

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).   Updated Script for AMM in 12c and 11g: #!/bin/ksh #based on explanations of pmap #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 #determine if the instance is an ASM or db if [ "`echo $ORACLE_SID|cut -b1-4`" = "+ASM" ]; then export prefix="asm" else export prefix="ora" fi #determine if the instance uses AMM on Linux (/dev/shm files for shared memory) export dev_shm_count=$(pmap `ps -elf|grep ${prefix}_pmon_$ORACLE_SID| grep -v grep|awk '{print $4}'` | grep /dev/shm | wc -l) pmap `ps -elf|grep ${prefix}_pmon_$ORACLE_SID| grep -v grep|awk '{print $4}'` | grep /dev/shm | awk '{print $1}' > shm_addresses #running calculations... export pids=`ps -elf|grep oracle$ORACLE_SID|grep -v grep|awk '{print $4}'` if [ -n "$pids" ]; then export countcon=`print "$pids"|wc -l` if [ "`uname -a|cut -f1 -d' '`" = "Linux" ]; then if [ $dev_shm_count -gt 0 ]; then export tconprivsz=$(pmap -x `print "$pids"`|grep " rw"|grep -Evf shm_addresses|awk '{total +=$2};END {print total}') else export tconprivsz=$(pmap -x `print "$pids"`|grep " rw"|grep -Ev "shmid|deleted"|awk '{total +=$2};END {print total}') fi else export tconprivsz=$(pmap -x `print "$pids"`|grep " rw"|grep -v "shmid"|awk '{total +=$2};END {print total}') fi export avgcprivsz=`expr $tconprivsz / $countcon` else export countcon=0 export tconprivsz=0 export avgcprivsz=0 fi if [ "`uname -a|cut -f1 -d' '`" = "Linux" ]; then if [ $dev_shm_count -gt 0 ]; then export instprivsz=$(pmap -x `ps -elf|grep ${prefix}_.*_$ORACLE_SID|grep -v grep|awk '{print $4}'`|grep " rw"|grep -Evf shm_addresses|awk '{total +=$2};END {print total}') else export instprivsz=$(pmap -x `ps -elf|grep ${prefix}_.*_$ORACLE_SID|grep -v grep|awk '{print $4}'`|grep " rw"|grep -Ev "shmid|deleted"|awk '{total +=$2};END {print total}') fi else export instprivsz=$(pmap -x `ps -elf|grep ${prefix}_.*_$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 if [ $dev_shm_count -gt 0 ]; then export instshmsz=$(pmap -x `ps -elf|grep ${prefix}_pmon_$ORACLE_SID| grep -v grep|awk '{print $4}'`|grep -Ef shm_addresses|awk '{total +=$2};END {print total}') else export instshmsz=$(pmap -x `ps -elf|grep ${prefix}_pmon_$ORACLE_SID| grep -v grep|awk '{print $4}'`|grep -E "shmid|deleted"|awk '{total +=$2};END {print total}') fi else export instshmsz=$(pmap -x `ps -elf|grep ${prefix}_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 ${prefix}_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` rm shm_addresses 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: 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
Topics: Oracle Technical Track Rac Oracle EBS Oracle Oda

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.