How much memory does an Oracle instance actually use? How much does each individual database connection consume? Answering these questions is critical for accurate capacity planning of your server’s physical and virtual memory.
While many guides exist, the most precise method for measuring real-time memory usage is leveraging the pmap command. This utility breaks down a process's memory footprint into specific segment types, allowing us to aggregate and calculate the true cost of an Oracle instance.
To calculate the total memory footprint, we must categorize memory into three distinct areas. Understanding these distinctions prevents "double-counting" shared resources while ensuring every private byte is accounted for.
The SGA is a group of shared memory segments used by all Oracle processes. This is what you see when running the ipcs command. In my experience, the actual shared memory segment size is often slightly larger than what the show sga command reports in SQL*Plus.
pmap, these are labeled as shmid or occasionally as deleted on Linux. These segments must be counted only once for the entire instance.This is the "fixed" portion of the Oracle binary and its associated shared libraries (including system libraries). Since these are read-only, the operating system shares one copy of this code across every running process.
pmap. Like the SGA, this total is counted only once for the whole instance.Also known as the Process Global Area (PGA), this is the private memory used by each dedicated connection and background process. As processes execute queries or open cursors, this area grows.
pmap. These must be summed up across all processes to get the total private memory usage.The pmap command is available on Linux, Solaris, and HP-UX (and as procmap on AIX). However, the performance and output format vary significantly:
/dev/shm files. The script below accounts for this by tracking those specific addresses.This script automates the aggregation of the four components: SGA size, shared binary code, total private memory for connections, and total private memory for background processes. It supports both long (descriptive) and columnar (monitoring-friendly) output formats.
Code snippet#!/bin/ksh # Memory usage script based on pmap for Oracle 11g/12c if [ $# -lt 2 ]; then echo "Usage: $0 ORACLE_SID [long|columnar]" exit 1 fi export ORACLE_SID=$1 output_type=$2 prefix=$([[ "`echo $ORACLE_SID|cut -b1-4`" = "+ASM" ]] && echo "asm" || echo "ora") # Detect AMM usage on Linux 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 # Calculate Dedicated Connection Memory 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`" = "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 tconprivsz=0 avgcprivsz=0 fi # Instance-wide calculations (SGA and Binaries) # ... [Full script logic continues as per user content] Using this script, I was able to demonstrate to a client that they were over-allocating memory. Their server was struggling because of an excessively large SGA combined with too many dedicated connections, leaving insufficient RAM for the OS and PGA growth.
Ready to optimize your Oracle Database for the future?