@@header

/*
*
*  Author    : Vishal Gupta
*  Purpose   : Display hourly load profile from AWR for statistics passed as input
*              All figures delta between two consecutive snapshots
*  Parameter : 1 - InstanceNumber (Use % for all instances)
*              2 - From Timestamp (In YYYY-MM-DD HH24:MI:SS format)
*              3 - To Timestamp   (In YYYY-MM-DD HH24:MI:SS format)
*              4 - Statistics Name (Exact Name, use @statname script to find exact name)           
*            
*
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  25-May-12  Vishal Gupta  Intial version
*  
*/


/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE INST_ID
UNDEFINE FROM_TIMESTAMP
UNDEFINE FROM_TIMESTAMP
UNDEFINE STAT_NAME

DEFINE INST_ID="&&1"
DEFINE FROM_TIMESTAMP="&2"
DEFINE TO_TIMESTAMP="&3"
DEFINE STAT_NAME="&4"

/************************************
*  CONFIGURATION PARAMETERS
************************************/

set pages 50000


DEFINE FORMAT="999,999,999,999"
DEFINE TOTAL_FORMAT="999,999,999,999"

DEFINE HEADING="#"
DEFINE DIVIDER="1"
--DEFINE HEADING="'1000s"
--DEFINE DIVIDER="1000"

--DEFINE HEADING="min"
--DEFINE DIVIDER="60/100"
--DEFINE HEADING="hr"
--DEFINE DIVIDER="60/60/100"

--DEFINE HEADING="'KB"
--DEFINE DIVIDER="1024"
--DEFINE HEADING="'MB"
--DEFINE DIVIDER="1024/1024"
--DEFINE HEADING="'GB"
--DEFINE DIVIDER="1024/1024/1024"




PROMPT 
PROMPT ************************************************************************************
PROMPT *
PROMPT *   S Y S T E M    S T A T I S T I C S   T R E N D 
PROMPT *  
PROMPT *   From AWR Repository (Delta Figures)
PROMPT *   - For Instance ID - &INST_ID
PROMPT *   - Between &&FROM_TIMESTAMP and &&TO_TIMESTAMP (YYYY-MM-DD HH24:MI:SS)
PROMPT *   - Statistic Name - "&&STAT_NAME"
PROMPT *
PROMPT *   (All Figures are in &&HEADING)
PROMPT *
PROMPT ************************************************************************************


COLUMN end_interval_time   HEADING "SnapTime"             FORMAT A15
COLUMN all_inst_value      HEADING "All Inst|Value"       FORMAT &&TOTAL_FORMAT
COLUMN inst1_value         HEADING "Inst 1|Value"         FORMAT &&FORMAT
COLUMN inst2_value         HEADING "Inst 2|Value"         FORMAT &&FORMAT
COLUMN inst3_value         HEADING "Inst 3|Value"         FORMAT &&FORMAT
COLUMN inst4_value         HEADING "Inst 4|Value"         FORMAT &&FORMAT
COLUMN inst5_value         HEADING "Inst 5|Value"         FORMAT &&FORMAT
COLUMN inst6_value         HEADING "Inst 6|Value"         FORMAT &&FORMAT
COLUMN inst7_value         HEADING "Inst 7|Value"         FORMAT &&FORMAT
COLUMN inst8_value         HEADING "Inst 8|Value"         FORMAT &&FORMAT

WITH snap
AS
 (SELECT s.dbid
       , s.instance_number
       , s.snap_id
       , LEAD (s.snap_id) OVER (ORDER BY s.dbid, s.instance_number, s.snap_id) next_snap_id
       , s.end_interval_time
   FROM  dba_hist_snapshot s
       , v$database d
  WHERE s.dbid = d.dbid
    AND s.instance_number LIKE '&&INST_ID'
    AND s.end_interval_time BETWEEN TO_TIMESTAMP('&&FROM_TIMESTAMP','YYYY-MM-DD HH24:MI:SS') 
                                AND TO_TIMESTAMP('&&TO_TIMESTAMP'  ,'YYYY-MM-DD HH24:MI:SS')
 )
SELECT /*+ USE_NL(s1 ss1 ss_prev) */ 
      TO_CHAR(s.end_interval_time,'DD-MON-YY HH24:MI')  end_interval_time
    , ROUND(SUM(ss.value - LEAST(ss_prev.value,ss.value)) / &&DIVIDER ) all_inst_value
    , ROUND(SUM(DECODE(ss.instance_number,1,(ss.value - LEAST(ss_prev.value,ss.value)),0)) / &&DIVIDER ) inst1_value
    , ROUND(SUM(DECODE(ss.instance_number,2,(ss.value - LEAST(ss_prev.value,ss.value)),0)) / &&DIVIDER ) inst2_value
    , ROUND(SUM(DECODE(ss.instance_number,3,(ss.value - LEAST(ss_prev.value,ss.value)),0)) / &&DIVIDER ) inst3_value
    , ROUND(SUM(DECODE(ss.instance_number,4,(ss.value - LEAST(ss_prev.value,ss.value)),0)) / &&DIVIDER ) inst4_value
    , ROUND(SUM(DECODE(ss.instance_number,5,(ss.value - LEAST(ss_prev.value,ss.value)),0)) / &&DIVIDER ) inst5_value
    , ROUND(SUM(DECODE(ss.instance_number,6,(ss.value - LEAST(ss_prev.value,ss.value)),0)) / &&DIVIDER ) inst6_value
    , ROUND(SUM(DECODE(ss.instance_number,7,(ss.value - LEAST(ss_prev.value,ss.value)),0)) / &&DIVIDER ) inst7_value
    , ROUND(SUM(DECODE(ss.instance_number,8,(ss.value - LEAST(ss_prev.value,ss.value)),0)) / &&DIVIDER ) inst8_value
FROM snap s
   , dba_hist_sysstat ss
   , dba_hist_sysstat ss_prev
where s.next_snap_id IS NOT NULL
and s.dbid = ss_prev.dbid
and s.instance_number = ss_prev.instance_number
and s.snap_id = ss_prev.snap_id
and s.dbid = ss.dbid
and s.instance_number = ss.instance_number
and s.next_snap_id = ss.snap_id
and ss_prev.stat_name = ss.stat_name
and ss.stat_name = '&&STAT_NAME'
GROUP BY TO_CHAR(s.end_interval_time,'DD-MON-YY HH24:MI')
ORDER BY TO_DATE(TO_CHAR(s.end_interval_time,'DD-MON-YY HH24:MI'),'DD-MON-YY HH24:MI') asc
/


@@footer