222 lines
7.9 KiB
MySQL
222 lines
7.9 KiB
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display load profile from AWR
|
|
* All figures delta between two consecutive snapshots
|
|
* Parameter : 1 - InstanceNumber (Use % for all instances)
|
|
* 2 - No of days
|
|
*
|
|
* dbid and instancenumber are optional.
|
|
* If not passes then it takes current instance's dbid and instancenumber
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 04-Apr-12 Vishal Gupta First Draft
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE INST_ID
|
|
UNDEFINE days
|
|
|
|
DEFINE INST_ID="&&1"
|
|
DEFINE days="&&2"
|
|
|
|
|
|
COLUMN _INST_ID NEW_VALUE INST_ID NOPRINT
|
|
COLUMN _days NEW_VALUE days NOPRINT
|
|
|
|
set term off
|
|
SELECT DECODE('&&INST_ID','','%','&&INST_ID') "_INST_ID"
|
|
, DECODE('&&days','','7','&&days') "_days"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
|
|
set pages 2000
|
|
|
|
DEFINE COUNT_FORMAT="999,999"
|
|
|
|
--DEFINE COUNT_DIVIDER="1"
|
|
--DEFINE COUNT_HEADING="#"
|
|
DEFINE COUNT_DIVIDER="1000"
|
|
DEFINE COUNT_HEADING="'000s"
|
|
|
|
DEFINE BYTES_FORMAT="9999"
|
|
|
|
--DEFINE BYTES_DIVIDER="1024"
|
|
--DEFINE BYTES_HEADING="KB"
|
|
--DEFINE BYTES_DIVIDER="1024/1024"
|
|
--DEFINE BYTES_HEADING="MB"
|
|
DEFINE BYTES_DIVIDER="1024/1024/1024"
|
|
DEFINE BYTES_HEADING="GB"
|
|
|
|
DEFINE TIME_FORMAT=9,999
|
|
DEFINE TIME_DIVIDER="1"
|
|
DEFINE TIME_HEADING="sec"
|
|
|
|
|
|
PROMPT
|
|
PROMPT #################################################################
|
|
PROMPT L O G I C A L ( C A C H E D ) R E A D S T R E N D
|
|
PROMPT #################################################################
|
|
|
|
|
|
COLUMN end_interval_day HEADING "Date|DD-MON-YY Day" FORMAT a14
|
|
COLUMN DayTotal HEADING "DayTotal|(&BYTES_HEADING)" FORMAT 999,999
|
|
COLUMN h00 HEADING "h00|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h01 HEADING "h01|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h02 HEADING "h02|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h03 HEADING "h03|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h04 HEADING "h04|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h05 HEADING "h05|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h06 HEADING "h06|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h07 HEADING "h07|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h08 HEADING "h08|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h09 HEADING "h09|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h10 HEADING "h10|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h11 HEADING "h11|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h12 HEADING "h12|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h13 HEADING "h13|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h14 HEADING "h14|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h15 HEADING "h15|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h16 HEADING "h16|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h17 HEADING "h17|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h18 HEADING "h18|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h19 HEADING "h19|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h20 HEADING "h20|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h21 HEADING "h21|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h22 HEADING "h22|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
COLUMN h23 HEADING "h23|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
|
|
|
|
BREAK ON REPORT
|
|
|
|
COMPUTE MIN MAX AVG OF DayTotal ON REPORT
|
|
COMPUTE MIN MAX AVG OF h00 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h01 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h02 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h03 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h04 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h05 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h06 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h07 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h08 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h09 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h10 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h11 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h12 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h13 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h14 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h15 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h16 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h17 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h18 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h19 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h20 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h21 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h22 ON REPORT
|
|
COMPUTE MIN MAX AVG OF h23 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 s.dbid = d.dbid
|
|
AND s.instance_number LIKE '&inst_id'
|
|
AND s.end_interval_time > TRUNC(systimestamp) - &days
|
|
)
|
|
, awr_stats as
|
|
(
|
|
SELECT TRUNC(s1.end_interval_time) end_interval_day
|
|
, TO_CHAR(s1.end_interval_time,'hh24') end_interval_hour
|
|
, ROUND((ss2.value - NVL(ss1.value,ss2.value))*p.value/&BYTES_DIVIDER) value
|
|
FROM snap s1
|
|
, dba_hist_sysstat ss1
|
|
, dba_hist_sysstat ss2
|
|
, dba_hist_stat_name sn
|
|
, v$system_parameter p
|
|
where p.name = 'db_block_size'
|
|
and s1.dbid = ss1.dbid
|
|
and s1.instance_number = ss1.instance_number
|
|
and s1.prev_snap_id = ss1.snap_id
|
|
and s1.dbid = ss2.dbid
|
|
and s1.instance_number = ss2.instance_number
|
|
and s1.snap_id = ss2.snap_id
|
|
and ss1.stat_id = ss2.stat_id
|
|
and ss1.dbid = sn.dbid
|
|
and ss1.stat_id = sn.stat_id
|
|
and sn.stat_name in (
|
|
'consistent gets'
|
|
/*
|
|
'redo size'
|
|
,'session logical reads'
|
|
,'db block changes'
|
|
,'physical reads'
|
|
,'physical writes'
|
|
,'user calls'
|
|
,'parse count (total)'
|
|
,'parse count (hard)'
|
|
,'sorts (memory)'
|
|
,'sorts (disk)'
|
|
,'logons cumulative'
|
|
,'execute count'
|
|
,'user commits'
|
|
,'user rollbacks'
|
|
*/
|
|
)
|
|
)
|
|
SELECT TO_CHAR(s.end_interval_day,'DD-Mon-YY Dy') end_interval_day
|
|
, SUM(s.value) DayTotal
|
|
, SUM(DECODE(s.end_interval_hour,'00',s.value,0)) h00
|
|
, SUM(DECODE(s.end_interval_hour,'01',s.value,0)) h01
|
|
, SUM(DECODE(s.end_interval_hour,'02',s.value,0)) h02
|
|
, SUM(DECODE(s.end_interval_hour,'03',s.value,0)) h03
|
|
, SUM(DECODE(s.end_interval_hour,'04',s.value,0)) h04
|
|
, SUM(DECODE(s.end_interval_hour,'05',s.value,0)) h05
|
|
, SUM(DECODE(s.end_interval_hour,'06',s.value,0)) h06
|
|
, SUM(DECODE(s.end_interval_hour,'07',s.value,0)) h07
|
|
, SUM(DECODE(s.end_interval_hour,'08',s.value,0)) h08
|
|
, SUM(DECODE(s.end_interval_hour,'09',s.value,0)) h09
|
|
, SUM(DECODE(s.end_interval_hour,'10',s.value,0)) h10
|
|
, SUM(DECODE(s.end_interval_hour,'11',s.value,0)) h11
|
|
, SUM(DECODE(s.end_interval_hour,'12',s.value,0)) h12
|
|
, SUM(DECODE(s.end_interval_hour,'13',s.value,0)) h13
|
|
, SUM(DECODE(s.end_interval_hour,'14',s.value,0)) h14
|
|
, SUM(DECODE(s.end_interval_hour,'15',s.value,0)) h15
|
|
, SUM(DECODE(s.end_interval_hour,'16',s.value,0)) h16
|
|
, SUM(DECODE(s.end_interval_hour,'17',s.value,0)) h17
|
|
, SUM(DECODE(s.end_interval_hour,'18',s.value,0)) h18
|
|
, SUM(DECODE(s.end_interval_hour,'19',s.value,0)) h19
|
|
, SUM(DECODE(s.end_interval_hour,'20',s.value,0)) h20
|
|
, SUM(DECODE(s.end_interval_hour,'21',s.value,0)) h21
|
|
, SUM(DECODE(s.end_interval_hour,'22',s.value,0)) h22
|
|
, SUM(DECODE(s.end_interval_hour,'23',s.value,0)) h23
|
|
FROM awr_stats s
|
|
GROUP BY s.end_interval_day
|
|
ORDER BY s.end_interval_day
|
|
/
|
|
|
|
UNDEFINE days
|
|
UNDEFINE INST_ID
|
|
|
|
|
|
@@footer
|