@@header

PROMPT
PROMPT ####################################################################################
PROMPT NOT COMPLETED YET
PROMPT ####################################################################################


/*
*
*  Author    : Vishal Gupta
*  Purpose   : Display AWR Statistics values
*  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)
*              3 - Statistic Name (Use % as wildcard, \ as escape)
*  
*             dbid AND instancenumber are optional. 
*             If not passes then it takes current instance's dbid AND instancenumber
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  04-Apr-12  Vishal Gupta  Created
*  
*/


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

set pages 100


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

DEFINE COUNT_FORMAT=9,999,999
--DEFINE COUNT_DIVIDER="1"
--DEFINE COUNT_HEADING="#"
DEFINE COUNT_DIVIDER="1000"
DEFINE COUNT_HEADING="#1000"



PROMPT 
PROMPT ####################################################################################
PROMPT #   S Y S T E M    S T A T I S T I C S (From AWR Repository)
PROMPT #  
PROMPT #   - For Instance ID - &INST_ID
PROMPT #   - Between &&FROM_TIMESTAMP AND &&TO_TIMESTAMP (YYYY-MM-DD HH24:MI:SS)
PROMPT #   - Statistic - &&statname
PROMPT # 
PROMPT ####################################################################################


COLUMN end_interval_time   HEADING "Snap Time"                                FORMAT a18
COLUMN stat_name           HEADING "Statistic Name"                           FORMAT a30
COLUMN value               HEADING "Statistic Value"                          FORMAT 999,999,999,999,999,999,999

SELECT MAX(TO_CHAR(s.end_interval_time,'DD-MON-YY hh24')) end_interval_time
     , ss.stat_name
     , SUM(ss.value) value
FROM dba_hist_snapshot s
   , dba_hist_sysstat ss
   , v$database d
where s.dbid = d.dbid
 AND s.dbid = ss.dbid
 AND s.instance_number = ss.instance_number
 AND s.snap_id = ss.snap_id
 AND upper(ss.stat_name) LIKE UPPER('&&statname') ESCAPE '\'
 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')
GROUP BY s.dbid,  TO_CHAR(s.end_interval_time,'DD-MON-YY hh24'),  ss.stat_name                             
ORDER BY TO_DATE(MAX(TO_CHAR(s.end_interval_time,'DD-MON-YY hh24')),'DD-Mon-YY hh24') asc
/

UNDEFINE INST_ID
UNDEFINE FROM_TIMESTAMP
UNDEFINE FROM_TIMESTAMP


@@footer