@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Generate ASH report for instances passed 
*  Parameters : 1 - From Time   (Optional) ( YYYY-MM-DD HH24:MI:SS format) - Default is (sysdate - 1/24)
*               2 - To Time     (Optional) ( YYYY-MM-DD HH24:MI:SS format) - Default is  sysdate
*               2 - INST_ID     (Optional) ( % for all or comma separated list of instance numbers).
*               4 - ServiceName (Optional) Default is NULL
*               5 - SID         (Optional) Default is NULL
*               6 - SQL_ID      (Optional) Default is NULL
*          
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  17-Feb-14  Vishal Gupta  Changed order of input parameters
*  28-Jan-15  Vishal Gupta  Added SID, SQL_ID, ServiceName input parameters
*  04-Oct-12  Vishal Gupta  Added default input parameter values
*  18-Feb-12  Vishal Gupta  Created
*/



/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE FROM_TIMESTAMP                   
UNDEFINE TO_TIMESTAMP                   
UNDEFINE INSTANCE_LIST
UNDEFINE SERVICE_NAME
UNDEFINE SID
UNDEFINE SQL_ID

DEFINE FROM_TIMESTAMP="&&1"
DEFINE TO_TIMESTAMP="&&2"
DEFINE INSTANCE_LIST="&&3"
DEFINE SERVICE_NAME="&&4"
DEFINE SID="&&5"
DEFINE SQL_ID="&&6"



COLUMN  _FROM_TIMESTAMP      NEW_VALUE FROM_TIMESTAMP     NOPRINT
COLUMN  _TO_TIMESTAMP        NEW_VALUE TO_TIMESTAMP       NOPRINT
COLUMN  _INSTANCE_LIST       NEW_VALUE INSTANCE_LIST      NOPRINT
COLUMN  _SID                 NEW_VALUE SID                NOPRINT
COLUMN  _SQL_ID              NEW_VALUE SQL_ID             NOPRINT
COLUMN  _SERVICE_NAME        NEW_VALUE SERVICE_NAME       NOPRINT
COLUMN  _SERVICE_HASH        NEW_VALUE SERVICE_HASH       NOPRINT

set term off
SELECT DECODE('&&FROM_TIMESTAMP','',to_char(sysdate - 1/24,'YYYY-MM-DD HH24:MI:SS'),'&&FROM_TIMESTAMP')  "_FROM_TIMESTAMP"
     , DECODE('&&TO_TIMESTAMP','',to_char(sysdate ,'YYYY-MM-DD HH24:MI:SS'),'&&TO_TIMESTAMP')  "_TO_TIMESTAMP"
     , DECODE('&&INSTANCE_LIST','','%','&&INSTANCE_LIST')  "_INSTANCE_LIST"
     , DECODE('&&SERVICE_NAME','','%','&&SERVICE_NAME')             "_SERVICE_NAME"
     , DECODE('&&SERVICE_HASH','','%','&&SERVICE_HASH')             "_SERVICE_HASH"
     , DECODE('&&SID','','%','&&SID')            "_SID"
     , DECODE('&&SQL_ID','','%','&&SQL_ID')      "_SQL_ID"
FROM DUAL;

set term on

set term off
SELECT s.name_hash   "_SERVICE_HASH" 
 FROM  dba_services s
WHERE s.name  = '&&SERVICE_NAME';

set term on


PROMPT
PROMPT ***********************************************************************
PROMPT *  A S H    R E P O R T   (HTML)
PROMPT *
PROMPT *  Input Parameters 
PROMPT *  - From Timestamp = '&&FROM_TIMESTAMP' ( YYYY-MM-DD HH24:MI:SS format)  - Default is (sysdate - 1/24)
PROMPT *  - To Timestamp   = '&&TO_TIMESTAMP' ( YYYY-MM-DD HH24:MI:SS format)  - Default is (sysdate)
PROMPT *  - Instance List  = '&&INSTANCE_LIST' ( % for ALL or comma separated list of instance numbers) - Default is %
PROMPT *  - SERVICE_NAME   = '&&SERVICE_NAME'       - Default is %
PROMPT *  - SID            = '&&SID'       - Default is %
PROMPT *  - SQL_ID         = '&&SQL_ID'       - Default is %
PROMPT ***********************************************************************

set pages 0

/*

DBMS_WORKLOAD_REPOSITORY.ASH_GLOBAL_REPORT_TEXT(
   l_dbid          IN VARCHAR2(1023),
   l_inst_num      IN VARCHAR2,
   l_btime         IN DATE,
   l_etime         IN DATE,
   l_options       IN NUMBER    DEFAULT 0,      -- Not currently used by Oracle
   l_slot_width    IN NUMBER    DEFAULT 0,      -- Not currently used by Oracle 
   l_sid           IN NUMBER    DEFAULT NULL,   -- v$session.sid
   l_sql_id        IN VARCHAR2  DEFAULT NULL,   -- V$SQL.SQL_ID                (Wildcard allowed)
   l_wait_class    IN VARCHAR2  DEFAULT NULL,   -- v$event_name.wait_class     (Wildcard allowed)
   l_service_hash  IN NUMBER    DEFAULT NULL,   -- v$active_services.name_hash
   l_module        IN VARCHAR2  DEFAULT NULL,   -- v$session.module            (Wildcard allowed)
   l_action        IN VARCHAR2  DEFAULT NULL,   -- v$session.action            (Wildcard allowed)
   l_client_id     IN VARCHAR2  DEFAULT NULL,   -- v$session.client_identifier (Wildcard allowed)
   l_plsql_entry   IN VARCHAR2  DEFAULT NULL,
   l_data_src      IN NUMBER    DEFAULT 0)
 RETURN awrrpt_text_type_table PIPELINED;
  
*/

set term off 
spool ash_report.html
 SELECT * from table(dbms_workload_repository.ash_global_report_html
                       (  l_dbid         => (select dbid from v$database)
                        , l_inst_num     => DECODE(upper('&INSTANCE_LIST'),'%',NULL,'&INSTANCE_LIST')
                        , l_btime        => TO_DATE('&&FROM_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
                        , l_etime        => TO_DATE('&&TO_TIMESTAMP','YYYY-MM-DD HH24:MI:SS')
                        , l_sid          => DECODE('&&SID','%',NULL,'&&SID')
                        , l_sql_id       => DECODE('&&SQL_ID','%',NULL,'&&SQL_ID')
                        , l_wait_class   => NULL
                        , l_service_hash => DECODE('&&SERVICE_HASH','%',NULL,'&&SERVICE_HASH')
                        , l_module       => NULL
                        , l_action       => NULL
                        , l_client_id    => NULL
                       )
                   );

spool off
set term on
PROMPT 
PROMPT Report generated as ash_report.html file. It will automatically open in your default browser.                  
                  
set pages 5000
host ash_report.html
                   
@@footer