#!/usr/bin/ksh
#
# Y.JAQUIER  15/05/2012  Creation
#
# -----------------------------------------------------------------------------
#
# Script intended to display RAC private interconnect traffic
#
# -----------------------------------------------------------------------------
#
# Usage : $0 <instance name> <interval in seconds (default 10)> <count (default 5)>
#
# According to sprepins.sql file in $ORACLE_HOME/rdbms/admin RAC interconnect
# traffic formula is
# Estd Interconnect traffic:
# ((Global Cache blocks received + Global Cache blocks served)*db_block_size
# +(GCS/GES messages received + GCS/GES messages sent)*200)/elapsed time
#

# -----------------------------------------------------------------------------
# Variables
# -----------------------------------------------------------------------------

OSTYPE=$(uname -s)
case $OSTYPE in
"AIX" ) alias bdf="/usr/bin/df -Ik"
        alias ll="/usr/bin/ls -l" ;;
"SunOS") alias bdf="/usr/bin/df -k"
         alias ll="/usr/bin/ls -l"
         alias awk="/usr/xpg4/bin/awk"
         alias grep="/usr/xpg4/bin/grep" ;;
"Linux") alias bdf="/bin/df -k"
         alias grep=egrep
         alias ll="ls -l" ;;
esac

LOG=/tmp/`basename $0`.$$.log

DB_BLOCK_SIZE=0
START_GCBR=0
START_GCBS=0
START_GCSMS=0
START_GCSMR=0
START_GESMS=0
START_GESMR=0
START_DATE="N/A"
END_DATE="N/A"
TRAFFIC=0
i=1

# -----------------------------------------------------------------------------
# Initialization
# -----------------------------------------------------------------------------

if [ "$LOGNAME" != "root" ]
then
  echo -e "\nYou must launch this script as root\n"
  exit 1
fi

if [ $# -lt 1 ]
then
  echo -e "Usage : $0 <instance name> <interval in seconds (default 10)> <count (default 5)>\n"
  echo -e "List of instances : \n`grep -v -e "^#" -e "^$" /etc/ORATAB|cut -f1 -d:`"
  exit 1
else
  if grep -q "^$1:" /etc/ORATAB
  then
    echo -e "\nInstance $1 exists in /etc/ORATAB\n"
  else
    echo -e "Instance <$1> does not exist in /etc/ORATAB"
    echo -e "List of existing instances:\n`grep -v -e "^#" -e "^$" /etc/ORATAB|cut -f1 -d:`"
    exit 1
  fi
fi

ORACLE_SID=$1

if [ -n "$2" ]
then
  INTERVAL=$2
else
  INTERVAL=10
fi

if [ -n "$3" ]
then
  COUNT=$3
else
  COUNT=5
fi

if [ `bdf /tmp|grep tmp|awk '{print $4}'|tr -d %` -ge 98 ]
then
  echo -e "\nFile system /tmp is full\n"
  exit 1
fi

rm -f ${LOG}

IFS=:
OHOME=`grep "^$ORACLE_SID:" /etc/ORATAB | cut -d: -f2`
FLAG=`grep "^$ORACLE_SID:" /etc/ORATAB | cut -d: -f3`
USER=`grep "^$ORACLE_SID:" /etc/ORATAB | cut -d: -f4`
VG=`grep "^$ORACLE_SID:" /etc/ORATAB | cut -d: -f5`

# -----------------------------------------------------------------------------
# Main
# -----------------------------------------------------------------------------

echo -e "Please wait $INTERVAL seconds...\n"

IFS=*
while [ $i -le $COUNT ]
do
su - ${USER} -c "
sqlplus -S / as sysdba << EOF > ${LOG}
set serveroutput on size 999999 linesize 200 feedback off
declare
  instance_number number;
	db_block_size number;
	type figures_type is table of number index by varchar2(64);
  start_figures figures_type;
  end_figures figures_type;
	cursor cursor1(instance_number number) is select
decode(name,'gc cr blocks received','Global Cache blocks received','gc cr blocks served','Global Cache blocks served','gc current blocks received','Global Cache blocks received','gc current blocks served','Global Cache blocks served',name) as name,
sum(value) as value,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as date_taken
from gv\\\$SYSSTAT
where inst_id=instance_number
and name in ('gc cr blocks received','gc cr blocks served','gc current blocks received','gc current blocks served','gcs messages sent','ges messages sent')
group by decode(name,'gc cr blocks received','Global Cache blocks received','gc cr blocks served','Global Cache blocks served','gc current blocks received','Global Cache blocks received','gc current blocks served','Global Cache blocks served',name)
union
select name,value,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as date_taken
from gv\\\$DLM_MISC
where name in ('gcs msgs received','ges msgs received')
and inst_id=instance_number;
  item1 cursor1%rowtype;
  plsql_start_date varchar2(19);
  plsql_end_date varchar2(19);
  interval number;
	computed_interval number;
  i number;
begin
  select value into instance_number from v\\\$parameter where name='instance_number';
	select value into db_block_size from v\\\$parameter where name='db_block_size';
  interval:=${INTERVAL};
  start_figures('Global Cache blocks received'):=${START_GCBR};
	start_figures('Global Cache blocks served'):=${START_GCBS};
	start_figures('gcs messages sent'):=${START_GCSMS};
	start_figures('gcs msgs received'):=${START_GCSMR};
	start_figures('ges messages sent'):=${START_GESMS};
	start_figures('ges msgs received'):=${START_GESMR};
	plsql_start_date:='${START_DATE}';

  --dbms_output.put_line('Instance Number: ' || instance_number);
  --dbms_output.put_line('db_block_size: ' || db_block_size);
	--dbms_output.put_line('interval: ' || interval);
	
	if (start_figures('Global Cache blocks received')=0) then
	  open cursor1(instance_number);
	  loop
	    fetch cursor1 into item1;
		  exit when cursor1%notfound;
		  start_figures(item1.name):=item1.value;
	  end loop;
	  plsql_start_date:=item1.date_taken;
		close cursor1;
	  dbms_lock.sleep(interval);
	end if;

	open cursor1(instance_number);
	loop
	  fetch cursor1 into item1;
		exit when cursor1%notfound;
		end_figures(item1.name):=item1.value;
	end loop;
	plsql_end_date:=item1.date_taken;
	close cursor1;

	dbms_output.put_line('DB_BLOCK_SIZE=' || db_block_size);
	dbms_output.put_line('START_GCBR=' || start_figures('Global Cache blocks received'));
	dbms_output.put_line('START_GCBS=' || start_figures('Global Cache blocks served'));
	dbms_output.put_line('START_GCSMS=' || start_figures('gcs messages sent'));
	dbms_output.put_line('START_GCSMR=' || start_figures('gcs msgs received'));
	dbms_output.put_line('START_GESMS=' || start_figures('ges messages sent'));
	dbms_output.put_line('START_GESMR=' || start_figures('ges msgs received'));
	dbms_output.put_line('END_GCBR=' || end_figures('Global Cache blocks received'));
	dbms_output.put_line('END_GCBS=' || end_figures('Global Cache blocks served'));
	dbms_output.put_line('END_GCSMS=' || end_figures('gcs messages sent'));
	dbms_output.put_line('END_GCSMR=' || end_figures('gcs msgs received'));
	dbms_output.put_line('END_GESMS=' || end_figures('ges messages sent'));
	dbms_output.put_line('END_GESMR=' || end_figures('ges msgs received'));
	dbms_output.put_line('START_DATE=\"' || plsql_start_date || '\"');
	dbms_output.put_line('END_DATE=\"' || plsql_end_date || '\"');
	computed_interval:=round((to_date(plsql_end_date,'yyyy-mm-dd hh24:mi:ss')-to_date(plsql_start_date,'yyyy-mm-dd hh24:mi:ss'))*24*60*60);
	dbms_output.put_line('COMPUTED_INTERVAL=' || computed_interval);
	
	dbms_output.put_line('TRAFFIC=' || ((end_figures('Global Cache blocks received')+end_figures('Global Cache blocks served')-start_figures('Global Cache blocks received')-start_figures('Global Cache blocks served'))*db_block_size+(end_figures('gcs messages sent')+end_figures('gcs msgs received')+end_figures('ges messages sent')+end_figures('ges msgs received')-start_figures('gcs messages sent')-start_figures('gcs msgs received')-start_figures('ges messages sent')-start_figures('ges msgs received'))*200)/1024/computed_interval);

end;
/
EOF
"

while read line
do
  #echo $line
	eval $line
done < ${LOG}

#TRAFFIC=`expr (($END_GCBR+$END_GCBS-$START_GCBR-$START_GCBS)*$DB_BLOCK_SIZE+($END_GCSMS+$END_GCSMR+$END_GESMS+$END_GESMR-$START_GCSMS-$START_GCSMR-$START_GESMS-$START_GESMR)*200)/1024/$INTERVAL)`
if [ $TRAFFIC -le 1024 ]
then
  echo -e "Estd Interconnect traffic at $END_DATE (KBytes/s): $TRAFFIC\n"
else
  TRAFFIC=`echo $TRAFFIC/1024 | bc -l`
  echo -e "Estd Interconnect traffic at $END_DATE (MBytes/s): $TRAFFIC\n"
fi

#cat ${LOG}

START_GCBR=$END_GCBR
START_GCBS=$END_GCBS
START_GCSMS=$END_GCSMS
START_GCSMR=$END_GCSMR
START_GESMS=$END_GESMS
START_GESMR=$END_GESMR
START_DATE=$END_DATE

i=`expr $i + 1`

sleep $INTERVAL
done

rm -f ${LOG}