Gather stats from crontab - once a month

crontab (run at 22:05 on second of each month)

5 22 2 * * bash -l /software/oracle/oracle/scripts/shared_pool_latch/gather_stats.sh

gather_stats.sh

#!/bin/bash

ORA_VER=1120
ORACLE_SID=igt
ORACLE_BASE=/software/oracle
ORA_NLS33=/software/oracle/112/ocommon/nls/admin/data
ORACLE_HOME=/software/oracle/112
WORK_DIR=/software/oracle/oracle/scripts/shared_pool_latch
LOG_FILE=${WORK_DIR}/gather_stats.log
RUN_DATE=`date "+%Y%m%d"_"%H%M"`
cd $WORK_DIR
echo "===============================" >> ${LOG_FILE}
echo "Start Gather Stats at ${RUN_DATE}" >> ${LOG_FILE}
sqlplus / as sysdba @gather_stats.sql
RUN_DATE=`date "+%Y%m%d"_"%H%M"`
echo "Finished Gather Stats at ${RUN_DATE}" >> ${LOG_FILE}
echo "===============================" >> ${LOG_FILE}

gather_stats.sql

spool gather_stats.log append

PROMPT DBMS_STATS.GATHER_SCHEMA_STATS ('SYS')

BEGIN

 DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

END;

/

PROMPT DBMS_STATS.GATHER_DICTIONARY_STATS

BEGIN

 DBMS_STATS.GATHER_DICTIONARY_STATS;

END;

/

PROMPT DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

BEGIN

 DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

END;

/

PROMPT DBMS_STATS.gather_schema_stats('XXX_YYYYY_AAAAA')

BEGIN

 DBMS_STATS.gather_schema_stats('XXX_YYYYY_AAAAA');

END;

/

PROMPT DBMS_STATS.gather_schema_stats('XXX_YYYYY_BBBBB')

BEGIN

 DBMS_STATS.gather_schema_stats('XXX_YYYYY_BBBBB');

END;

/

PROMPT DBMS_STATS.gather_schema_stats('XXX_YYYYY_CCCCC')

BEGIN

 DBMS_STATS.gather_schema_stats('XXX_YYYYY_CCCCC');

END;

/

EXIT;