Skip to content

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Calculates RAM memory used by an Oracle Instance - by Marc Billette - http://www.pythian.com/blog/oracle-instance-memory-usage/
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.