public
Last active

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

  • Download Gist
memory.sh
Shell
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120
#!/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

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.