Oracle : Shell Script to gather stats for Stale tables
Dear Readers, In this article, we wrote an automation job for Shell Script to gather stats for Stale tables. Note : 1) Please test the Shell script in Non-Prod before implementing it in Production. Script for gather stats for Stale tables
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
#!/bin/bash function PreChks { ORACLE_HOME=/u01/app/oracle/product/12.1.0.2 if [[ ! -e $ORACLE_HOME ]]; then echo " " echo "Error ::: Given ORACLE_HOME :: $ORACLE_HOME does not exist!!! " echo " " exit; fi ORACLE_SID=mvmrp1 export PATH=$ORACLE_HOME/bin:$PATH:. export ORACLE_BASE ORACLE_HOME PATH ORACLE_SID LOGFILE=/tmp/main.log echo "Task Start at `date` " > $LOGFILE export MAIL_RECIPIENT_INFO='ktexperts@admin.com' } function get_objects { echo "#CHECK FOR DATABASE and Tables STATUS##">>"$LOGFILE" DB_OPEN_MODE=`sqlplus -s / as sysdba <<EOF set pagesize 0 feedback off verify off heading off echo off select open_mode from v\\$database; EOF` echo "$DB_OPEN_MODE">>"$LOGFILE" if [[ $DB_OPEN_MODE = "READ WRITE" ]] then echo "Fetch Stale_Stats Data" sqlplus -s / as sysdba <<EOF > /tmp/a.log set pagesize 0 feedback off verify off heading off echo off set linesize 300 select table_name, owner from dba_tab_statistics where stale_stats='YES' and owner in ('MVM1') order by table_name; EOF else ##DATABASE IS NOT OPEN## mail_subject="Database $INSTANCE is not open hence Gather Stats cant not Start...." echo " " echo "Database $INSTANCE is not open hence Gather Stats cant not Start...." echo " " mailx -s "${mail_subject}" ${MAIL_RECIPIENT_INFO}<<EOF " Database $INSTANCE is not open hence Gather Stats cant not Start...." EOF exit 0 fi cat /tmp/a.log >> /tmp/main.log } function gather_stats { cat /tmp/a.log | while read p do tbl=`echo $p | awk '{ print $1 }'` owner=`echo $p | awk '{ print $2 }'` SQL=`echo "Exec dbms_stats.gather_table_stats('$owner','$tbl',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);"` echo $SQL >> /tmp/main.log sqlplus -s / as sysdba <<EOF >> /tmp/main.log Exec dbms_stats.gather_table_stats('$owner','$tbl',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4); EOF done } PreChks get_objects gather_stats get_objects mail_subject="mvmrp1 Stale Stats Report" cat "$LOGFILE" | mailx -s "${mail_subject}" ${MAIL_RECIPIENT_INFO} |
Output :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
################## Task Start at Mon Jun 14 21:00:01 PDT 2021 #CHECK FOR DATABASE and Tables STATUS## READ WRITE READ WRITE #CHECK FOR DATABASE and Tables STATUS## READ WRITE READ WRITE EMP MVM1 DEPT MVM1 SALGRADE MVM1 Exec dbms_stats.gather_table_stats('MVM1','EMP',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4); PL/SQL procedure successfully completed. Exec dbms_stats.gather_table_stats('MVM1','DEPT',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4); PL/SQL procedure successfully completed. Exec dbms_stats.gather_table_stats('MVM1','SALGRADE',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4); PL/SQL procedure successfully completed. #CHECK FOR DATABASE and Tables STATUS## READ WRITE |
