@@header

/*
*
*  Author    : Vishal Gupta
*  Purpose   : Display system metric per sec from AWR repository
*  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
*  ---------  ------------  -----------------------------------------
*  05-Aug-12  Vishal Gupta  Created
*  
*/


/************************************
*  INPUT PARAMETERS
************************************/
DEFINE INST_ID="&&1"
DEFINE FROM_TIMESTAMP="&2"
DEFINE TO_TIMESTAMP="&3"
DEFINE STAT_NAME="&4"

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



DEFINE FORMAT="9999"
DEFINE TOTAL_FORMAT="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"

DEFINE TIME_FORMAT="DD-MON-YY HH24:MI"
--DEFINE TIME_FORMAT="DD-MON-YY HH24"

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 (Absolute figures , not delta)
PROMPT #   - Statistic Name  - "&&STAT_NAME"
PROMPT #   - For Instance ID - &INST_ID
PROMPT #   - Snap Between    - &&FROM_TIMESTAMP and &&TO_TIMESTAMP (YYYY-MM-DD HH24:MI:SS)
PROMPT #
PROMPT #   (All Figures are in &&HEADING)
PROMPT #
PROMPT ####################################################################################

COLUMN time                           HEADING "Time|&&TIME_FORMAT"            FORMAT a18
COLUMN instance_number                HEADING "I#"                            FORMAT 99
COLUMN average_active_session         HEADING "Avg|Active|Session"            FORMAT 9999999
COLUMN redo                           HEADING "Redo|PerSec"                   FORMAT 9999999
COLUMN logons                         HEADING "Logons|PerSec"                 FORMAT 9999999
COLUMN user_calls                     HEADING "UserCalls|PerSec"              FORMAT 9999999
COLUMN executions                     HEADING "Exec|PerSec"                   FORMAT 9999999
COLUMN physical_reads                 HEADING "PhyReads|PerSec"               FORMAT 9999999
COLUMN physical_writes                HEADING "PhyWrites|PerSec"              FORMAT 9999999
COLUMN network_traffic_volume         HEADING "Network|Traffic|Bytes|PerSec" FORMAT 9999999
COLUMN user_transactions              HEADING "Transactions|PerSec"           FORMAT 9999999

SELECT TO_CHAR(m.end_time,'&&TIME_FORMAT') Time     
     , m.instance_number
     , AVG(DECODE(m.metric_name,'Average Active Sessions',m.value,0)) average_active_session
     , AVG(DECODE(m.metric_name,'Redo Generated Per Sec',m.value,0)) redo
     , AVG(DECODE(m.metric_name,'Logons Per Sec',m.value,0)) logons
     , AVG(DECODE(m.metric_name,'User Calls Per Sec',m.value,0)) user_calls
     , AVG(DECODE(m.metric_name,'Executions Per Sec',m.value,0)) executions
     , AVG(DECODE(m.metric_name,'Physical Reads Per Sec',m.value,0)) physical_reads
     , AVG(DECODE(m.metric_name,'Physical Writes Per Sec',m.value,0)) physical_writes
     , AVG(DECODE(m.metric_name,'Network Traffic Volume Per Sec',m.value,0)) network_traffic_volume
     , AVG(DECODE(m.metric_name,'User Transaction Per Sec',m.value,0)) user_transactions
  FROM dba_hist_sysmetric_history m
  JOIN v$database d ON d.dbid = m.dbid
GROUP BY TO_CHAR(m.end_time,'&&TIME_FORMAT')
       , m.instance_number
ORDER BY TO_DATE(TO_CHAR(m.end_time,'&&TIME_FORMAT'),'&&TIME_FORMAT')  
;

UNDEFINE INST_ID
UNDEFINE FROM_TIMESTAMP
UNDEFINE FROM_TIMESTAMP
UNDEFINE STAT_NAME


@@footer