Bash script to gather statistics

The following shell script will gather table stats, and optionally, various system-wide stats. Copy the contents into a file and make it executable as the oracle owner account.

By default it runs with OS authentication as SYSDBA, you can alter that behavior by setting an env variable before running the script. I do not recommend adding passwords into this script for obvious security reasons.

You can show the usage by just passing an invalid command line parameter, for example ./GatherStats.sh -?

# Gather table stats – table list filtered by last time and (optionally) size (based on the number of records)
#
# File $Id: GatherStats.sh  $
# Modified $Author: dheltzel $
# Date $Date:  $
# Revision $Revision: $

CRED=${CRED:-/}

usage() {
echo “Usage: $0 [-t days] [-r num_recs] [-d database name] [-s] [-f filter_string]”
echo ”  -t days – only select tables that have not had stats gathered in at least this many days (default 7)”
echo ”  -r num_recs – only select tables with fewer than X thousand records (default is unlimited)”
echo ”  -d database name – defaults to $ORACLE_SID”
echo ”  -s – also gather system stats (takes a long time)”
echo ”  -f filter_string – arbitrary filter for the select query (ex. \”AND s.owner=’DBADMIN’\”)”
exit 1
}

# Handle parameters
while getopts “:d:t:r:sf:” opt; do
case $opt in
d)
TWO_TASK=$OPTARG
DB_NAME=$OPTARG
;;
t)
AGE_DAYS=$OPTARG
;;
r)
NUM_RECS=”AND num_rows < $OPTARG * 1000″
;;
s)
SYS_STATS=Y
;;
f)
OPT_FILTER=$OPTARG
;;
\?)
echo “Invalid option: -$OPTARG” >&2
usage
;;
: )
echo “Option -$OPTARG requires an argument.” >&2
usage
;;
esac
done

DB_NAME=${DB_NAME:-${ORACLE_SID}}
AGE_DAYS=${AGE_DAYS:-7}
REPORT_NAME=GatherStats_${DB_NAME}-`date “+%y%m%d%H%M”`.sql
LOG_NAME=GatherStats_${DB_NAME}-`date “+%y%m%d%H%M”`.log
#echo “${AGE_DAYS}”
#echo “${NUM_RECS}”
#echo “${REPORT_NAME}”
#echo “${OPT_FILTER}”
echo “Gathering stats for tables in ${DB_NAME} with stats older that ${AGE_DAYS} days . . .”

(sqlplus -s ${CRED} as sysdba <<!
set pages 0
set feed off
set head off
set lines 130
prompt set sqlprompt “_CONNECT_IDENTIFIER> “;
prompt set feed off;
prompt set echo on;
–prompt spool ${LOG_NAME}
SELECT ‘exec DBMS_STATS.GATHER_TABLE_STATS(”’||t.owner||”’,”’||t.table_name||”’);’ FROM dba_tables t
JOIN dba_tab_statistics s ON (s.OWNER = t.OWNER AND s.TABLE_NAME = t.TABLE_NAME)
WHERE t.owner NOT LIKE ‘%SYS%’ AND t.owner NOT IN (‘XDB’) AND t.temporary = ‘N’ AND t.secondary = ‘N’ AND t.segment_created = ‘YES’
AND s.stattype_locked IS NULL AND (t.last_analyzed < SYSDATE –  ${AGE_DAYS} OR t.last_analyzed IS NULL) AND t.table_name NOT LIKE ‘SYS%’ ${NUM_RECS} ${OPT_FILTER}
ORDER BY t.num_rows;
exit
!
) > ${REPORT_NAME}

# Add commands to gather system stats if requested
if [ -n “${SYS_STATS}” ] ; then
echo “exec DBMS_STATS.GATHER_SYSTEM_STATS” >>${REPORT_NAME}
echo “exec DBMS_STATS.GATHER_DICTIONARY_STATS” >>${REPORT_NAME}
echo “exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS” >>${REPORT_NAME}
fi
echo “exit” >>${REPORT_NAME}

sqlplus ${CRED} as sysdba @${REPORT_NAME}