123 lines
5.6 KiB
SQL
123 lines
5.6 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display AWR Statistics value delta
|
|
* 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 - Statistic Name (Use % as wildcard, \ as escape)
|
|
*
|
|
* If not passes then it takes current instance's dbid and instancenumber
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 04-Apr-12 Vishal Gupta First Draft
|
|
*
|
|
*/
|
|
|
|
|
|
UNDEFINE INST_ID
|
|
UNDEFINE FROM_TIMESTAMP
|
|
UNDEFINE TO_TIMESTAMP
|
|
UNDEFINE STAT_NAME
|
|
|
|
DEFINE INST_ID="&&1"
|
|
DEFINE FROM_TIMESTAMP="&2"
|
|
DEFINE TO_TIMESTAMP="&3"
|
|
DEFINE STAT_NAME="&4"
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
|
|
DEFINE COUNT_FORMAT=9,999,999
|
|
DEFINE COUNT_DIVIDER="1"
|
|
DEFINE COUNT_HEADING="#"
|
|
--DEFINE COUNT_DIVIDER="1000"
|
|
--DEFINE COUNT_HEADING="#1000"
|
|
|
|
--DEFINE COUNT_DIVIDER="1024"
|
|
--DEFINE COUNT_HEADING="KB"
|
|
--DEFINE COUNT_DIVIDER="1024/1024"
|
|
--DEFINE COUNT_HEADING="MB"
|
|
|
|
|
|
|
|
PROMPT
|
|
PROMPT ***********************************************************************
|
|
PROMPT * System Statistics Delta (From AWR Repository)
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Instance Number = &INST_ID
|
|
PROMPT * - From Timestamp = "&&from_timestamp" (YYYY-MM-DD HH24:MI:SS)
|
|
PROMPT * - To Timestamp = "&&to_timestamp" (YYYY-MM-DD HH24:MI:SS)
|
|
PROMPT * - Statistic Name = "&&STAT_NAME"
|
|
PROMPT ***********************************************************************
|
|
|
|
COLUMN end_interval_time HEADING "Snap Time|(DD-MON-YY HH24:MI)" FORMAT a19
|
|
COLUMN stat_name HEADING "Statistic Name" FORMAT a30
|
|
COLUMN value_delta_all HEADING "Total|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN value_delta1 HEADING "Inst1|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN value_delta2 HEADING "Inst2|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN value_delta3 HEADING "Inst3|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN value_delta4 HEADING "Inst4|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN value_delta5 HEADING "Inst5|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN value_delta6 HEADING "Inst6|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN value_delta7 HEADING "Inst7|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN value_delta8 HEADING "Inst8|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN value_delta9 HEADING "Inst9|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN value_delta10 HEADING "Inst10|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
COLUMN value_delta11 HEADING "Inst11|(&&COUNT_HEADING)" FORMAT &&COUNT_FORMAT
|
|
|
|
WITH snap
|
|
AS
|
|
(SELECT s.dbid
|
|
, s.instance_number
|
|
, s.snap_id
|
|
, LAG (s.snap_id) OVER (PARTITION BY s.dbid, s.instance_number ORDER BY s.dbid, s.instance_number, s.snap_id) prev_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
|
|
--, ss.stat_name
|
|
, SUM(ss.value - LEAST(ss_prev.value,ss.value))/&&COUNT_DIVIDER value_delta_all
|
|
, SUM(DECODE(ss.instance_number,1,ss.value - LEAST(ss_prev.value,ss.value)))/&&COUNT_DIVIDER value_delta1
|
|
, SUM(DECODE(ss.instance_number,2,ss.value - LEAST(ss_prev.value,ss.value)))/&&COUNT_DIVIDER value_delta2
|
|
, SUM(DECODE(ss.instance_number,3,ss.value - LEAST(ss_prev.value,ss.value)))/&&COUNT_DIVIDER value_delta3
|
|
, SUM(DECODE(ss.instance_number,4,ss.value - LEAST(ss_prev.value,ss.value)))/&&COUNT_DIVIDER value_delta4
|
|
, SUM(DECODE(ss.instance_number,5,ss.value - LEAST(ss_prev.value,ss.value)))/&&COUNT_DIVIDER value_delta5
|
|
, SUM(DECODE(ss.instance_number,6,ss.value - LEAST(ss_prev.value,ss.value)))/&&COUNT_DIVIDER value_delta6
|
|
, SUM(DECODE(ss.instance_number,7,ss.value - LEAST(ss_prev.value,ss.value)))/&&COUNT_DIVIDER value_delta7
|
|
, SUM(DECODE(ss.instance_number,8,ss.value - LEAST(ss_prev.value,ss.value)))/&&COUNT_DIVIDER value_delta8
|
|
, SUM(DECODE(ss.instance_number,9,ss.value - LEAST(ss_prev.value,ss.value)))/&&COUNT_DIVIDER value_delta9
|
|
, SUM(DECODE(ss.instance_number,10,ss.value - LEAST(ss_prev.value,ss.value)))/&&COUNT_DIVIDER value_delta10
|
|
, SUM(DECODE(ss.instance_number,11,ss.value - LEAST(ss_prev.value,ss.value)))/&&COUNT_DIVIDER value_delta11
|
|
FROM snap s
|
|
, dba_hist_sysstat ss
|
|
, dba_hist_sysstat ss_prev
|
|
where s.prev_snap_id IS NOT NULL
|
|
and s.dbid = ss_prev.dbid
|
|
and s.instance_number = ss_prev.instance_number
|
|
and s.prev_snap_id = ss_prev.snap_id
|
|
and s.dbid = ss.dbid
|
|
and s.instance_number = ss.instance_number
|
|
and s.snap_id = ss.snap_id
|
|
and ss_prev.stat_name = ss.stat_name
|
|
and upper(ss.stat_name) LIKE UPPER('&&STAT_NAME') ESCAPE '\'
|
|
GROUP BY TO_CHAR(s.end_interval_time,'DD-MON-YY HH24:MI')
|
|
, ss.stat_name
|
|
ORDER BY TO_DATE(TO_CHAR(s.end_interval_time,'DD-MON-YY HH24:MI'),'DD-MON-YY HH24:MI') asc
|
|
/
|
|
|
|
@@footer |