Shell script to analyze selected tables (in APPS).

Problem:
When we run gather_ schema_states concurrent program. it takes a lot of time and resources and do not provide immediate relive to development instance (as it takes long time to run)

Solution:
I found that analyzing a small number of tables from different schema will improve performance. So I created below shell script. It reads table from a text file stored at the same location and analyze tables. This script can be scheduled in cron tab in unix.


Script:
(1)
#/usr/bin

#This script will read table name from a list file and analyze them.
#diffrent analyze commands can be selected.
#if a table is commentd in the script. it will not be considered.

SCRIPT="`basename $0`"
DT_FMT="%d%m%y-%H:%M"
DATE=`date +${DT_FMT}`
LOG_DIR=${APPLCSF}/${APPLLOG}
LOG_FILE=${LOG_DIR}/${SCRIPT}_${DATE}.log
ORACLE_SID=
TBL_LST=table_list.lst

DB_CHK() {

##function to check if oracle is up.

oracle_up=`ps -eaf | grep -w "ora_pmon_t201" |grep -v grep |wc -l`
oracle_num=`expr ${oracle_up}`

if [ $oracle_num -lt 0 ] ; then
echo "Oracle instance is NOT up. Please start Oracle and retry." | tee -a ${LOG_FILE}
exit 1
fi

echo "OK \t Oracle is up SID:${ORACLE_SID}" | tee -a ${LOG_FILE}

}

if [ ! -f ${TBL_LST} ]; then
echo "Failed: ${DATE} ${SCRIPT}: ${TBL_LST} file cannot be read" | tee -a ${LOG_FILE}
exit 1
else
if [ -f ANA_TB.sql ]; then
rm ANA_TB.sql ANA_TB_COL.sql ANA_TB_IND.sql ANA_TB_IND_COL.sql
touch ANA_TB.sql ANA_TB_COL.sql ANA_TB_IND.sql ANA_TB_IND_COL.sql
fi
fi

RUN_SQL() {
DB_CHK
typeset SCRIPT1=$1
sqlplus -s <apps/*****
@${SCRIPT1}
EOF
}
rm ANA_TB_COL.sql ANA_TB_IND.sql ANA_TB_IND_COL.sql ANA_TB.sql

for Tbl in `cat ./${TBL_LST} |grep -v \# | grep -v \*`
do
echo $Tbl

echo "PROMPT ANALYZE TABLE $Tbl COMPUTE STATISTICS;" >> ANA_TB.sql
echo "ANALYZE TABLE $Tbl COMPUTE STATISTICS;\n" >> ANA_TB.sql

echo "PROMPT ANALYZE TABLE $Tbl COMPUTE STATISTICS FOR ALL COLUMNS;" >> ANA_TB_COL.sql
echo "ANALYZE TABLE $Tbl COMPUTE STATISTICS FOR ALL COLUMNS;\n" >> ANA_TB_COL.sql

echo "PROMPT ANALYZE TABLE $Tbl COMPUTE STATISTICS FOR ALL INDEXES;" >> ANA_TB_IND.sql
echo "ANALYZE TABLE $Tbl COMPUTE STATISTICS FOR ALL INDEXES;\n" >> ANA_TB_IND.sql

echo "PROMPT ANALYZE TABLE $Tbl COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;" >> ANA_TB_IND_COL.sql
echo "ANALYZE TABLE $Tbl COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;\n" >> ANA_TB_IND_COL.sql

done

RUN_SQL ANA_TB.sql
#RUN_SQL ANA_TB_COL.sql
#RUN_SQL ANA_TB_IND.sql
#RUN_SQL ANA_TB_IND_COL.sql
#####################################################################

(2) LIST

#this is list of table
#if table name is preceded with # or * it will not be considered.

Emp
Dept
#emp_tmp