@@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 ************************************/ DEFINE FORMAT="99999" DEFINE TOTAL_FORMAT="999,999" DEFINE HEADING="" DEFINE DIVIDER="" COLUMN _HEADING NEW_VALUE HEADING NOPRINT COLUMN _DIVIDER NEW_VALUE DIVIDER NOPRINT set term off SELECT CASE --WHEN UPPER('&&STAT_NAME') LIKE '%CPU%' THEN '#' WHEN UPPER('&&STAT_NAME') LIKE '%CPU%' THEN 'thousands' --WHEN UPPER('&&STAT_NAME') LIKE '%CPU%' THEN 'millions' --WHEN UPPER('&&STAT_NAME') LIKE '%TIME%' THEN 'sec' --WHEN UPPER('&&STAT_NAME') LIKE '%TIME%' THEN 'min' WHEN UPPER('&&STAT_NAME') LIKE '%TIME%' THEN 'hour' --WHEN UPPER('&&STAT_NAME') LIKE '%BYTES%' THEN 'KB' --WHEN UPPER('&&STAT_NAME') LIKE '%BYTES%' THEN 'MB' WHEN UPPER('&&STAT_NAME') LIKE '%BYTES%' THEN 'GB' ELSE '#' END "_HEADING" , CASE --WHEN UPPER('&&STAT_NAME') LIKE '%CPU%' THEN '1' WHEN UPPER('&&STAT_NAME') LIKE '%CPU%' THEN '1000' --WHEN UPPER('&&STAT_NAME') LIKE '%CPU%' THEN '1000000' --WHEN UPPER('&&STAT_NAME') LIKE '%TIME%' THEN '100' --WHEN UPPER('&&STAT_NAME') LIKE '%TIME%' THEN '60/100' WHEN UPPER('&&STAT_NAME') LIKE '%TIME%' THEN '60/60/100' --WHEN UPPER('&&STAT_NAME') LIKE '%BYTES%' THEN '1024' --WHEN UPPER('&&STAT_NAME') LIKE '%BYTES%' THEN '1024/1024' WHEN UPPER('&&STAT_NAME') LIKE '%BYTES%' THEN '1024/1024/1024' ELSE '1' END "_DIVIDER" FROM DUAL; set term on --DEFINE HEADING="#" --DEFINE DIVIDER="1" --&&_IF_COUNT_METRIC DEFINE HEADING="'1000s" --&&_IF_COUNT_METRIC DEFINE DIVIDER="1000" --&&_IF_TIME_METRIC DEFINE HEADING="minutes" --&&_IF_TIME_METRIC DEFINE DIVIDER="60/100" --&&_IF_TIME_METRIC DEFINE HEADING="hour" --&&_IF_TIME_METRIC DEFINE DIVIDER="60/60/100" --DEFINE HEADING="KB" --DEFINE DIVIDER="1024" --DEFINE HEADING="MB" --DEFINE DIVIDER="1024/1024" --&&_IF_BYTES_METRIC DEFINE HEADING="GB" --&&_IF_BYTES_METRIC 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 (Delta Figures) 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 "Date" HEADING "Date" FORMAT A9 COLUMN "Day" HEADING "Day" FORMAT A3 COLUMN "Total" HEADING "Day|Total" FORMAT &&TOTAL_FORMAT COLUMN h0 HEADING "h0" FORMAT &&FORMAT COLUMN h1 HEADING "h1" FORMAT &&FORMAT COLUMN h2 HEADING "h2" FORMAT &&FORMAT COLUMN h3 HEADING "h3" FORMAT &&FORMAT COLUMN h4 HEADING "h4" FORMAT &&FORMAT COLUMN h5 HEADING "h5" FORMAT &&FORMAT COLUMN h6 HEADING "h6" FORMAT &&FORMAT COLUMN h7 HEADING "h7" FORMAT &&FORMAT COLUMN h8 HEADING "h8" FORMAT &&FORMAT COLUMN h9 HEADING "h9" FORMAT &&FORMAT COLUMN h10 HEADING "h10" FORMAT &&FORMAT COLUMN h11 HEADING "h11" FORMAT &&FORMAT COLUMN h12 HEADING "h12" FORMAT &&FORMAT COLUMN h13 HEADING "h13" FORMAT &&FORMAT COLUMN h14 HEADING "h14" FORMAT &&FORMAT COLUMN h15 HEADING "h15" FORMAT &&FORMAT COLUMN h16 HEADING "h16" FORMAT &&FORMAT COLUMN h17 HEADING "h17" FORMAT &&FORMAT COLUMN h18 HEADING "h18" FORMAT &&FORMAT COLUMN h19 HEADING "h19" FORMAT &&FORMAT COLUMN h20 HEADING "h20" FORMAT &&FORMAT COLUMN h21 HEADING "h21" FORMAT &&FORMAT COLUMN h22 HEADING "h22" FORMAT &&FORMAT COLUMN h23 HEADING "h23" FORMAT &&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') - 1/24 AND TO_TIMESTAMP('&&TO_TIMESTAMP','YYYY-MM-DD HH24:MI:SS') ) SELECT /*+ LEADING(s sn) USE_NL(s sn ss ss_prev) */ TO_CHAR(trunc(s.end_interval_time),'DD-Mon-YY') "Date" , TO_CHAR(trunc(s.end_interval_time), 'Dy') "Day" , ROUND(SUM(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)) / &&DIVIDER ) Total , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'00',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h0" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'01',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h1" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'02',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h2" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'03',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h3" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'04',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h4" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'05',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h5" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'06',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h6" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'07',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h7" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'08',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h8" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'09',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h9" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'10',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h10" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'11',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h11" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'12',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h12" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'13',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h13" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'14',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h14" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'15',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h15" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'16',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h16" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'17',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h17" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'18',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h18" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'19',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h19" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'20',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h20" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'21',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h21" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'22',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h22" , ROUND(SUM(DECODE(TO_CHAR(s.end_interval_time, 'hh24'),'23',(ss.value - LEAST(NVL(ss_prev.value,ss.value),ss.value)),0)) / &&DIVIDER ) "h23" FROM snap s , dba_hist_stat_name sn , dba_hist_sysstat ss , dba_hist_sysstat ss_prev WHERE ss.dbid = s.dbid AND ss.instance_number = s.instance_number AND ss.snap_id = s.snap_id AND ss_prev.dbid = s.dbid AND ss_prev.instance_number = s.instance_number AND ss_prev.snap_id = s.prev_snap_id AND ss_prev.stat_id = ss.stat_id AND sn.dbid = s.dbid AND sn.stat_id = ss.stat_id AND UPPER(sn.stat_name) = UPPER('&&STAT_NAME') GROUP BY trunc(s.end_interval_time) ORDER BY trunc(s.end_interval_time) asc / @@footer