Enterprise Manager Grid Control repository database optimization job

As part of the weekly maintenance tasks described in the best practice paper on Oracle Enterprise Manager Grid Control. I created a cron job which performs the basic optimization on the repository database. This job will force the statistics to be updates on the most active tables of EMGC and its indexes.
Offcourse in a low target monitoring environment you won’t see a big performance boost here , but i have seen it do wonders on monitoring system which gather information for 500 targets and up.

#!/bin/sh
#############################################################
# Auteur  : John Paul van Helvoort
# Owner   : Yenlo B.V.
# Datum   : 02-02-2010
# file    : week_maintenance.sh
#
#############################################################

PATH=/usr/local/bin:/usr/bin:/etc:/usr/sbin:/usr/ucb:/opt/oracle/bin:/usr/bin/X11:/sbin:$PATH          ; export PATH

# ===========    VARIABLES   ================

SID=`cat /etc/oratab|grep -v "^#"|grep -v "^oms10g"|grep -v "^agent10g"|cut -f1 -d: -s`
HOST=`uname -n`
MAILADRES="support@example.nl"

# ===========    FUNCTIONS   ================

# Set Environment DB

set_environment() {
  ORACLE_SID=$SID; export ORACLE_SID
  ORAENV_ASK="NO"; export ORAENV_ASK
  . /usr/local/bin/oraenv
}

maintenance(){

  set_environment

  # Query name from database
  SQL=`sqlplus /nolog <<EOF
       conn / as sysdba
       SET ECHO OFF PAGES 2000 FEED OFF HEAD OFF
       set timing on

       SPOOL /home/oracle/scripts/week_maintenance.log

       DELETE FROM SYSMAN.MGMT_TARGETS_DELETE;
       COMMIT;

       exec dbms_stats.unlock_table_stats('SYSMAN','MGMT_METRICS_RAW');
       exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_RAW', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);

       exec dbms_stats.unlock_table_stats('SYSMAN','MGMT_METRICS_1HOUR');
       exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_1HOUR', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);

       exec dbms_stats.unlock_table_stats('SYSMAN','MGMT_METRICS_1DAY');
       exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_1DAY', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);

      SPOOL OFF
exit
EOF
`
}

mail_yenlo(){
   mail -s "OS, EMGC, Week Maintenance Report by $HOST" $MAILADRES < /home/oracle/scripts/week_maintenance.log
}

#############################
# Main
#############################

maintenance
mail_yenlo

I have scheduled it like this in cron to be performed every Sunday at 8′oclock.


# * * * * * command to be executed
# - - - - -
# | | | | |
# | | | | +----- day of week (0 - 6) (Sunday=0)
# | | | +------- month (1 - 12)
# | | +--------- day of month (1 - 31)
# | +----------- hour (0 - 23)
# +------------- min (0 - 59)

# Maintenance EMGC WEEKLY
00 20 * * 0 /home/oracle/scripts/week_maintenance.sh >> /dev/null 2>&1


Offcourse you are free to change the frequency if needed.