addmreporter.sh
#!/bin/sh
. /home/oracle/.oracle_profile
snapshots=`sqlplus -s '/as sysdba' <<EOF
set echo off termout off feedback off pages 0 newpage 1 head off
select SNAP_ID from dba_hist_snapshot where to_char(begin_interval_time, 'YYYYMMDDHH24MI')=to_char(sysdate-1, 'YYYYMMDD')||'0000'
or to_char(begin_interval_time, 'YYYYMMDDHH24MI')=to_char(sysdate, 'YYYYMMDD')||'0000' order by SNAP_ID;
exit
EOF`
snap0=`echo $snapshots | awk '{print $1}'`;
snap1=`echo $snapshots | awk '{print $2}'`;
/home/oracle/addmreporter/report.expect $snap0 $snap1
mail -s "ADDM Report" spam@compute.info < rpt.txt
report.expect
#!/usr/bin/expect --
set snap0 [lrange $argv 0 0]
set snap1 [lrange $argv 1 1]
spawn /home/oracle/addmreporter/runaddm.sh
match_max 100000
expect -exact "Enter value for begin_snap: "
send -- "$snap0\r"
expect -exact "Enter value for end_snap: "
send -- "$snap1\r"
expect -exact "Enter value for report_name: "
send -- "rpt.txt\r"
expect -exact "SQL> "
send -- "exit\r"
runaddm.sh
#!/bin/sh
. /home/oracle/.oracle_profile
sqlplus '/as sysdba' @/opt/oracle/app/oracle/product/10.2.0/db_1/rdbms/admin/addmrpt.sql
tuner.pl
#!/usr/bin/perl
my @sqlids=`grep "RATIONALE: SQL statement with SQL_ID" rpt.txt | awk '{print \$6}' | sed 's/\"//g'`;
my @copyids = @sqlids;
foreach(@copyids)
{
chop($_);
my $sqlf = $_;
$_ = "'" . $_ . "'";
$taskname = $sqlf . "_tuning";
$tmptunfile = 'tmp/sqltune_' . $sqlf . ".sql";
open (sqltunef, '>' . $tmptunfile);
print sqltunef "DECLARE
my_task_name VARCHAR2 (30);
BEGIN
my_task_name := dbms_sqltune.create_tuning_task (
sql_id => $_,
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => '$taskname',
description => 'Tuning Task'
);
END;
/\n";
close(sqltunef);
@tunstat=`. ~oracle/.oracle_profile
sqlplus -s '/as sysdba'<<EOF
set echo off feedback off head off
SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '$taskname';
exit;
EOF`;
$tunstat = @tunstat[1];
chop($tunstat);
if ($tunstat ne "COMPLETED")
{
@runproc=`. ~oracle/.oracle_profile
sqlplus -s '/as sysdba'<<EOF
set pagesize 200;
\@$tmptunfile
begin
dbms_sqltune.execute_tuning_task (task_name => '$taskname');
end;
/
exit;
EOF`;
}
@tunsug=`. ~oracle/.oracle_profile
sqlplus -s '/as sysdba'<<EOF
set echo off feedback off head off
SET LONG 10000
SET LONGCHUNKSIZE 10000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('$taskname') from dual;
exit;
EOF`;
`rm $tmptunfile`;
open(tun, ">>tun.txt");
print tun @tunsug;
close(tun);
}
`mail -s "SQL Tuning Report" spam\@compute.info < tun.txt`;
crontab:
0 2 * * * /home/oracle/addmreporter/addmreporter.sh
15 2 * * * /home/oracle/addmreporter/tuner.pl
Note that a tmp directory needs to be created at the same level of these scripts.
