#!/bin/ksh
## Copyright 2008, The Board of Trustees of the University of Illinois (UI). All Rights Reserved. 
## UI MAKES NO REPRESENTATIONS ABOUT THE SUITABILITY OF THIS SOFTWARE FOR ANY PURPOSE. 
## IT IS PROVIDED "AS IS" WITHOUT EXPRESS OR IMPLIED WARRANTY. 
## THE UI SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY THE USERS OF THIS SOFTWARE. 
## ---------------------------------------------------------------------
## Script to calculate table STATS manually in Banner/Non-Banner databases
##
##	By default the stats gathered by this script use SIZE 1 (no histograms) unless
##		otherwise specified in in-script parameters (see below).
##
##    This script will: 
##		1) clean up old files
##		2) flush database monitoring info from memory
##		3) gather dictionary stats
##		4) produce scripts to gather stats for tables with subpartitions 
##		5) produce scripts by schema onwer to gather stats for non-partitioned tables
##		6) execute the scripts in parallel, looping every minute until completed
##		7) check output logs for errors
##		8) email completion status
##
##	Usage:  runStats.ksh <all> <dbname>
##
##    Parameters within the script that need to be set are:
##		emailList	- individual or group email account where status report to be sent
##		bucketSize	- used in SIZE option of DBMS_STATS command for histogram depth,
##				  recommend 254 which is maximum size
##		statsIUD	- Number of Inserts, Updates, and Deletes on a table that will trigger new stats to be generated
##		statsPer	- Percentage of change on a table that will trigger new stats to be generated
##		listOfTables- List of tables to receive histograms on ALL columns
##		listOfHistograms - List of tables with specific histograms to be preserved with SIZE REPEAT 
##
## Audit Trail of modifications:
## 05/21/2006 RG  Base Script
## 07/28/2006 RG  Modified to include a special set of tables 
##                that need histograms
## 07/31/2006 RG  Added dictionary_stats for STALE dictionary objects
## 08/03/2006 RG  Fixed a problem related to tables with a "$" sign
## 09/01/2006 sac Replaced AUTO bucket sizes with 254
## 03/28/2007 RG  Fixed a minor bug with the bucketSize
## 03/03/2008 sjh added more comments, added code to re-gather histograms
##                using REPEAT, removed hard-coded table names
## 06/26/2008 sdr Added optional "all" first parameter to run stats on all
##                tables (vs modified tables).  Changed scrDir and logDir
##                pathnames to include the SID, and create them if they
##                don't exist.  Check for version 9 or 10 of Oracle (using
##                ORACLE_HOME pathname, but there's probably a better way)
##                to determine which dbms_stats routine to call to gather
##                dictionary stats.  Also gather stats on tables that have
##                been truncated.  Include the table name and date/time in
##                the log file to see what table is currently being worked
##                on (tail -f *stats.log).
## ---------------------------------------------------------------------
if [ "$1" = "all" ]; then
export runAllStats=yes
shift
else
export runAllStats=no
fi
if [ $# -eq 0 ]
then
echo "Usage : $0 <dbname>"
exit
fi

## ------------------------------------------
##  CHANGE THESE TO MEET YOUR INSTITUTIONS NEEDS
## ------------------------------------------
export emailList="srea@maristream.org"                     ## Group email address to send notifications to
export bucketSize="254"                              ## Histogram bucket size 
export statsIUD=250000                               ## Calculate stats if sum change (IUD) > statsIUD
export statsPer=5                                    ## Calculate stats if %change of (IUD) > statsPer

## ------------------------------------------
##  CHANGE THESE TO MEET YOUR INSTITUTIONS NEEDS
## List of tables that need histogram info
## Separate each table with a space
## For example "SSBSECT SPRIDEN SPBPERS"
## ------------------------------------------
export listOfTables="SSBSECT SORWDSP GLBEXTR SCBCRSE STVSUBJ FTVORGN TBRAPPL"

## ------------------------------------------
##  CHANGE THESE TO MEET YOUR INSTITUTIONS NEEDS
## List of tables that have histograms on specific columns
## Script will use REPEAT parameter on these to gather the same histograms
##    previously specified by a manual stats run
## Separate each table with a space
## For example "FABINVH FRRBDET"
## ------------------------------------------
## export listOfHistograms="FABINVH FRRBDET"
export listOfHistograms=""

export ORACLE_SID=$1
export ORACLE_HOME=`grep "^${ORACLE_SID}:" /etc/oratab | cut -f2 -d":"`
export scrDir="/home/oracle/runStats.$ORACLE_SID/scr"
export logDir="/home/oracle/runStats.$ORACLE_SID/log"
export sqlFile="$logDir/$ORACLE_SID.stats.sql"
export logFile="$logDir/$ORACLE_SID.`date '+%m%d%Y.%H%M`.stats.log"
export tmpFile1="$logDir/$ORACLE_SID.stats.tmp1"
export tmpFile2="$logDir/$ORACLE_SID.stats.tmp2"
export tmpFile3="$logDir/$ORACLE_SID.stats.tmp3"
export tmpFile4="$logDir/$ORACLE_SID.stats.tmp4"
export tmpFile5="$logDir/$ORACLE_SID.stats.tmp5"
if [ -n "`env ORACLE_HOME | grep v9`" ]; then
export oraver=v9
else
export oraver=v10
fi
env | egrep '(ORACLE_SID|ORACLE_HOME|srcDir|logDir|runAllStats|oraver|bucketSize|logFile)'
mkdir -p $scrDir 2>/dev/null
mkdir -p $logDir 2>/dev/null

## ------------------------------------------
##  Clean up old files first
## ------------------------------------------
rm $logFile $tmpFile1 $tmpFile2 $tmpFile3 $tmpFile4 >/dev/null 2>&1
rm $logDir/runStats*.done $logDir/runStats*.log $scrDir/runStats*.ksh >/dev/null 2>&1
find $logDir -name "*.log" -mtime +90 -exec rm {} \; > /dev/null 2>&1

## ------------------------------------------
##  Function to run SQL scripts
## ------------------------------------------
function runSQL {
  myTmpFile=$1
  $ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<! > $myTmpFile 2>&1
  set pagesize 0 linesize 240 head off feed off echo on
  @$sqlFile
!
}

## ------------------------------------------
##  Function to build ksh scripts
## ------------------------------------------
function crKsh {
  myTmpFile=$1
  fileName="runStats_table_$2"
  myKshFile="$scrDir/$fileName.ksh"
  myLogFile="$logDir/$fileName.log"
  myDneFile="$logDir/$fileName.done"
  echo "#!/bin/ksh" > $myKshFile
  echo "export ORACLE_SID=$ORACLE_SID" >> $myKshFile
  echo "export ORACLE_HOME=$ORACLE_HOME" >> $myKshFile
  echo "export bucketSize=$bucketSize" >> $myKshFile
  echo "$ORACLE_HOME/bin/sqlplus -s \"/ as sysdba\" <<! > $myLogFile" >> $myKshFile
  echo "set timing on" >> $myKshFile
  cat $myTmpFile >> $myKshFile
  echo "quit\n!\n" >> $myKshFile
  echo "touch $myDneFile" >> $myKshFile
  chmod 755 $myKshFile
  cnt=`wc -l $myTmpFile|awk '{print $1}'`
  echo "`date '+%m-%d-%Y %H:%M:%S'`  $myKshFile   $cnt entries" >> $logFile
}

## ------------------------------------------
##  Begin stats processing here
## ------------------------------------------
echo "--------------------------------------------------------------------------------" > $logFile
echo "Manual Collection of STATS Job" >> $logFile
echo "Database : $ORACLE_SID" >> $logFile
echo "Run date : `date '+%m-%d-%y %H:%M'`" >> $logFile
echo "--------------------------------------------------------------------------------" >> $logFile

## ------------------------------------------
## Flush Database Monitoring Info first
## ------------------------------------------
echo "\nFlushing database monitoring info from memory" >> $logFile
echo "exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();" > $sqlFile
stime=`date '+%m-%d-%Y %H:%M:%S'`
runSQL $tmpFile1
etime=`date '+%m-%d-%Y %H:%M:%S'`
echo "Flush Database Monitoring :: Start time: $stime     End time: $etime" >> $logFile 
echo "--------------------------------------------------------------------------------" >> $logFile
if [ -s $tmpFile1 ]; then
   cat $tmpFile1 >> $logFile
fi

## ------------------------------------------
## Run DICTIONARY stats - gather_dictionary_stats not in 9i
## ------------------------------------------
echo "\nRunning Dictionary Stats now" >> $logFile
if [ "$runAllStats" = "yes" ]; then
export gather='GATHER'
else
export gather='GATHER STALE'
fi
if [ "$oraver" = "v10" ]; then
echo "exec dbms_stats.gather_dictionary_stats(estimate_percent=>NULL, method_opt=>'FOR ALL COLUMNS SIZE $bucketSize', degree=>DBMS_STATS.DEFAULT_DEGREE, options=>'$gather', cascade=>TRUE, granularity=>'ALL');" > $sqlFile
else
echo "exec dbms_stats.gather_schema_stats('SYS', estimate_percent=>NULL, method_opt=>'FOR ALL COLUMNS SIZE $bucketSize', degree=>DBMS_STATS.DEFAULT_DEGREE, options=>'$gather', cascade=>TRUE, granularity=>'ALL');" > $sqlFile
fi
stime=`date '+%m-%d-%Y %H:%M:%S'`
runSQL $tmpFile1
etime=`date '+%m-%d-%Y %H:%M:%S'`
echo "Dictionary STATS :: Start time: $stime     End time: $etime" >> $logFile 
echo "--------------------------------------------------------------------------------" >> $logFile
if [ -s $tmpFile1 ]; then
   cat $tmpFile1 >> $logFile
fi

## ------------------------------------------
## Run STATS for Tables with Subpartitions
## ------------------------------------------
echo "\nGenerating scripts for PARTITIONED tables" >> $logFile
echo "--------------------------------------------------------------------------------" >> $logFile
echo "select distinct table_owner,table_name from dba_tab_subpartitions;" > $sqlFile 2>&1
runSQL $tmpFile1
if [ -s $tmpFile1 ]; then                       ## Go inside the block only if Subpartitions exist
   cat $tmpFile1 |
   while true
   do
      read own tab
      if [ $? -ne 0 ]; then
        break
      fi
#      echo "Table is $own.$tab"
      if [ "$runAllStats" = "yes" ]; then
        echo "select '!echo ''\\\n'||b.table_owner||'.'||replace(b.table_name,'\$','\\\$')||'.'||b.partition_name||' `date`'''||chr(10)||'exec dbms_stats.gather_table_stats('''||'$own'||''','''||'$tab'||''','''||b.subpartition_name||''',cascade=>TRUE,degree=>DBMS_STATS.DEFAULT_DEGREE, method_opt=> '''||'FOR ALL COLUMNS SIZE $bucketSize'''||',estimate_percent=>NULL,Granularity=>'''||'SUBPARTITION'''||');'
        from dba_tab_subpartitions b 
        where b.table_owner='$own' and b.table_name='$tab';" > $sqlFile
      else
        echo "select '!echo ''\\\n'||b.table_owner||'.'||replace(b.table_name,'\$','\\\$')||'.'||b.partition_name||' `date`'''||chr(10)||'exec dbms_stats.gather_table_stats('''||'$own'||''','''||'$tab'||''','''||a.subpartition_name||''',cascade=>TRUE,degree=>DBMS_STATS.DEFAULT_DEGREE, method_opt=> '''||'FOR ALL COLUMNS SIZE $bucketSize'''||',estimate_percent=>NULL,Granularity=>'''||'SUBPARTITION'''||');'
        from dba_tab_modifications a, dba_tab_subpartitions b 
        where a.table_owner='$own' and a.table_name='$tab'
        and a.table_owner=b.table_owner and a.table_name=b.table_name
        and a.partition_name=b.partition_name
        and a.subpartition_name=b.subpartition_name and b.num_rows > 0
        and (((a.INSERTS+a.UPDATES+a.DELETES)*100/b.num_rows > $statsPer)
          or  ((a.INSERTS+a.UPDATES+a.DELETES) > $statsIUD)
          or  (a.TRUNCATED = 'YES'));" > $sqlFile
      fi
      runSQL $tmpFile2
      if [ -s $tmpFile2 ]; then
           crKsh $tmpFile2 ${own}_${tab}
      fi
   done
else
  echo "No partitioned tables need fresh stats" >> $logFile
fi

## ------------------------------------------
## Parse lists of tables to receive histograms into individual lines
## ------------------------------------------
echo $listOfTables > $tmpFile3
perl -pi -e 's/ /\n/g' $tmpFile3 >/dev/null 2>&1
echo $listOfHistograms > $tmpFile5
perl -pi -e 's/ /\n/g' $tmpFile5 >/dev/null 2>&1


## ------------------------------------------
## Generate scripts for the remaining tables by schema owner
## (i.e. non-partitioned tables)
## ------------------------------------------
echo "\nGenerating scripts for non-PARTITIONED tables" >> $logFile
echo "--------------------------------------------------------------------------------" >> $logFile
echo "select distinct owner from dba_tables where owner not in ('SYS','SYSTEM');" > $sqlFile 2>&1
runSQL $tmpFile1
if [ -s $tmpFile1 ]; then
   cat $tmpFile1 |
   while true
   do
      read own
      if [ $? -ne 0 ]; then
        break
      fi
      if [ "$runAllStats" = "yes" ]; then
        echo "select '!echo ''\\\n'||a.owner||'.'||replace(a.table_name,'\$','\\\$')||' `date`'''||chr(10)||'exec dbms_stats.gather_table_stats('''||a.owner||''','''||'\"'||replace(a.table_name,'\$','\\$')||'\"'||''','||'cascade=>TRUE,degree=>DBMS_STATS.DEFAULT_DEGREE, method_opt=> '''||'FOR ALL COLUMNS SIZE 1'''||',estimate_percent=>NULL,Granularity=>'''||'ALL'''||');'
        from dba_tables a 
        where a.owner='$own'
        order by a.owner,a.table_name;" > $sqlFile
      else
        echo "select '!echo ''\\\n'||a.owner||'.'||replace(a.table_name,'\$','\\\$')||' `date`'''||chr(10)||'exec dbms_stats.gather_table_stats('''||a.owner||''','''||'\"'||replace(a.table_name,'\$','\\$')||'\"'||''','||'cascade=>TRUE,degree=>DBMS_STATS.DEFAULT_DEGREE, method_opt=> '''||'FOR ALL COLUMNS SIZE 1'''||',estimate_percent=>NULL,Granularity=>'''||'ALL'''||');'
        from dba_tables a, dba_tab_modifications b 
        where a.owner='$own'
        and a.owner=b.table_owner
          and a.owner||a.table_name not in (select distinct c.table_owner||c.table_name from dba_tab_subpartitions c)
        and a.table_name = b.table_name 
        and (((b.INSERTS+b.UPDATES+b.DELETES)*100/decode(a.num_rows,0,1,a.num_rows) > $statsPer)
          or  ((b.INSERTS+b.UPDATES+b.DELETES) > $statsIUD)
          or  (b.TRUNCATED = 'YES'))
        order by (b.INSERTS+b.UPDATES+b.DELETES)*100/decode(a.num_rows,0,1,a.num_rows) desc;" > $sqlFile
      fi
      runSQL $tmpFile2
      if [ -s $tmpFile2 ]; then
           grep -vif $tmpFile3 $tmpFile2 | grep -vif $tmpFile5 > $tmpFile4
           grep -if $tmpFile3 $tmpFile2|sed 's/SIZE 1/SIZE $bucketSize/g' >> $tmpFile4
           grep -if $tmpFile5 $tmpFile2|sed 's/SIZE 1/SIZE REPEAT/g' >> $tmpFile4
           crKsh $tmpFile4 ${own}
      fi
   done
fi

## ------------------------------------------
## Now execute all the scripts in parallel
## ------------------------------------------
chmod 755 $scrDir/runStats*ksh >/dev/null 2>&1
tot=`ls -l $scrDir/runStats*ksh|wc -l`
echo "\n--------------------------------------------------------------------------------" >> $logFile
echo "Total Scripts = $tot. Now running them in background. " >> $logFile
echo "Parallel Execution Start  Time : `date '+%m-%d-%Y %H:%M:%S'`" >> $logFile
echo "--------------------------------------------------------------------------------" >> $logFile
set `ls -l $scrDir/runStats*ksh|sed 's#.* ##g'`
while [ $# -gt 0 ]
do
   echo "Executing : $1 .." >> $logFile
   $1 > /dev/null 2>&1 &
   shift
done
echo "--------------------------------------------------------------------------------" >> $logFile

## ------------------------------------------
## Loop till all background scripts complete
## ------------------------------------------
doneCount=0
whatsLeft=$tot

while [ $whatsLeft -gt 0 ]
do
   if [ -f $logDir/runStats*.done ]; then
       doneCount=`ls -1 $logDir/runStats*.done|wc -l|sed 's/  *//g'`
   else
       doneCount=0
   fi
   whatsLeft=$((tot-doneCount))
   DATE=`date +%m%d%Y.%H%M%S`
   echo "$DATE : $doneCount out of $tot scripts completed. Sleeping for 60 seconds.." >> $logFile
   sleep 60
done
echo "--------------------------------------------------------------------------------" >> $logFile
echo "Parallel Execution Finish Time : `date '+%m-%d-%Y %H:%M:%S'`" >> $logFile
echo "--------------------------------------------------------------------------------" >> $logFile

## ------------------------------------------
## Check for Errors
## Filtering any errors related to Locked
## Stats (ORA-20005 and ORA-06512)
## ------------------------------------------
echo "\nNow checking for Errors in the logfiles" >> $logFile
errorFlag="N"
set `ls -1 $logDir/runStats*.log|sed 's#.* ##g'`
while [ $# -gt 0 ]
do
   errors=`grep "ORA-" $1|grep -v ORA-20005|grep -v ORA-06512|grep -v ORA-25191`
   if [ "$errors" = "" ]; then
      echo "$1 had no errors" >> $logFile
   else
      echo "----------------------------------------------------" >> $logFile
      echo "$1 encountered errors" >> $logFile
      echo $errors >> $logFile
      echo "----------------------------------------------------" >> $logFile
      errorFlag="Y"
   fi
   shift
done

echo "--------------------------------------------------------------------------------" >> $logFile
echo "Stats Collection Job Completion  Time : `date '+%m-%d-%Y %H:%M:%S'`" >> $logFile

if [ "$errorFlag" = "Y" ]; then
    mailx -s "$ORACLE_SID : Stats collection job hit ERRORS" $emailList < $logFile
else
    mailx -s "$ORACLE_SID : Stats collection job completed SUCCESSFULLY " $emailList < $logFile
fi