SQL Tuning Advisor - Expert Indexing in Oracle Database 11g - page 205

Automatically E-mailing Tuning Advice
On Linux/Unix systems, it's quite easy to automate the e-mailing of output from a SQL script. First,
encapsulate the SQL in a shell script, and then use a utility such as cron to automatically generate and e-
mail the output. Here's a sample shell script that generates and sends automatic SQL tuning advice:
#!/bin/bash
if [ $# -ne 1 ]; then
echo "Usage: $0 SID"
exit 1
fi
# source oracle OS variables
. /var/opt/oracle/oraset $1
#
BOX=`uname -a | awk '{print$2}'`
OUTFILE=$HOME/bin/log/sqladvice.txt
#
sqlplus -s <<EOF
mv_maint/foo
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" [e-mail]
exit 0
Here's the corresponding cron entry that runs the report on a daily basis:
#-----------------------------------------------------------------
# SQL Advice report from SQL auto tuning
16 11 * * * /orahome/oracle/bin/sqladvice.bsh DWREP
1>/orahome/oracle/bin/log/sqladvice.log 2>&1
#-----------------------------------------------------------------
(In this cron entry, the command was broken into two lines to fit on a page within this topic.)
Depending on the activity and load on your database, the report may contain no suggestions or may
provide a great deal of advice. You can adjust what is reported by the automatic SQL tuning job via
parameters passed to the REPORT_AUTO_TUNING_TASK function. The parameters for the
REPORT_AUTO_TUNING_TASK function are described in detail in Table 9-1. These parameters allow you a
great deal of flexibility in customizing the advice output.