* AWRReport.Sql (c) Tim Onions 2007
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!

whenever sqlerror exit sql.sqlcode;
-- Get the current database/instance information - this will be used
-- later in the report along with bid, eid to lookup snapshots
set echo off heading on underline on autoprint off ver off feedback off;
column inst_num  heading "Inst Num"  new_value inst_num  format A3;
column inst_name heading "Instance"  new_value inst_name format a12;
column db_name   heading "DB Name"   new_value db_name   format a12;
column dbid      heading "DB Id"     new_value dbid      format A40;
column report_name heading "Report name" new_value report_name format A40 word;
def dir='&&1';

prompt Current Instance
prompt ~~~~~~~~~~~~~~~~
SELECT  TO_CHAR(d.dbid)  dbid
      ,            db_name
      ,TO_CHAR(i.instance_number) inst_num
      ,i.instance_name   inst_name
      ,'&&dir.awr_'||LOWER(instance_name)||'_'||TO_CHAR(sysdate-1,'YYYYMMDD') report_name
FROM	 v$database d
      ,v$instance i ;

-- option settings
define NO_OPTIONS   = 0;
define ENABLE_ADDM  = 8;
def report_type='text';
set linesize 80

--  Set up the databasebinds
variable rpt_options number;
variable dbid       number;
variable inst_num   number;
  :dbid      :=  '&dbid';
  :inst_num  :=  '&inst_num';
  :rpt_options := &ENABLE_ADDM;
  IF :dbid IS NULL OR :inst_num IS NULL OR :rpt_options IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001,'Cannot create AWR report as unable to set database data');
  END IF; --:dbid IS NULL

-- Get the begin and end snap ids for "yesterday".
column begin_snap new_value begin_snap format a10 heading "Start Snap"
column end_snap   new_value end_snap format a10 heading "End Snap"
column begin_snapdat format A20 heading "Start Snap Date"
column end_snapdat format A20 heading "End Snap Date"
column instart_fmt format A22 heading "Instance start date/time"
column lvl format 999 heading "Snap level"
SELECT to_char(s1.startup_time,'dd Mon "at" HH24:mi:ss')  instart_fmt
     , di.instance_name                                  inst_name
     , di.db_name                                        db_name
     , to_char(s2.snap_id)                                         begin_snap
     , to_char(s1.snap_id)                                         end_snap
     , to_char(s2.end_interval_time,'dd Mon YYYY HH24:mi') begin_snapdat
     , to_char(s1.end_interval_time,'dd Mon YYYY HH24:mi') end_snapdat
     , s1.snap_level                                      lvl
FROM	 dba_hist_snapshot s1,dba_hist_snapshot s2
     , dba_hist_database_instance di
WHERE s1.dbid              = :dbid 
   and s2.dbid=:dbid
   and di.dbid             = :dbid
   and s1.instance_number   = :inst_num
   and di.instance_number  = :inst_num
   and di.dbid             = s1.dbid
   and di.dbid             = s2.dbid
   and di.instance_number  = s1.instance_number
   and di.instance_number  = s2.instance_number
   and di.startup_time     = s1.startup_time
   and di.startup_time     = s2.startup_time
   and s1.snap_id=(SELECT MAX(snap_id) FROM dba_hist_snapshot WHERE dbid=:dbid AND instance_number=:inst_num AND end_interval_time<=TRUNC(sysdate)+5/1440)
   and s2.snap_id=(SELECT MIN(snap_id) FROM dba_hist_snapshot WHERE dbid=:dbid AND instance_number=:inst_num AND end_interval_time>s1.begin_interval_time-.99)
 order by db_name, instance_name, s1.snap_id

--  Set up the snapshot binds
variable bid        number;
variable eid        number;
  :bid       :=  '&&begin_snap';
  :eid       :=  '&&end_snap';
    RAISE_APPLICATION_ERROR(-20002,'Cannot create AWR report as unable to locate necessary snapshot data');
  END IF; --:dbid IS NULL

-- Spool to the file name with the the current date in it created earlier
SPOOL &report_name;

-- call the table function to generate the report
SELECT output FROM table(dbms_workload_repository.awr_report_text( :dbid,
                                                            :bid, :eid,
                                                            :rpt_options ));


/* SUMMARY - Automatically create an AWR report for the previous day (i.e. without the need to enter any database or snapshot details
 * DETAIL - The AWR reports in Oracle 10g would seem to be aimed at replacing STATSPACK - just so long as you have paid for the necessary optional
 *          "pack". When using STATSPACK it was found to be useful to generate one each night for the previous 24 hour period. This required a degree
 *          of hacking with the supplied SQL report which was awkward with each new release as it meant having to go back and re-do the changes for the
 *          latest version of the STATSPACK report.
 *          With AWR the reporting is done via a single package call - dbms_workload_repository.awr_report_text, if you want the main report in 
 *          text (as opposed to HTML) format. Therefore, all that needs doing is working out the required input parameters: database ID; instance number;
 *          begin snapshot id; end snapshot id. All of these can be easily located from tables such as V$DATABASE, V$INSTANCE, dba_hist_snapshot and
 *          dba_hist_database_instance. This script does just that, it requires a single input parameter of the directory to place the output. The output
 *          is formated with instance name and the date included - eg awr_racdb1_20070317.lst.
 *          The report as supplied is for the instance that the session logs on to. In a RAC environment you can get the report for any instance (ie. node) of the
 *          RAC cluster by adding a second input parameter for the instance number and altering the first SQL to:
 *		SELECT TO_CHAR(d.dbid)   dbid
 *			,            db_name
 *		FROM v$database d;
 *		and getting the instance name from dba_hist_database_instance.