Automatically emailing tuning advice

posted May 2, 2012, 6:28 AM by Sachchida Ojha   [ updated May 2, 2012, 5:24 PM ]
In any UNIX platform, it's very easy to automate the e-mailing of output from A SQL script. All you need to do is encapsulate the SQL in a shell script, and then use cron utility to automatically generate and email the output.

Sample shell script that generates and sends automatic SQL tuning advice. we will use Oracle's DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK procedure to generate tuning advise.

#!bin/bash
 if [ $# -ne 1 ]; then
 echo "Usage: $0 SID"
 exit 1
fi
# source oracle OS variables
BOX=`uname -a |awk '{print $2}'`
outfile=$HOME/bin/log/sqladvice.txt
#
sqlplus -s <<EOF
dbaref/dbaref
SPO $outfile

set linesize 80 pagesize 0 long 100000
select DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK from dual;
EOF
cat $outfile | mailx -s "SQL Advice :$1 $BOX" snojha@gmail.com
exit 0


You can put this script in CRON to run this script on a daily basis

###############################################################################
# Min Hour DayOfMonth Month Wkday Command # Comment
# (0-59) (0-23) (1-31) (1-12) (0-6) (0=Sunday)
#
###############################################################################
00 11 * * * $ORACLE_HOME/DBA/bin/scripts/sqladvice.bash MJBDB 1>$ORACLE_HOME/DBA/bin/log/sqladvice.log 2>&1

###############################################################################







SQL> select client_name,status, consumer_group
  2  from dba_autotask_client
  3  order by client_name;

CLIENT_NAME                             STATUS
---------------------------------------------------------------- --------
CONSUMER_GROUP
------------------------------
auto optimizer stats collection                  ENABLED
ORA$AUTOTASK_STATS_GROUP

auto space advisor                         ENABLED
ORA$AUTOTASK_SPACE_GROUP

sql tuning advisor                         ENABLED
ORA$AUTOTASK_SQL_GROUP



SQL>


Comments