DBA Script

<- Scripts

#!/bin/bash
# Purpose:      Update CBO Stats
# Version:      2018.10.01
# Dependencies: inc_system.sh, inc_sqlplus.sh
# Comments:     - usrMaxRunTime cannot be used on days usrAutoStatsDay used.
#               - To disable native Oracle stats job from scheduler run: 
#                 exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL, window_name => NULL);

######################################
# Preamble: 2018.10.01               #
######################################
set -a; DIR_SCRIPTS="/u01/app/scripts"; source $DIR_SCRIPTS/inc_system.sh
sFullName=$(basename "$0"); me=${sFullName%.*}
sSLog="$DIR_LOGS/$me.sess.log"; sHLog="$DIR_LOGS/$me.hist.log"; > $sSLog
if [[ $MAINT_WINDOW -eq 1 ]]; then printf "Maintenance Window Detected - Exiting\n"; exit; fi

# User Vars
usrAutoStatsDOW=1; # DOW (1-7) to run Oracle's automatic statistics gathering job. Disabled=0
usrDictStatsDOM=1; # DOM (1-31) to update stats on Oracle's Dictionary.

# Comma delimited list of Schema names. Ex: "SCHEMA1,SCHEMA2"
#usrSchemaLst="ACTIVITYMANAGER,DDC"
usrSchemaLst="\
ACTIVITYMANAGER,AWBATCH,BI,CCM,COREUSER,DDC,DMPDATA,DMPDATA2,MAPPER,MARKETVISION,\
OSIBANK,OSIEXTN,OSIOAS,PAYRSWAPSAVE,PORTAL,SAF,SAFUSER,SCCU,SYSMETA,WFINST";
usrMaxRunTime=180; # No new action if Elapse greater than this value (in minutes).


######################################
# Init Script Actions\Functions\Vars #
######################################
IFS=","
source $DIR_LIB/inc_sqlplus.sh

# Process CmdLine Parameters
if ! [[ -z "$1" ]]; then export ORACLE_SID="$1"; fi
if [[ -z "$ORACLE_SID" ]]; then echo "Invalid ORACLE_SID"; exit; fi;
rm $DIR_TMP/$sBaseName.???.t?? > /dev/null 2>&1; # Sweep tmp files from incompleted script.
nDOM=$(date +%d)
nDOW=$(date +%u)

######################################
# Start                              #
######################################
clear; log "$sSLog" "$sFullName Started"; linesep
SysVars_show
printf "ORACLE_SID:      $ORACLE_SID\n"
printf "usrAutoStatsDOW: $usrAutoStatsDOW($nDOW)\n"
printf "usrDictStatsDOM: $usrDictStatsDOM($nDOM)\n"
linesep
printf "usrMaxRunTime:   $usrMaxRunTime\n"
printf "usrSchemaLst:    $usrSchemaLst\n"
linesep "="

# Update Dictionary Stats?
if [[ $nDOM -eq $usrDictStatsDOM ]]; then
   log "$sSLog" "Updating Dictionary Stats"
   sSQL="EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;"
   xsql "$sSQL"
fi

# User Stats Update Section
if [[ $nDOW -eq $usrAutoStatsDOW ]]; then
   log "$sSLog" "Type: Oracle's Native Stats Job"
   sSQL="exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;"
   log "$sSLog" "$sSQL"
   xsql "$sSQL"
else
   log "$sSLog" "Type: User Confired Schema"
   for s in $usrSchemaLst; do
      log "$sSLog" "[Schema: $s]"

      # DBMS_STATS.GATHER_SCHEMA_STATS
      sSQL="EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => '$s',\
      cascade => DBMS_STATS.AUTO_CASCADE,degree => DBMS_STATS.AUTO_DEGREE,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);"
      log "$sSLog" "$sSQL"
      xsql "$sSQL"

      # MaxRunTime Check
      if [[ $(elapse) -ge $usrMaxRunTime ]]; then
         log "$sSLog" "Breaking: usrMaxRunTime exceeded ($usrMaxRunTime)"
         break
      fi
   done
fi

######################################
# End                                #
######################################
log "$sSLog" "$sFullName Ended [Elapse Time: $(elapse)]";
echo $(linesep "=") >> $sHLog; cat $sSLog >> $sHLog;
tail -32768 $sHLog > $sHLog.tmp; mv $sHLog.tmp $sHLog