OracleDB‎ > ‎Tuning‎ > ‎

Reporting Scripts

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.

Comments