Calculates RAM memory used by an Oracle Instance - by Marc Billette - http://www.pythian.com/blog/oracle-instance-memory-usage/

memory.sh

#!/bin/ksh
#based on explanations of pmap in http://www.makelinux.co.il/books/lkd2/ch14lev1sec2
# Author: Marc Billette
# Ref: http://www.pythian.com/blog/oracle-instance-memory-usage/
# Description: Calculates RAM memory used by an Oracle Instance
# Changes
# Austin Hackett - 06/06/2012 - Extra greps to exclude POSIX Shared Memory
# Lucas Lellis - 11/16/2013 - Error treatment when there are no connections
#                           - Average memory for background processes
#                           - Treating job queue processes as user process, as in v$session
#                           - Distinction between PGA and non-PGA process memory
#                           - Adapted for AMM on Linux

#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
ORACLE_SID=$1
output_type=$2

#running calculations...

pids=`ps -elf|grep -E "oracle$ORACLE_SID|ora_j[0-9]{3}_$ORACLE_SID"|grep -v grep|awk '{print $4}'`

if [[ -n $pids ]]; then
    countcon=`print "$pids"|wc -l`
else
    countcon=0
fi

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

instprocs=`ps -elf|grep ora_.*_$ORACLE_SID|grep -Ev "ora_j[0-9]{3}_$ORACLE_SID"|grep -v grep|awk '{print $4}'`
countinstprc=0
for pid in $instprocs; do
    ((countinstprc+=1))
done

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

avginstprivsz=`expr $instprivsz / $countinstprc`

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

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

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

tmp_file="/tmp/memory_$(date '+%Y%m%d%H%M%S')"
rm -f ${tmp_file}

sqlplus -S / as sysdba > ${tmp_file} <<ENDEND
set head off feed off pages 0 newpage none trimspool on
select sess.type||';'||ceil(sum(ss.value/1024)) pga_memory_kb
from v\$session sess
join v\$sesstat ss on ss.sid = sess.sid
join v\$statname st on ss.statistic# = st.statistic#
where st.name = 'session pga memory'
and not exists (select 1 from v\$mystat m where m.sid = sess.sid)
group by sess.type
order by sess.type;
exit
ENDEND

sessionpga=$(grep USER ${tmp_file} | cut -d';' -f2)
instpga=$(grep BACKGROUND ${tmp_file} | cut -d';' -f2)

if [[ $countcon -gt 0 ]]; then
    avgsesspga=`expr $sessionpga / $countcon`
else
    avgsesspga=0
fi

avginstpga=`expr $instpga / $countinstprc`

rm -f ${tmp_file}

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 dedicated connections in PGA.....: "$sessionpga KB
   echo "Number of current dedicated connections........................: "$countcon
   echo "Average memory usage by database connection....................: "$avgcprivsz KB
   echo "Average memory usage by database connection in PGA.............: "$avgsesspga KB
   echo "Total private memory usage by instance processes...............: "$instprivsz KB
   echo "Number of current instance processes...........................: "$countinstprc
   echo "Average memory usage by instance process.......................: "$avginstprivsz KB
   echo "Average memory usage by instance process in PGA................: "$avginstpga 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 %10s %12s %13s %10s %10s\n" "date" "ORACLE_SID" "instshmsz" "binlibsz" "tconprivsz" "sessionpga" "countcon" "avgcprivsz" "avgsesspga" "instprivsz" "countinstprc" "avginstprivsz" "avginstpga" "sumsz"
   echo "----------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ ------------- ---------- ----------"
   printf "%17s %10s %10s %10s %10s %10s %10s %10s %10s %10s %12s %13s %10s %10s\n" "`date +%y/%m/%d_%H:%M:%S`" $ORACLE_SID $instshmsz $binlibsz $tconprivsz $sessionpga $countcon $avgcprivsz $avgsesspga $instprivsz $countinstprc $avginstprivsz $avginstpga $sumsz
fi