117 lines
4.2 KiB
SQL
117 lines
4.2 KiB
SQL
@@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 (Absolute figures , not delta)
|
|
PROMPT # - For Instance ID - &INST_ID
|
|
PROMPT # - Snap 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
|
|
|
|
SELECT /*+ USE_NL(s ss) */
|
|
TO_CHAR(s.end_interval_time,'DD-MON-YY HH24:MI') end_interval_time
|
|
, ROUND(SUM(ss.value) / &&DIVIDER ) all_inst_value
|
|
, ROUND(SUM(DECODE(ss.instance_number,1,ss.value,0)) / &&DIVIDER ) inst1_value
|
|
, ROUND(SUM(DECODE(ss.instance_number,2,ss.value,0)) / &&DIVIDER ) inst2_value
|
|
, ROUND(SUM(DECODE(ss.instance_number,3,ss.value,0)) / &&DIVIDER ) inst3_value
|
|
, ROUND(SUM(DECODE(ss.instance_number,4,ss.value,0)) / &&DIVIDER ) inst4_value
|
|
, ROUND(SUM(DECODE(ss.instance_number,5,ss.value,0)) / &&DIVIDER ) inst5_value
|
|
, ROUND(SUM(DECODE(ss.instance_number,6,ss.value,0)) / &&DIVIDER ) inst6_value
|
|
, ROUND(SUM(DECODE(ss.instance_number,7,ss.value,0)) / &&DIVIDER ) inst7_value
|
|
, ROUND(SUM(DECODE(ss.instance_number,8,ss.value,0)) / &&DIVIDER ) inst8_value
|
|
FROM dba_hist_snapshot s
|
|
, dba_hist_sysstat ss
|
|
where s.dbid = ss.dbid
|
|
and s.instance_number = ss.instance_number
|
|
and s.snap_id = ss.snap_id
|
|
and ss.stat_name = '&&STAT_NAME'
|
|
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 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
|