Goal: for a given process, find out how the SGA was allocated in different locality groups on a system running Solaris operating system.
Download the shell script, sga_in_lgrp.sh. The script accepts any Oracle database process id as input, and prints out the memory allocated in each locality group.
Usage: ./sga_in_lgrp.sh <pid>
eg.,
# prstat -p 12820
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
12820 oracle 32G 32G sleep 60 -20 0:00:16 0.0% oracle/2
# ./sga_in_lgrp.sh 12820
Number of Locality Groups (lgrp): 4
------------------------------------
lgroup 1 : 8.56 GB
lgroup 2 : 6.56 GB
lgroup 3 : 6.81 GB
lgroup 4 : 10.07 GB
Total allocated memory: 32.00 GB
For those who wants to have a quick look at the source code, here it is.
# cat sga_in_lgrp.sh
#!/bin/bash
# check the argument count
if [ $# -lt 1 ]
then
echo "usage: ./sga_in_lgrp.sh <oracle pid>"
exit 1
fi
# find the number of locality groups
lgrp_count=$(kstat -l lgrp | tail -1 | awk -F':' '{ print $2 }')
echo "\nNumber of Locality Groups (lgrp): $lgrp_count"
echo "------------------------------------\n"
# save the ism output using pmap
pmap -sL $1 | grep ism | sort -k5 > /tmp/tmp_pmap_$1
# calculate the total amount of memory allocated in each lgroup
for i in `seq 1 $lgrp_count`
do
echo -n "lgroup $i : "
grep "$i \[" /tmp/tmp_pmap_$1 | awk '{ print $2 }' | sed 's/K//g' | awk '{ sum+=$1} END {printf ("%6.2f GB\n", sum/(1024*1024))}'
done
echo
echo -n "Total allocated memory: "
awk '{ print $2 }' /tmp/tmp_pmap_$1 | sed 's/K//g' | awk '{ sum+=$1} END {printf ("%6.2f GB\n\n", sum/(1024*1024))}'
rm /tmp/tmp_pmap_$1
Like many things in life, there will always be a better or simpler way to achieve this. If you find one, do not fret over this approach. Please share, if possible.
