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

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

Marc Billette

Want to talk with an expert? Schedule a call with our team to get the conversation started.

30 Comments. Leave new

Tom Kyle

December 29, 2011 1:03 pm

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

December 29, 2011 11:42 pm

Super post, Marc. Thanks for sharing this.

Raivis

January 3, 2012 5:32 am

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
0x00000000 1230929923 oracle 640 4096 0
0x00000000 1230962698 oracle 640 4096 0
0xc60e6588 1230995467 oracle 640 4096 0

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

Marc

January 3, 2012 3:24 pm

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.

January 3, 2012 3:31 pm

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…

Timo Raitalaakso

January 19, 2012 3:18 am

You could also ask Oracle https://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

June 6, 2012 7:52 am

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.

June 6, 2012 9:03 am

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

June 7, 2012 3:19 am

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…

June 8, 2012 8:22 am

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

August 24, 2012 7:15 am

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

[[email protected] 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

September 4, 2012 10:54 am

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.

Uday Vallamsetty

October 26, 2012 11:43 am

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

Thanks for sharing this information.

sumer

November 8, 2012 8:01 am

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…

November 9, 2012 2:37 pm

Hi Sumer,

I don’t have access to HP-UX so I can’t test nor tweak the script for that platform. Sorry.

Steve C

November 21, 2012 6:33 am

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.

December 18, 2012 10:06 pm

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

anonuser

January 22, 2013 4:55 am

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

February 27, 2013 5:13 am

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

June 12, 2013 6:02 am

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

June 17, 2013 10:49 pm

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

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

Lucas Lellis

November 16, 2013 5:53 pm

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

Andres Tarallo

March 14, 2014 10:17 am

Hi !!!! I’ve tried to run the script under SLES 11 SP1 and SLES11 SP2. An update/upgrade of the procps package.

The script might need an update, the numbers I’m getting seems to me very high

April 3, 2014 12:02 pm

Hi Andres, please contact me at lucaslellis[at]gmail[dot]com so we can solve the problems you’re having.

Duncan McCallum

May 29, 2014 2:34 pm

Hey Marc,

I’m an DBA consultant working mostly in the NCR and currently working on the myGCHR project.

I recently took your script memory.sh from an old blog posting of yours and tried to use it with a combination of 11g and 12c dbs and found that it did not seem to work correctly because of changes in how Oracle creates it shared memory segment on Linux.

For example, the instance gcupkdev’s main shmid is 1605658 and lists as:
[[email protected] ~]$ ll /dev/shm/ora_gcupkdev_1605658*
-rw-r—–. 1 oracle dba 0 May 8 09:04 /dev/shm/ora_gcupkdev_1605658_0
-rw-r—–. 1 oracle dba 0 May 8 09:04 /dev/shm/ora_gcupkdev_1605658_1
-rw-r—–. 1 oracle dba 0 May 8 09:04 /dev/shm/ora_gcupkdev_1605658_10

And if you count all 509 ‘slices’ of 16M you get a shm of 8144M, which is what we expect. But I couldn’t explain this before with backing from oracle corp.…until now; search for ‘granules’ on this page: https://docs.oracle.com/cd/E16655_01/server.121/e17636/memory.htm

Note; since the only two other shared memory segments of this instance are small, this works pretty well too:
[[email protected] ~]$ pmap -x $instprocs $bkgdprocs|grep ” rw-s-“|sort –key=1,2 -u|awk ‘{total+=$2} END {print total/1024}’
8208.07

So my remaining issues are:
1) Why does ipcs not report the true size? It’s like it’s just reporting the memory descriptor size (4K)
[[email protected] ~]$ ipcs -m -i 1605658

Shared memory Segment shmid=1605658
uid=15000 gid=15000 cuid=15000 cgid=15000
mode=0640 access_perms=0640
bytes=4096 lpid=31491 cpid=14785 nattch=0
att_time=Wed May 28 14:33:40 2014
det_time=Wed May 28 14:33:40 2014
change_time=Thu May 8 09:04:15 2014

2) Why does oracle allocate the shared memory segment(s) it needs for the ‘maximum memory’, and not just the current ‘total memory’; what’s the point of having two parameters if you still need a restart to reduce your shm footprint?
SQL> show parameter memory

NAME TYPE VALUE
———————————— ———– ——————————
hi_shared_memory_address integer 0
memory_max_target big integer 8G
memory_target big integer 3G
shared_memory_address integer 0

And is it related to the output of:
[[email protected] ~]$ ll /dev/shm/ora_gcupkdev_1605658* |awk ‘{total+=$5} END {print total/1048576}’
1728

May 6, 2015 7:50 pm

Hi Duncan,

Pythian agreed to replace the above script with a version I wrote a while ago that determins if your instance uses AMM, which uses /dev/shm memory files. Give it a shot and see if it works with your 12C instance. Cheers.

Pratik Maru

June 9, 2014 6:13 am

script was really fantastic;helped us lot in memory sizing for the server as we tend to have multiple instances on a single server

Reg Brink

May 20, 2020 3:32 pm

Is this script still valid?
I see HUGE differences between 12.1 and 12.2

David Lord

April 8, 2022 6:56 am

Here’s a minor fix to cope with a RAC management repository instance:

#determine if the instance is an ASM, management repository or db
if [ “`echo $ORACLE_SID|cut -b1-4`” = “+ASM” ]; then
export prefix=”asm”
elif [ “`echo $ORACLE_SID|cut -b1-4`” = “-MGM” ]; then
export prefix=”mdb”
else
export prefix=”ora”
fi

August 21, 2022 6:52 am

Wanting to test this on Solaris but having issues copying the script. Is there by any chacne, there is a copy of this script that is ‘real’ text-based? Tried copying and quotes are coming up funny :( Thanks.

Snowflake

SAP

Snowflake

SAP

Fixed Utility Menu