2 thoughts on “Oracle capacity planning with RRDTOOL”

1. Pingback: http://%/bvwkjea

2. #!/bin/bash

   # here is porting to bash – i like the idea .

   set -o nounset
   set -o errexit

   # global vars
   mysql=”sqlplus -s -l ”
   connstr=system/system@ora10
   rrdpath=/usr/local/rrdtool-1.2.19/bin
   rrd=$rrdpath/rrdtool
   WD=pwd

   function docmd()
   {
   [ “$1” = “” ] && return 0
   echo “$1\n.”
   eval “$1″
   return $?
   }
   #docmd

   function createRRD() {
   local name=$1
   local interval=$2
   local cs=$3
   local hb=$((interval*5)); #heartbeat
   local cmd=”$rrd create $WD/${name}.rrd -s${interval} DS:waits:DERIVE:$hb:0:U DS:mswaited:DERIVE:$hb:0:U RRA:AVERAGE:0.5:1:1440 RRA:AVERAGE:0.5:30:336 RRA:AVERAGE:0.5:120:372 RRA:AVERAGE:0.5:720:730 RRA:MIN:0.5:1:1440 RRA:MIN:0.5:30:336 RRA:MIN:0.5:120:372 RRA:MIN:0.5:720:730 RRA:MAX:0.5:1:1440 RRA:MAX:0.5:30:336 RRA:MAX:0.5:120:372 RRA:MAX:0.5:720:730 RRA:LAST:0.5:1:1440”
   docmd “$cmd” || return $?
   return 0;
   }
   #createRRD

   #/* take the snapshot frequency from dba_hist_wr_control to create the RDD with correct heartbeat value */
   sql=”select extract(hour from snap_interval)*3600 + extract(minute from snap_interval)*60 as SEED from DBA_HIST_WR_CONTROL”
   interval= ${mysql} ${connstr}<$outfile<100000
and s.instance_number=i.instance_number
and s.instance_number=g.instance_number)
order by event_name, END_INTERVAL_TIME;
END

   i=0
   oldevent=""
   cmd=""
   last="0"
   cat $outfile | while IFS=, read end_interval_time event_name wait_class total_waits ms; do
   if [ "$oldevent" != "$event_name" ]; then
   # //NEW EVENT DETECTED: WILL START A NEW UPDATE CMD
   [ ! "$i" -eq "0" -a "$cmd" != "" ] && docmd "$cmd"
   echo event=$event_name
   cleanName=${event_name// /_}
   cleanName=${cleanName//:/_}
   # if there is no rrd for this event, I create a new one
   [ ! -f "${WD}/${cleanName}.rrd" ] && createRRD "$cleanName" "$interval" ""
   # * I initialize a new update command. This string act as a buffer: I append many values to be updated so I'll update many values in a single command line:
   # * less forks of rrdtool and less file opens: the whole update process has an enormous improvement.

   precmd="$rrd update $WD/${cleanName}.rrd"
last=$rrd info $WD/${cleanName}.rrd| grep last_update | awk ‘{print \$NF}’`
   last=${last:-0}
   printf “%s – %s – last: %d\n” “$event_name” “$cleanName” “$last”
   i=0;
   cmd=”$precmd”
   oldevent=”$event_name”
   fi
   time=$end_interval_time
   # //print “time: “.$time.” last: “.$last.”\n”;
   if [ “$time” -gt “$last” ]; then
   cmd=”$cmd ${time}:${total_waits}:${ms}”
   i=$(($i+1))
   fi
   if [ “$i” -gt “40” ]; then
   # // when I reach 40 values per commandline I force
   # // the update: next loop will reinitialize a new commandline.
   docmd “$cmd”
   cmd=”$precmd”
   i=0
   fi
   done
   [ “$i” -eq “0” ] || docmd $cmd

   [ -f “$outfile” ] && rm “$outfile”
   echo done in $SECONDS

   Reply ↓

This site uses Akismet to reduce spam. Learn how your comment data is processed.