-
Notifications
You must be signed in to change notification settings - Fork 34
/
busiest_awr.sql
78 lines (78 loc) · 3.79 KB
/
busiest_awr.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
REM ================================================================================
REM Name: busiest_awr.sql
REM Type: Oracle SQL script
REM Date: 27-April 2020
REM From: Americas Customer Engineering team (CET) - Microsoft
REM
REM Copyright and license:
REM
REM Licensed under the Apache License, Version 2.0 (the "License"); you may
REM not use this file except in compliance with the License.
REM
REM You may obtain a copy of the License at
REM
REM http://www.apache.org/licenses/LICENSE-2.0
REM
REM Unless required by applicable law or agreed to in writing, software
REM distributed under the License is distributed on an "AS IS" basis,
REM WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
REM
REM See the License for the specific language governing permissions and
REM limitations under the License.
REM
REM Copyright (c) 2020 by Microsoft. All rights reserved.
REM
REM Ownership and responsibility:
REM
REM This script is offered without warranty by Microsoft Customer Engineering.
REM Anyone using this script accepts full responsibility for use, effect,
REM and maintenance. Please do not contact Microsoft or Oracle support unless
REM there is a problem with a supported SQL or SQL*Plus command.
REM
REM Description:
REM
REM SQL*Plus script to find the top 5 busiest AWR snapshots within the horizon
REM of all information stored within the Oracle AWR repository, based on the
REM AWR metrics "CPU Usage Per Sec" and "I/O Megabytes per Second" found in the
REM view DBA_HIST_SYSMETRIC_HISTORY.
REM
REM Modifications:
REM TGorman 27apr20 v0.1 written
REM TGorman 04may20 v0.2 removed NTILE, using only ROW_NUMBER now...
REM NBhandare 14May21 v0.3 added reference to innermost subqueries as fix for
REM instance restart...
REM TGorman 01jun21 v0.4 cleaned up some mistakes, parameterized
REM TGorman 09dec22 v0.5 changed query from using stats from DBA_HIST_SYSSTAT
REM to using metrics from DBA_HIST_SYSMETRIC_HISTORY
REM TGorman 12dec22 v0.6 cleaned up snap IDs and times
REM ================================================================================
set pages 100 lines 180 verify off echo off feedback 6 timing off recsep off
col instance_number format 90 heading 'I#'
col snap_id heading 'Beginning|Snap ID'
col begin_tm format a20 heading 'Beginning|Snap Time' word_wrap
col avg_value heading 'Average|IO and CPU|per second' format 999,999,990.0000
define V_CPU_WEIGHT=1 /* multiplicative factor to favor/disfavor CPU metrics */
define V_IO_WEIGHT=2 /* multiplicative factor to favor/disfavor I/O metrics */
spool b
select x.instance_number,
x.snap_id snap_id,
to_char(s.end_interval_time, 'DD-MON-YYYY HH24:MI:SS') begin_tm,
x.avg_value
from (select instance_number, snap_id, avg(value) avg_value, avg(sort_value) sort_value,
row_number() over (partition by instance_number order by avg(sort_value) desc) rn
from (select instance_number, snap_id, value, (value*&&V_CPU_WEIGHT) sort_value
from dba_hist_sysmetric_history
where metric_name = 'CPU Usage Per Sec'
and dbid = (select dbid from v$database)
union all
select instance_number, snap_id, value, (value*&&V_IO_WEIGHT) sort_value
from dba_hist_sysmetric_history
where metric_name = 'I/O Megabytes per Second'
and dbid = (select dbid from v$database))
group by instance_number, snap_id) x,
dba_hist_snapshot s
where s.snap_id = x.snap_id
and s.instance_number = x.instance_number
and rn <= 5
order by instance_number, rn;
spool off