153 lines
7.0 KiB
SQL
153 lines
7.0 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Service statistics from AWR
|
|
* Parameters : 1 - Statistic Name (Use % as wildcard, escape with \)
|
|
* 2 - SERVICE_NAME (Use % as wildcard, escape with \)
|
|
* 3 - From timestamp (YYYY-MM-DD HH24:MI:SS format, Default value sysdate - 2 hours)
|
|
* 4 - To timstamp (YYYY-MM-DD HH24:MI:SS format, Default value sysdate)
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 19-Mar-12 Vishal Gupta Intial version
|
|
* 15-May-12 Vishal Gupta Change from/to relative hours to absolute timestamp
|
|
*
|
|
*
|
|
*/
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE service_name
|
|
UNDEFINE stat_name
|
|
UNDEFINE from_timestamp
|
|
UNDEFINE to_timestamp
|
|
|
|
DEFINE stat_name="&&1"
|
|
DEFINE service_name="&&2"
|
|
DEFINE from_timestamp="&&3"
|
|
DEFINE to_timestamp="&&4"
|
|
|
|
set term off
|
|
COLUMN _stat_name NEW_VALUE stat_name NOPRINT
|
|
COLUMN _service_name NEW_VALUE service_name NOPRINT
|
|
COLUMN _from_timestamp NEW_VALUE from_timestamp NOPRINT
|
|
COLUMN _to_timestamp NEW_VALUE to_timestamp NOPRINT
|
|
|
|
SELECT UPPER(DECODE('&&stat_name','','physical reads','&&stat_name')) "_stat_name"
|
|
, UPPER(DECODE('&&service_name','','%','&&service_name')) "_service_name"
|
|
, UPPER(DECODE('&&from_timestamp','',TO_CHAR(sysdate - 120/(24*60),'YYYY-MM-DD HH24:MI:SS'),'&&from_timestamp')) "_from_timestamp"
|
|
, UPPER(DECODE('&&to_timestamp','',TO_CHAR(sysdate ,'YYYY-MM-DD HH24:MI:SS'),'&&to_timestamp')) "_to_timestamp"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
DEFINE FORMAT=999,999,999
|
|
DEFINE DIVIDER="1"
|
|
DEFINE HEADING="#s"
|
|
--DEFINE DIVIDER="1000"
|
|
--DEFINE HEADING="#1000s"
|
|
--DEFINE DIVIDER="1000000"
|
|
--DEFINE HEADING="#1000000s"
|
|
|
|
DEFINE TOTAL_FORMAT=999,999,999,999
|
|
DEFINE TOTAL_HEADING="#1000s"
|
|
|
|
|
|
PROMPT ***************************************************************************************************
|
|
PROMPT * AWR Service Statistics (Delta)
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Service Name = '&&service_name'
|
|
PROMPT * - Statistic Name = '&&stat_name'
|
|
PROMPT * - From Timestamp = '&&from_timestamp'
|
|
PROMPT * - To Timestamp = '&&to_timestamp'
|
|
PROMPT ***************************************************************************************************
|
|
|
|
|
|
COLUMN end_interval_time HEADING "SnapTime" FORMAT a15
|
|
COLUMN service_name HEADING "ServiceName" FORMAT a25
|
|
COLUMN stat_name HEADING "StatisticName" FORMAT a25
|
|
COLUMN value_clusterwide HEADING "RAC Wide|Value|(&&TOTAL_HEADING)" FORMAT &&TOTAL_FORMAT
|
|
COLUMN value_inst1 HEADING "Inst 1|Value|(&&HEADING)" FORMAT &&FORMAT
|
|
COLUMN value_inst2 HEADING "Inst 2|Value|(&&HEADING)" FORMAT &&FORMAT
|
|
COLUMN value_inst3 HEADING "Inst 3|Value|(&&HEADING)" FORMAT &&FORMAT
|
|
COLUMN value_inst4 HEADING "Inst 4|Value|(&&HEADING)" FORMAT &&FORMAT
|
|
COLUMN value_inst5 HEADING "Inst 5|Value|(&&HEADING)" FORMAT &&FORMAT
|
|
COLUMN value_inst6 HEADING "Inst 6|Value|(&&HEADING)" FORMAT &&FORMAT
|
|
COLUMN value_inst7 HEADING "Inst 7|Value|(&&HEADING)" FORMAT &&FORMAT
|
|
COLUMN value_inst8 HEADING "Inst 8|Value|(&&HEADING)" FORMAT &&FORMAT
|
|
|
|
BREAK ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF value_clusterwide FORMAT &&TOTAL_FORMAT ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF value_inst1 FORMAT &&TOTAL_FORMAT ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF value_inst2 FORMAT &&TOTAL_FORMAT ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF value_inst3 FORMAT &&TOTAL_FORMAT ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF value_inst4 FORMAT &&TOTAL_FORMAT ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF value_inst5 FORMAT &&TOTAL_FORMAT ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF value_inst6 FORMAT &&TOTAL_FORMAT ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF value_inst7 FORMAT &&TOTAL_FORMAT ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF value_inst8 FORMAT &&TOTAL_FORMAT ON REPORT
|
|
|
|
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 d.dbid = s.dbid
|
|
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 TO_CHAR(s.end_interval_time,'DD-MON-YY HH24:MI') end_interval_time
|
|
, ss.service_name
|
|
, ss.stat_name
|
|
, SUM( ss.value-LEAST(ss_prev.value,ss.value))/&&DIVIDER value_clusterwide
|
|
, SUM(DECODE(ss.instance_number, 1, ss.value-LEAST(ss_prev.value,ss.value), 0))/&&DIVIDER value_inst1
|
|
, SUM(DECODE(ss.instance_number, 2, ss.value-LEAST(ss_prev.value,ss.value), 0))/&&DIVIDER value_inst2
|
|
, SUM(DECODE(ss.instance_number, 3, ss.value-LEAST(ss_prev.value,ss.value), 0))/&&DIVIDER value_inst3
|
|
, SUM(DECODE(ss.instance_number, 4, ss.value-LEAST(ss_prev.value,ss.value), 0))/&&DIVIDER value_inst4
|
|
, SUM(DECODE(ss.instance_number, 5, ss.value-LEAST(ss_prev.value,ss.value), 0))/&&DIVIDER value_inst5
|
|
, SUM(DECODE(ss.instance_number, 6, ss.value-LEAST(ss_prev.value,ss.value), 0))/&&DIVIDER value_inst6
|
|
, SUM(DECODE(ss.instance_number, 7, ss.value-LEAST(ss_prev.value,ss.value), 0))/&&DIVIDER value_inst7
|
|
, SUM(DECODE(ss.instance_number, 8, ss.value-LEAST(ss_prev.value,ss.value), 0))/&&DIVIDER value_inst8
|
|
FROM snap s
|
|
, dba_hist_service_stat ss
|
|
, dba_hist_service_stat ss_prev
|
|
WHERE s.dbid = ss.dbid
|
|
AND s.instance_number = ss.instance_number
|
|
AND s.snap_id = ss.snap_id
|
|
AND s.dbid = ss_prev.dbid
|
|
AND s.instance_number = ss_prev.instance_number
|
|
AND s.prev_snap_id = ss_prev.snap_id
|
|
AND ss.service_name = ss_prev.service_name
|
|
AND ss.stat_name = ss_prev.stat_name
|
|
AND UPPER(ss.service_name) LIKE UPPER('&&service_name') ESCAPE '\'
|
|
AND UPPER(ss.stat_name) LIKE UPPER('&&stat_name') ESCAPE '\'
|
|
GROUP BY TO_CHAR(s.end_interval_time,'DD-MON-YY HH24:MI')
|
|
, ss.service_name
|
|
, ss.stat_name
|
|
ORDER BY TO_DATE(TO_CHAR(s.end_interval_time,'DD-MON-YY HH24:MI'),'DD-MON-YY HH24:MI')
|
|
, ss.service_name
|
|
, ss.stat_name
|
|
;
|
|
|
|
UNDEFINE service_name
|
|
UNDEFINE stat_name
|
|
UNDEFINE from_timestamp
|
|
UNDEFINE to_timestamp
|
|
|
|
|
|
@@footer
|