forked from carlos-sierra/cscripts
-
Notifications
You must be signed in to change notification settings - Fork 0
/
cs_blocked_sessions_by_machine_ash_awr_chart.sql
executable file
·206 lines (206 loc) · 8.73 KB
/
cs_blocked_sessions_by_machine_ash_awr_chart.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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
----------------------------------------------------------------------------------------
--
-- File name: cs_blocked_sessions_by_machine_ash_awr_chart.sql
--
-- Purpose: Top Session Blockers by Machine of Root Blocker as per ASH from AWR (time series chart)
--
-- Author: Carlos Sierra
--
-- Version: 2021/02/10
--
-- Usage: Execute connected to CDB or PDB
--
-- Enter range of dates and filters when requested.
--
-- Example: $ sqlplus / as sysdba
-- SQL> @cs_blocked_sessions_by_machine_ash_awr_chart.sql
--
-- Notes: Developed and tested on 12.1.0.2.
--
---------------------------------------------------------------------------------------
--
@@cs_internal/cs_primary.sql
@@cs_internal/cs_set.sql
@@cs_internal/cs_def.sql
@@cs_internal/cs_file_prefix.sql
--
DEF cs_script_name = 'cs_blocked_sessions_by_machine_ash_awr_chart';
DEF cs_hours_range_default = '24';
--
@@cs_internal/cs_sample_time_from_and_to.sql
@@cs_internal/cs_snap_id_from_and_to.sql
--
PRO 3. Root Blocker State: [{ANY}|ACTIVE|INACTIVE|ACTIVE ON CPU|ACTIVE WAITING|UNKNOWN]
DEF root_blocker_state = '&3.';
UNDEF 3;
COL root_blocker_state NEW_V root_blocker_state NOPRI;
SELECT CASE WHEN UPPER(TRIM('&&root_blocker_state.')) IN ('ANY', 'ACTIVE', 'INACTIVE', 'ACTIVE ON CPU', 'ACTIVE WAITING', 'UNKNOWN') THEN UPPER(TRIM('&&root_blocker_state.')) ELSE 'ANY' END AS root_blocker_state FROM DUAL
/
--
--@@cs_internal/&&cs_set_container_to_cdb_root.
--
COL machine_01 NEW_V machine_01 NOPRI;
COL machine_02 NEW_V machine_02 NOPRI;
COL machine_03 NEW_V machine_03 NOPRI;
COL machine_04 NEW_V machine_04 NOPRI;
COL machine_05 NEW_V machine_05 NOPRI;
COL machine_06 NEW_V machine_06 NOPRI;
COL machine_07 NEW_V machine_07 NOPRI;
COL machine_08 NEW_V machine_08 NOPRI;
COL machine_09 NEW_V machine_09 NOPRI;
COL machine_10 NEW_V machine_10 NOPRI;
COL machine_11 NEW_V machine_11 NOPRI;
COL machine_12 NEW_V machine_12 NOPRI;
--
SET TERM OFF;
GET cs_internal/cs_blocked_sessions_ash_awr_internal.sql NOLIST
.
666666 ,
666666 by_sessions_sum AS (
666666 SELECT /*+ MATERIALIZE NO_MERGE */
666666 machine,
666666 ROW_NUMBER() OVER (ORDER BY SUM(CASE '&&root_blocker_state.'
666666 WHEN 'ANY' THEN sessions_blocked
666666 WHEN 'ACTIVE' THEN active_on_cpu + active_waiting
666666 WHEN 'INACTIVE' THEN inactive
666666 WHEN 'ACTIVE ON CPU' THEN active_on_cpu
666666 WHEN 'ACTIVE WAITING' THEN active_waiting
666666 WHEN 'UNKNOWN' THEN unknown
666666 END
666666 ) DESC NULLS LAST) top_sum
666666 FROM blockers_and_blockees
666666 WHERE sessions_blocked > 0
666666 --AND machine <> 'unknown'
666666 GROUP BY
666666 machine
666666 )
666666 SELECT MAX(CASE top_sum WHEN 01 THEN machine END) machine_01,
666666 MAX(CASE top_sum WHEN 02 THEN machine END) machine_02,
666666 MAX(CASE top_sum WHEN 03 THEN machine END) machine_03,
666666 MAX(CASE top_sum WHEN 04 THEN machine END) machine_04,
666666 MAX(CASE top_sum WHEN 05 THEN machine END) machine_05,
666666 MAX(CASE top_sum WHEN 06 THEN machine END) machine_06,
666666 MAX(CASE top_sum WHEN 07 THEN machine END) machine_07,
666666 MAX(CASE top_sum WHEN 08 THEN machine END) machine_08,
666666 MAX(CASE top_sum WHEN 09 THEN machine END) machine_09,
666666 MAX(CASE top_sum WHEN 10 THEN machine END) machine_10,
666666 MAX(CASE top_sum WHEN 11 THEN machine END) machine_11,
666666 MAX(CASE top_sum WHEN 12 THEN machine END) machine_12
666666 FROM by_sessions_sum
666666 WHERE top_sum BETWEEN 1 AND 12;
SET TERM ON;
/
--
SELECT '&&cs_file_prefix._&&cs_script_name.' cs_file_name FROM DUAL;
--
DEF report_title = 'Sessions Blocked by Machine of Root Blocker between &&cs_sample_time_from. and &&cs_sample_time_to. UTC';
DEF chart_title = '&&report_title.';
DEF xaxis_title = 'Root Blocker State: &&root_blocker_state.';
DEF vaxis_title = 'Blocked Sessions Count';
--
-- (isStacked is true and baseline is null) or (not isStacked and baseline >= 0)
DEF is_stacked = "isStacked: false,";
--DEF is_stacked = "isStacked: true,";
--DEF vaxis_baseline = ", baseline:&&cs_num_cpu_cores., baselineColor:'red'";
DEF vaxis_baseline = "";
DEF chart_foot_note_2 = '<br>2) "INACTIVE" means: Database is waiting for Application Host to release LOCK.';
DEF chart_foot_note_3 = "<br>";
--DEF chart_foot_note_3 = "";
DEF chart_foot_note_4 = "";
DEF report_foot_note = 'SQL> @&&cs_script_name..sql "&&cs_sample_time_from." "&&cs_sample_time_to." "&&root_blocker_state."';
--
@@cs_internal/cs_spool_head_chart.sql
--
PRO ,{label:'&&machine_01.', id:'01', type:'number'}
PRO ,{label:'&&machine_02.', id:'02', type:'number'}
PRO ,{label:'&&machine_03.', id:'03', type:'number'}
PRO ,{label:'&&machine_04.', id:'04', type:'number'}
PRO ,{label:'&&machine_05.', id:'05', type:'number'}
PRO ,{label:'&&machine_06.', id:'06', type:'number'}
PRO ,{label:'&&machine_07.', id:'07', type:'number'}
PRO ,{label:'&&machine_08.', id:'08', type:'number'}
PRO ,{label:'&&machine_09.', id:'09', type:'number'}
PRO ,{label:'&&machine_10.', id:'10', type:'number'}
PRO ,{label:'&&machine_11.', id:'11', type:'number'}
PRO ,{label:'&&machine_12.', id:'12', type:'number'}
PRO ]
--
SET HEA OFF PAGES 0;
--
SET TERM OFF;
GET cs_internal/cs_blocked_sessions_ash_awr_internal.sql NOLIST
.
666666 ,
666666 by_sessions_sum AS (
666666 SELECT /*+ MATERIALIZE NO_MERGE */
666666 time,
666666 machine,
666666 SUM(CASE '&&root_blocker_state.'
666666 WHEN 'ANY' THEN sessions_blocked
666666 WHEN 'ACTIVE' THEN active_on_cpu + active_waiting
666666 WHEN 'INACTIVE' THEN inactive
666666 WHEN 'ACTIVE ON CPU' THEN active_on_cpu
666666 WHEN 'ACTIVE WAITING' THEN active_waiting
666666 WHEN 'UNKNOWN' THEN unknown
666666 END
666666 ) AS sessions_blocked
666666 FROM blockers_and_blockees
666666 WHERE sessions_blocked > 0
666666 --AND machine <> 'unknown'
666666 GROUP BY
666666 time,
666666 machine
666666 )
666666 SELECT ', [new Date('||
666666 TO_CHAR(q.time, 'YYYY')|| /* year */
666666 ','||(TO_NUMBER(TO_CHAR(q.time, 'MM')) - 1)|| /* month - 1 */
666666 ','||TO_CHAR(q.time, 'DD')|| /* day */
666666 ','||TO_CHAR(q.time, 'HH24')|| /* hour */
666666 ','||TO_CHAR(q.time, 'MI')|| /* minute */
666666 ','||TO_CHAR(q.time, 'SS')|| /* second */
666666 ')'||
666666 ','||num_format(SUM(CASE q.machine WHEN '&&machine_01.' THEN q.sessions_blocked ELSE 0 END))||
666666 ','||num_format(SUM(CASE q.machine WHEN '&&machine_02.' THEN q.sessions_blocked ELSE 0 END))||
666666 ','||num_format(SUM(CASE q.machine WHEN '&&machine_03.' THEN q.sessions_blocked ELSE 0 END))||
666666 ','||num_format(SUM(CASE q.machine WHEN '&&machine_04.' THEN q.sessions_blocked ELSE 0 END))||
666666 ','||num_format(SUM(CASE q.machine WHEN '&&machine_05.' THEN q.sessions_blocked ELSE 0 END))||
666666 ','||num_format(SUM(CASE q.machine WHEN '&&machine_06.' THEN q.sessions_blocked ELSE 0 END))||
666666 ','||num_format(SUM(CASE q.machine WHEN '&&machine_07.' THEN q.sessions_blocked ELSE 0 END))||
666666 ','||num_format(SUM(CASE q.machine WHEN '&&machine_08.' THEN q.sessions_blocked ELSE 0 END))||
666666 ','||num_format(SUM(CASE q.machine WHEN '&&machine_09.' THEN q.sessions_blocked ELSE 0 END))||
666666 ','||num_format(SUM(CASE q.machine WHEN '&&machine_10.' THEN q.sessions_blocked ELSE 0 END))||
666666 ','||num_format(SUM(CASE q.machine WHEN '&&machine_11.' THEN q.sessions_blocked ELSE 0 END))||
666666 ','||num_format(SUM(CASE q.machine WHEN '&&machine_12.' THEN q.sessions_blocked ELSE 0 END))||
666666 ']'
666666 FROM by_sessions_sum q
666666 GROUP BY
666666 q.time
666666 ORDER BY
666666 q.time;
SET TERM ON;
/
/****************************************************************************************/
SET HEA ON PAGES 100;
--
-- [Line|Area|SteppedArea|Scatter]
DEF cs_chart_type = 'Scatter';
-- disable explorer with "//" when using Pie
DEF cs_chart_option_explorer = '';
-- enable pie options with "" when using Pie
DEF cs_chart_option_pie = '//';
-- use oem colors
DEF cs_oem_colors_series = '//';
DEF cs_oem_colors_slices = '//';
-- for line charts
DEF cs_curve_type = '//';
--
@@cs_internal/cs_spool_id_chart.sql
@@cs_internal/cs_spool_tail_chart.sql
PRO
PRO &&report_foot_note.
--
--@@cs_internal/&&cs_set_container_to_curr_pdb.
--
@@cs_internal/cs_undef.sql
@@cs_internal/cs_reset.sql
--