collect.sh


Modify variable "SYS" to connect as SYS to the database you want to monitor.
Then run

  collect.sh &

It will create a director called "LOG" in your home directory.
Within that directory it will create a directory with the data and time
Go to that directory and run

    utlstat.sh

to create a statspack type report

collect.sh :

#!/bin/ksh  
# Author       : Kyle Hailey
# Created      : Jan 1998
#      updated : Jan 30 1998  - added define files
#                Feb 20 1998  - added possibility of using seconds elapsed for
#                               sample point id
#
# Funtion: collects most of the important stats for tuning an Oracle database 
#          such as 
#             v$sqlarea
#             v$session_wait
#             v$version
#             v$systat
#             v$system_event (x$kslei)
#             v$latch
#             v$rollstat
#             v$filestat
#             v$rowcache
#             v$librarycache
#             v$session - current sql hash value and address
#
# This should be run a sys, but if you want to run as system you can set
# up the following objects:
#
#    create view x_$kslei as select * from x$kslei;
#    create public synonym x$kslei for  x_$kslei;
#    grant select on x_$kslei to system;
#    
#    create view x_$ksmss as select * from x$ksmss;
#    create public synonym x$ksmss for  x_$ksmss;
#    grant select on x_$ksmss to system;
#    
#    create view x_$ksqst as select * from x$ksqst;
#    create public synonym x$ksqst for  x_$ksqst;
#    grant select on x_$ksqst to system;
#    
#    create view x_$ksled as select * from x$ksled;
#    create public synonym x$ksled for  x_$ksled;
#    grant select on x_$ksled to system;
#
# usage [ poll time ] [ run time ] [ sqlarea time ]
#
#    where 
#           SLOW_RATE - interval between collecting stats
#           FAST_RATE - interval between checking for exit file
#                       or certain stats if desired
#           run     time - total period over which stats are polled/collected
#           sqlarea time - dump shared pool at this interval
#
# Note         : as always, unsupported, if you see problems
#                you can always email me, but the best thing
#                in my opinion, is to try to improve on this
#                and then send the changes - I'll
#                consoldate within reason the changes

export ORACLE_HOME=${ORACLE_HOME:-/home/oracle}
export ORACLE_SID=${ORACLE_SID:-AF}
export SYS=${SYS:-sys/change_on_install}
export INTERNAL=${INTERNAL:-internal/oracle}
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin

 
    FORCE=1 # if it takes longer than SLOW_RATE between loops such that
            # the sampling falls behind, FORCE=1 causes FAST_SAMPLE to be
            # run at least once per SLOW_SAMLPE otherwise it is not run at all
            # normally FAST_SAMPLE is run as many times as possible until
            # SLOW_RATE time is elapsed
            # ie if 
            #   SLOW_RATE = 60
            #   FAST_RATE = 5
            # fast_rate sample is run ~11/12 times  for every slow_rate

    MACHINE_STATS=0 # collect machine stats ?(vmstat)
    HOST=dlserv6    # use "local" for local host, otherwise rsh to $HOST 

    FAST_RATE=15              # rate at which to run FAST_SAMPLE work
                             # as well  as check for exit file
    SLOW_RATE=${1:-300}       # rate at which to run SLOW_SAMPLE work
    RUN_TIME=${2:-43200}     # total run time, 12 hours default 43200
    DMP_TIME=${3:-900}       # dump shared pool time

    DEBUG=${DEBUG:-0}

    #SYS="internal"
    #SYS=${SYS:-"sys/change_on_install"}
    #SYS=${SYS:-"sys as sysdba"}
    #SYS=${SYS:-"internal"}
    SYS=${SYS:-"sys/change_on_install@AF"}

    MON_LOG=${MON_LOG:-$HOME/LOG} 

  # 
  # * - def function for this choice 
  # 
  #   utlstat stuff:
  # * sevt	- v$system_event  (x$kslei)
  # * systat	- v$sysstat
  # * latch	- v$latch
  # * libc	- v$library_cache
  # * fstat	- v$filestat
  # * waitstat	- v$waitstat (buffer busy waits)
  # * rowc	- v$row_cache
  # * rbs	- v$rollstat
  #
  #   lock stuff:
  #   enqueue	- v$wait_event = v$session - get sql_address
  #   xlock	- v$ksqst 
  #   vlock	- v$lock
  #   slock	- v$session where waiting for an object 
  #
  #   other stuff:
  # * sga	- v$ksmss 
  #   waits	- v$wait_event
  #   sql	- v$sqlarea - not totally implemented, see functions
  #		  sql_stat and sql_addr 
  #
  #   OPS stuff:
  #   pings	- v$lock_activity
  #   event 29700 needs to be set for time stats for the convers:
  #   dlm_convert_remote -  v$dlm_convert_remote 
  #   dlm_convert_local	 -  v$dlm_convert_local
  # * dlmlatch 	 - v$dlm_latch
  #   class_ping - v$class_ping
  #   file_ping - v$file_ping
  #

    UTLSTAT="libc sevt systat latch waitstat fstat rbs rowc" 
    UTLSTAT_SHORT="sevt systat libc fstat" 
    OPS="pings dlm_convert_remote dlm_convert_local dlmlatch  class_ping file_ping"

    COMMON=""
    COMMON="sga $UTLSTAT $OPS"
    COMMON="sga $UTLSTAT "

  # list of types of data to collect
    SLOW_SAMPLE="$COMMON"

  # its seems to much work to keep track of which choices need
  # definition files and which don't
  # how about just trying to get define files for everything
  # and ignoring the errors when the def functions don't exist
  #
  # list of defines files to create once at the beginning
    DEFS="$COMMON version init"

  # list of stats to sample every FAST_RATE time instead of loop time
    FAST_SAMPLE="waits"

    DMP_SQL=0       # dump the shared pool 1=yes, 0=no  (every DMP_TIME)
  # if DMP_SQL=1 then:
  # SQL=sql_stat    # dump addr and hash AND STATS of current stmts - slow
    SQL=sql_addr    # dump just the addr and hash of current stmts - faster
 

  MACHINE=`uname -a | awk '{print $1}'`
  case $MACHINE  in
    AIX)
            MKNOD=/etc/mknod
            MKNOD=/usr/sbin/mknod
            ;;
    DYNIX/ptx)
            MKNOD=mknod
            ;;
    SunOS)
            MKNOD=/etc/mknod
            ;;
    HP-UX)
            MKNOD=mknod
            ;;
    OSF1)
            MKNOD=/sbin/mknod
            ;;
    *)
            MKNOD=mknod
            ;;
  esac

  # create OUPUT directory
    if [ ! -f "$MON_LOG" ]; then
       mkdir $MON_LOG > /dev/null 2>&1
    fi

    if test x$1 = xend ; then
       if [ -f $MON_LOG/*\.end ]; then
           rm $MON_LOG/*\.end
       fi
       if [ -f $MON_LOG/*/*\.end ]; then
           rm $MON_LOG/*/*\.end
       fi
       exit
    fi

  # setup OUTPUT file name template
    CURR_DATE=`date "+%d%m_%H%M%S"`  
    MON_BATCH_TYPE=""        
    MON_NODE="`hostname | sed -e 's/\..*//'`"
    MON_LOG=${MON_LOG}/$CURR_DATE
    mkdir $MON_LOG > /dev/null 2>&1
    PRE=${MON_LOG}/${MON_NODE}.${ORACLE_SID:-$TWO_TASK}_${MON_BATCH_TYPE}_${CURR_DATE}_
    SUF=.dat
    TMP="$MON_LOG/"
    OUTPUT=${TMP}connect.out
    OUTPUTLOG=${TMP}connect.log
    echo "SYS=$SYS"  >  $OUTPUTLOG

  # exit if removed
    EXIT=${PRE}collect.end
    touch $EXIT
  # echo "$SLOW_RATE" > ${PRE}utlstat.par

  # printout setup
    for i in 1; do
    echo
    echo
    echo "SYS=$SYS" 
    echo "RUN_TIME=$RUN_TIME" 
    echo "SLOW_RATE=$SLOW_RATE" 
    echo "SLOW_SAMPLE=$SLOW_SAMPLE" 
    echo "FAST_RATE=$FAST_RATE" 
    echo "FAST_SAMPLE=$FAST_SAMPLE" 
    echo "DMP_SQL=$DMP_SQL" 
    echo "HOST=$HOST" 
    echo "DEBUG=$DEBUG" 
    echo
    done > $OUTPUTLOG
    cat $OUTPUTLOG

  # Choices

  # create a UNIX named pipe
  # in order to avoid disconnects when attachin sqlplus to the named pipe
  # create an empty file and "tail -f" this empty file into the pipe
  # this will prevent the pipe from closing on the sqlplus session
  # otherwise the sqlplus session would exit after every cat to the pipe
  # had finished

  # setup sqlplus connection readin off a pipe
    OPEN=${MON_LOG}/collect.open
    PIPE=${MON_LOG}/collect.pipe
    rm $OPEN $PIPE > /dev/null 2>&1
    touch  $OPEN
    cmd="$MKNOD $PIPE p"
    eval $cmd
    tail -f $OPEN >> $PIPE &
    OPENID="$!"

    if [ $DEBUG -lt 2 ]; then
      sqlplus -s "$SYS" < $PIPE > /dev/null &
    else
      sqlplus    "$SYS" < $PIPE &
    fi
    SQLID="$!"

  # collect machine statistics
    if [ MACHINE_STATS == 1 ]; then
      if test $HOST = "local"; then
         vmstat $SLOW_RATE $RUN_TIME > ${PRE}vmstat$SUF &
         VMSTATID=$!
      else 
         rsh $HOST -e "vmstat $SLOW_RATE $RUN_TIME" > ${PRE}vmstat$SUF &
         VMSTATID=$!
      fi
    fi

  # setup exit/cleanup stuff
    CMD="( date; \
           rm $PIPE $OPEN ; \
           kill -9 $SQLID $OPENID $VMSTATID; \
           rm $PIPE $OPEN $EXIT \
         ) > /dev/null 2>&1; exit"
    trap "$CMD" 0 3 5 9 15 

    if [ ! -p $PIPE ]; then
       echo "error creating named pipe "
       echo "command was:"
       echo "             $cmd"
       eval $CMD
       exit
    fi

#   /******************************/
#   *                             *
#   * BEGIN FUNCTION DEFINITIONS  *
#   *                             *
#   /******************************/
#

function debug {
if [ $DEBUG -ge 1 ]; then
   #   echo "   ** beg debug **"
   var=$*
   nvar=$#
   if test x"$1" = xvar; then
     shift
     let nvar=nvar-1
     while (( $nvar > 0 ))
     do
        eval val='$'{$1} 1>&2
        echo "       :$1:$val:"  1>&2
        shift
        let nvar=nvar-1
     done
   else
     while (( $nvar > 0 ))
     do
        echo "       :$1:"  1>&2
        shift
        let nvar=nvar-1
     done
   fi
   #   echo "   ** end debug **"
fi
}                         

function check_exit {
        if [  ! -f $EXIT ]; then
           echo "exit file removed, exiting at `date`"
           echo $CMD
           eval $CMD
           exit
        fi
}

function sqloutput  {
    cat << EOF >> $PIPE &
       set pagesize 0
       set feedback off
       spool $OUTPUT
       select 1 from dual;
       spool off
EOF
}

function testconnect {
     rm $OUTPUT 2> /dev/null
     if [ $CONNECTED -eq 0 ]; then
        limit=10
     else
        limit=600
     fi
     debug "before sqloutput"
     sqloutput
     debug "after sqloutput"
     count=0
     found=1
     debug "before while"
     while [ $count -lt $limit -a $found -eq 1 ]; do
        if [ -f $OUTPUT ]; then
          grep '^ *1'  $OUTPUT > /dev/null  2>&1
          found=$?
        else 
          debug  "sql output file: $OUTPUT, not found"
        fi
          debug "found $found"
          debug "loop#  $LOOPS_DONE  count $count limit $limit "
          if [ $CONNECTED -eq 0 ]; then
             echo "Trying to connect"
          fi
          let TO_SLEEP=TO_SLEEP-count
          sleep $count
          count=`expr $count + 1`
          check_exit
     done
     debug "after while"
     if [ $count -ge $limit ]; then
       echo "timeout waiting connection to sqlplus"
       echo "output from sqlplus: "
       if [ -f $OUTPUT ]; then
          cat $OUTPUT 
       else
          echo "sqlplus output file: $OUTPUT, not found"
          echo "check user name and password for sqlplus"
          echo "try 'export DEBUG=1' and rerun"
       fi
       echo "loop# " $LOOPS_DONE " count $count limit $limit " >> $OUTPUTLOG
       eval $CMD
       exit
     fi
     echo "loop# " $LOOPS_DONE " count $count limit $limit " >> $OUTPUTLOG
}

function _timer  {
     cat << EOF
     select 
                  to_char(sysdate,'SSSSS')  
              + ( to_char(sysdate,'J') - &START_DAY )*86400  timer
          from dual;
     spool  ${TMP}elapsed.tmp
     select &timer - &curr_time from dual;
     spool off
EOF
}
  
#NEXTVAL=0
function _date  {
     # let NEXTVAL=$NEXTVAL+1
     # we add the # of days elapsed * 86400 secs/day,
     # which is normally zero, incase the day changes
     # in which case we don't want to start over at zero secs
     # but at 86400+elapsed seconds
     # to_char(sysdate,'SSSSS') - seconds elapsed today
     # to_char(sysdate,'J')     - # of days in julian date
     # start_day  - days in julian date on start day
     cat << EOF
     column curr_time    new_value curr_time
     set echo on
     select 
         (to_char(sysdate,'SSSSS')+
        (to_char(sysdate,'J')-&start_day)*86400)  curr_time
       from dual;
     spool  ${TMP}date.tmp
     REM how can we bind variable this?
     select 
              &curr_time               ||','||
              to_char(sysdate,'DDMMYY,HH24:MI:SS') 
          from dual;
     spool off
     set echo off

     REM      $NEXTVAL                 ||','||
     REM      &curr_time - &start_time ||','||
EOF
}
  
function sevt  {
     cat << EOF
     spool  ${TMP}sevt.tmp
     select  
  	indx    ||','||
  	ksleswts||','||
  	kslestim 
      from 
  	x\$kslei ;
     spool off
EOF
}
  
function cachechain  {
     cat << EOF
     spool  ${TMP}cachechain.tmp
     select  
  	S.SID         ||','|| 
  	S.SQL_ADDRESS 
     from 
  	v\$session_wait w,v\$session s
     where
         w.event='latch free' and
         w.p2=(select latch# from v$latchname where name='cache buffers chains') and
         w.sid=s.sid;
     spool off
EOF
}

# I'm getting strange results from this
# the sql queries are in the BAAN benchmark
# for the most part select statements
# why would a select statement 
# I take it this comes from the fact that there is no read
# consistency when joining these views
function enqueue  {
     cat << EOF
     spool  ${TMP}enqueue.tmp
     select  
  	S.SID         ||','|| 
  	S.SQL_ADDRESS ||','||
  	ROW_WAIT_OBJ# ||','||
  	W.P1 
     from 
  	v\$session_wait w,v\$session s
     where
         w.event='enqueue' and
         w.sid=s.sid;
     spool off
EOF
}

function dlm_convert_remote  {
     cat << EOF
     spool  ${TMP}dlm_convert_remote.tmp
     select  
  	INST_ID              ||','|| 
  	CONVERT_TYPE         ||','|| 
  	AVERAGE_CONVERT_TIME ||','||
        CONVERT_COUNT
     from 
  	v\$dlm_convert_remote;
     spool off
EOF
}

function dlm_convert_local  {
     cat << EOF
     spool  ${TMP}dlm_convert_local.tmp
     select  
  	INST_ID              ||','|| 
  	CONVERT_TYPE         ||','|| 
  	AVERAGE_CONVERT_TIME ||','||
        CONVERT_COUNT
     from 
  	v\$dlm_convert_local;
     spool off
EOF
}

function xlock  {
     cat << EOF
     spool  ${TMP}xlock.tmp
     select  
  	ksqsttyp       ||','|| 
  	ksqstget      ||','|| 
  	ksqstwat    
     from 
  	x\$ksqst
     where
         ksqstget > 0;
     spool off
EOF
}

function vlock  {
     cat << EOF
     spool  ${TMP}vlock.tmp
     select  
  	SID       ||','|| 
  	TYPE      ||','|| 
  	ID1       ||','|| 
  	ID2       ||','|| 
  	LMODE     ||','|| 
  	REQUEST   
     from 
  	v\$lock
     where
         SID > 4;
     spool off
EOF
}

function slock  {
     cat << EOF
     spool  ${TMP}slock.tmp
     select  
  	SID           ||','|| 
  	SQL_ADDRESS   ||','|| 
  	ROW_WAIT_OBJ# 
     from 
  	v\$session
     where
        ROW_WAIT_OBJ# > 0;
     spool off
EOF
}

function sga  {
     cat << EOF
     spool  ${TMP}sga.tmp
     select  
  	INDX      ||','|| 
  	KSMSSLEN 
     from 
  	x\$ksmss;
     spool off
EOF
}

function systat  {
     cat << EOF
     spool  ${TMP}systat.tmp
     select  
  	STATISTIC#||','|| 
  	VALUE 
     from 
  	v\$sysstat;
     spool off
EOF
}
  
function pings  {
     cat << EOF
     spool  ${TMP}pings.tmp
     select  
  	FROM_VAL  ||','|| 
  	TO_VAL    ||','|| 
  	COUNTER 
     from 
  	v\$lock_activity;
     spool off
EOF
}

function file_ping  {
     cat << EOF
     spool  ${TMP}file_ping.tmp
     select  
           FILE_NUMBER           ||','||
           FREQUENCY             ||','||
           X_2_NULL              ||','||
           X_2_NULL_FORCED_WRITE ||','||
           X_2_NULL_FORCED_STALE ||','||
           X_2_S                 ||','||
           X_2_S_FORCED_WRITE    ||','||
           X_2_SSX               ||','||
           X_2_SSX_FORCED_WRITE  ||','||
           S_2_NULL              ||','||
           S_2_NULL_FORCED_STALE ||','||
           SS_2_NULL             ||','||
           SS_2_RLS              ||','||
           WRB                   ||','||
           WRB_FORCED_WRITE      ||','||
           RBR                   ||','||
           RBR_FORCED_WRITE      ||','||
           RBR_FORCED_STALE      ||','||
           CBR                   ||','||
           CBR_FORCED_WRITE      ||','||
           NULL_2_X              ||','||
           S_2_X                 ||','||
           SSX_2_X               ||','||
           NULL_2_S              ||','||
           NULL_2_SS             ||','||
           OP_2_SS               
     from 
  	v\$file_ping;
     spool off
EOF
}
function class_ping  {
     cat << EOF
     spool  ${TMP}class_ping.tmp
     select  
          replace(nvl(class,'null'),' ','_')      ||','||
          X_2_NULL               ||','||
          X_2_NULL_FORCED_WRITE  ||','||
          X_2_NULL_FORCED_STALE  ||','||
          X_2_S                  ||','||
          X_2_S_FORCED_WRITE     ||','||
          X_2_SSX                ||','||
          X_2_SSX_FORCED_WRITE   ||','||
          S_2_NULL               ||','||
          S_2_NULL_FORCED_STALE  ||','||
          SS_2_NULL              ||','||
          SS_2_RLS               ||','||
          OP_2_SS                ||','||
          NULL_2_X               ||','||
          S_2_X                  ||','||
          SSX_2_X                ||','||
          NULL_2_S               ||','||
          NULL_2_SS                               
     from 
  	v\$class_ping;
     spool off
EOF
}

function waitstat  {
     cat << EOF
     spool  ${TMP}waitstat.tmp
     select  
        replace(class,' ','_')||','|| 
  	COUNT                 ||','|| 
  	TIME 
     from 
  	v\$waitstat;
     spool off
EOF
}

function dlmlatch  {
     cat << EOF
     spool  ${TMP}dlmlatch.tmp
     select  
  	LATCH# ||','|| 
  	LEVEL# ||','|| 
  	GETS   ||','|| 
  	MISSES ||','|| 
  	SLEEPS 
     from 
  	v\$dlm_latch;
     REM Immediate_gets, 
     REM Immediate_misses,Waiters_woken,Waits_holding_latch, 
     REM Spin_gets,Sleep1,Sleep2,Sleep3,Sleep4,Sleep5,Sleep6, 
     REM Sleep7,Sleep8,Sleep9,Sleep10 
     spool off
EOF
}
  
function latch  {
     cat << EOF
     spool  ${TMP}latch.tmp
     select  
  	LATCH# ||','|| 
  	LEVEL# ||','|| 
  	GETS   ||','|| 
  	MISSES ||','|| 
  	SLEEPS 
     from 
  	v\$latch;
     REM Immediate_gets, 
     REM Immediate_misses,Waiters_woken,Waits_holding_latch, 
     REM Spin_gets,Sleep1,Sleep2,Sleep3,Sleep4,Sleep5,Sleep6, 
     REM Sleep7,Sleep8,Sleep9,Sleep10 
     spool off
EOF
}
  
function rbs  {
     cat << EOF
     spool  ${TMP}rbs.tmp
     select 
  	usn            ||','||
  	extents        ||','||
  	rssize         ||','||
  	writes         ||','||
  	xacts          ||','||
  	gets           ||','||
  	waits          ||','||
  	nvl(optsize,0) ||','||
  	hwmsize        ||','||
  	shrinks        ||','||
  	wraps          ||','||
  	extends        ||','||
  	aveshrink      ||','||
  	aveactive      ||','|| 
          substr(''''||status||'''',1,18) 
     from 
  	v\$rollstat;
     spool off
EOF
}
  
function rowc  {
     cat << EOF
     spool  ${TMP}rowc.tmp
     select  
  	CACHE#              ||','||
  	nvl(SUBORDINATE#,-1)||','||
  	COUNT               ||','||
  	USAGE               ||','||
  	FIXED               ||','|| 
  	GETS                ||','|| 
  	GETMISSES           ||','|| 
  	SCANS               ||','|| 
  	SCANMISSES          ||','|| 
  	SCANCOMPLETES       ||','|| 
  	MODIFICATIONS       ||','||
  	FLUSHES             ||','||
  	DLM_REQUESTS        ||','||
  	DLM_CONFLICTS       ||','||
  	DLM_RELEASES
     from 
  	v\$rowcache;
     spool off
EOF
}
  
function libc  {
     cat << EOF
     spool  ${TMP}libc.tmp
     select
  	Gets                ||','||
  	Gethits             ||','||
  	round(Gethitratio,2)||','||
  	Pins                ||','||
  	Pinhits             ||','|| 
  	round(Pinhitratio,2)||','||
  	Reloads             ||','||
  	Invalidations       ||','|| 
  	substr(''''||Namespace||'''',1,18) 
    from 
  	v\$librarycache ;
     spool off
EOF
}
  
function fstat  {
     cat << EOF
     spool  ${TMP}fstat.tmp
     select 
  	file#     ||','||
  	phyrds    ||','||
  	Phywrts   ||','||
  	Phyblkrd  ||','||
  	Phyblkwrt ||','||
  	Readtim   ||','||
  	Writetim 
     from 
  	v\$filestat;
     spool off
EOF
}

function sql_stat {
     cat << EOF
           spool  ${TMP}sql.tmp
           select 
           sid               ||','||
           SQL_HASH_VALUE    ||','||
           SQL_ADDRESS       ||','||
           ROWS_PROCESSED    ||','||
           BUFFER_GETS       ||','||
           DISK_READS        ||','||
           EXECUTIONS        
           from 
           v\$sqlarea,
           v\$session
           where SQL_HASH_VALUE != 0
                 and SQL_HASH_VALUE=HASH_VALUE
                 and SQL_ADDRESS=ADDRESS
                 and PARSING_USER_ID != 0 ;
           spool off
EOF
}

function sql_addr {
     cat << EOF
           spool  ${TMP}sql.tmp
           select 
           sid               ||','||
           SQL_HASH_VALUE    ||','||
           SQL_ADDRESS      
           from 
           v\$session
           where SQL_HASH_VALUE != 0
                 and AUDSID != 0 ;
           spool off
EOF
}

function waits {
     cat << EOF
          spool  ${TMP}waits.tmp
          select  
          sid     ||','||
          event   ||','||
          p1      ||','||
          p2      ||','||
          p3
          from 
          v\$session_wait
          where event not like '%message%' and
                event not like '%Null%' and
                event not like '%timer%';
          spool off
EOF
}
  
SQL_DUMP_TIME=0           

function dump_shared_pool {
     if [ $SQL_DUMP_TIME -lt  $CURRENT -a $DMP_SQL -eq 1 ]; then
         SQL_DUMP_TIME=`expr $DMP_TIME + $CURRENT`
         sqldmp.sh 1 sql$CURRENT > /dev/null &
     fi
}
  
function tight_loop {
   #
   # sleep every FAST_RATE until a total of SLOW_RATE sleeped
   # every FAST_RATE check EXIT file exists
   # if EXIT file has been deleted, then exit
   # 
     check_exit
     SLEPTED=$FAST_RATE
     debug var SLEPTED FAST_RATE
     if [ $FORCE -eq 1 ]; then
        for i in $FAST_SAMPLE; do
           ${i} >> $PIPE
        done
        testconnect
        for i in  $FAST_SAMPLE; do
          cat ${TMP}${i}.tmp | sed -e "s/^/$date,/" >> ${PRE}${i}$SUF
        done
        check_exit
        let SLEPTED=SLEPTED+FAST_RATE
        debug var SLEPTED
        debug "sleeping $FAST_RATE"
        sleep $FAST_RATE
     fi
     while [ $TO_SLEEP -gt $SLEPTED ]; do
        for i in $FAST_SAMPLE; do
           ${i} >> $PIPE
        done
        testconnect
        for i in  $FAST_SAMPLE; do
          cat ${TMP}${i}.tmp | sed -e "s/^/$date,/" >> ${PRE}${i}$SUF
        done
        check_exit
        let SLEPTED=SLEPTED+FAST_RATE
        debug var SLEPTED 
        debug "sleeping $FAST_RATE"
        sleep $FAST_RATE
     done
     let TO_SLEEP=TO_SLEEP-SLEPTED+FAST_RATE
     debug var TO_SLEEP 
     debug "sleeping $TO_SLEEP"
     if [ $TO_SLEEP -gt 0 ]; then
       sleep $TO_SLEEP
     fi
}

function sevt_def {
  cat << EOF
  spool ${PRE}sevt.def
  select indx||' '||replace(kslednam,' ','_')  from x\$ksled;
  spool off
EOF
}

function sga_def {
  cat << EOF
  spool ${PRE}sga.def
  select INDX||' '||replace(KSMSSNAM,' ','_') from x\$ksmss ;
  spool off
EOF
}

function version_def {
  cat << EOF
  spool ${PRE}version.def
  select * from v\$version;
  spool off
EOF
}

function systat_def {
  cat << EOF
  spool ${PRE}systat.def
  select statistic#||' '||replace(name,' ','_') from v\$statname;
  spool off
EOF
}

function rbs_def {
  cat << EOF
  spool ${PRE}rbs.def
  select usn||' '||replace(name,' ','_') from v\$rollname;
  spool off
EOF
}

function fstat_def {
  cat << EOF
  spool ${PRE}fstat.def
  select file_id||' '||replace(file_name,' ','_')||' '||tablespace_name  
       from 
           dba_data_files;
  spool off
EOF
}

function dlmlatch_def  {
  cat << EOF
  spool ${PRE}dlmlatch.def
  select latch#||' '||replace(name,' ','_') from v\$dlm_latch;
  spool off
EOF
}

function latch_def {
  cat << EOF
  spool ${PRE}latch.def
  select latch#||' '||replace(name,' ','_') from v\$latchname;
  spool off
EOF
}

# use a dat file instead of a def file so that from utlstat
# it will be easier to choose to include it in the output
# or not
function init_def {
  cat << EOF
  spool ${PRE}init.dat
  select NAME||'='||
         value from v\$parameter;
  spool off
EOF
}

function set_event {
  cat << EOF
  $EVENT
EOF
}

function rowc_def {
  cat << EOF
  spool ${PRE}rowc.def
  select CACHE#||nvl(SUBORDINATE#,'')||' '||
         replace(PARAMETER,' ','_') from v\$rowcache;
  spool off
EOF
}

function libc_def {
  cat << EOF
  spool ${PRE}libc.def
  select '1 ''BODY'''            from dual;
  select '2 ''CLUSTER'''         from dual;
  select '3 ''INDEX'''           from dual;
  select '4 ''OBJECT'''          from dual;
  select '5 ''PIPE'''            from dual;
  select '6 ''SQLAREA'''         from dual;
  select '7 ''TABLE/PROCEDURE''' from dual;
  select '8 ''TRIGGER'''         from dual;
  spool off
EOF
}

function setup_sql {
  cat << EOF
  set echo on
  set pause off
  set linesize 2500
  set verify off
  set feedback off
  set heading off
  set pagesize 0
  set linesize 100
  set trims on
  set trim on
  column start_day    new_value start_day 
  select  to_char(sysdate,'J')     start_day  from dual;
  column pt           new_value pt
  column seq          new_value seq
  column curr_time    new_value curr_time
  column elapsed      new_value elapsed     
  column timer        new_value timer       
  set echo off
EOF
}
#  alter session set sql_trace=false;
#  REM drop sequence orastat;
#  REM create sequence orastat;


#   /******************************/
#   *                             *
#   *   END FUNCTION DEFINITIONS  *
#   *                             *
#   /******************************/



#   /******************************/
#   *                             *
#   *      BEGIN PROGRAM          *
#   *                             *
#   /******************************/


  LOOPS_DONE=0
  CURRENT=0
  TO_SLEEP=$SLOW_RATE

  CONNECTED=0
  setup_sql >> $PIPE
  testconnect
  echo "Connected, starting collect at `date`"
  CONNECTED=1
  setup_sql >> $PIPE

# GET DEFINES 
  for i in $DEFS; do
          debug $i
          debug ${i}_def
          if test x$i = "xdlm_convert_remote"; then
             EVENT="alter system set events='29700 trace name context forever';"
             ( set_event >> $PIPE ) > /dev/null 2>&1
          fi
          if test x$i = "xdlm_convert_local"; then
             EVENT="alter system set events='29700 trace name context forever';"
             ( set_event >> $PIPE ) > /dev/null 2>&1
          fi
        # echo ${i}_def
        # try getting defines for everyting and ignore errors 
        # ${i}_def >> $PIPE 
        echo "defs $i"
          ( ${i}_def >> $PIPE ) > /dev/null 2>&1
        # apparently we can overload the PIPE on LINUX and so we need to check
        # that every command is processed before going onto the next
        # sort of a pain
          testconnect
  done
  echo  "finished defs"

if [ $DEBUG -eq 1 ]; then
    echo "finished defines"
    echo "hit return to continue"
    read prompt
fi

echo "starting stats collecting "
# BEGIN COLLECT LOOP
  while [ $CURRENT -lt $RUN_TIME -a -f $EXIT ]; do
       CURRENT=`expr $CURRENT + $SLOW_RATE `
       LOOPS_DONE=`expr $LOOPS_DONE + 1 `
     # dump shared pool if DMP_SQL=1    
       dump_shared_pool 
     # collect stats for each type in SLOW_SAMPLE
       _date > $PIPE
       testconnect
       for i in $SLOW_SAMPLE; do
          ${i} >> $PIPE
          testconnect 
       done
       testconnect 
       date=`tail -1 ${TMP}date.tmp | sed -e 's/,.*//'`
       for i in $SLOW_SAMPLE; do
          cat ${TMP}${i}.tmp | sed -e "s/^/$date,/" >> ${PRE}${i}$SUF
        # apparently we can overload the PIPE on LINUX and so we need to check
        # that every command is processed before going onto the next
        # sort of a pain
          testconnect 
       done
       testconnect
       _timer > $PIPE
       testconnect
       elapsed=`tail -1 ${TMP}elapsed.tmp | sed -e 's/ //g'`
       let TO_SLEEP=$TO_SLEEP-$elapsed 
       cat ${TMP}date.tmp | sed -e "s/$/,$elapsed/" >> ${PRE}date$SUF
       if [ $TO_SLEEP -lt 0 ]; then
          TO_SLEEP=0
       fi
       tight_loop
       TO_SLEEP=$SLOW_RATE
  done
# END COLLECT LOOP

# CLEANUP
   echo "run time expired, exiting at `date`"
   echo $CMD
   eval $CMD 




Comments