-
Notifications
You must be signed in to change notification settings - Fork 64
/
cs_ash_awr_peaks_bubble.sql
executable file
·144 lines (144 loc) · 5.27 KB
/
cs_ash_awr_peaks_bubble.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
----------------------------------------------------------------------------------------
--
-- File name: cs_ash_awr_peaks_bubble.sql
--
-- Purpose: ASH Peaks Bubble from AWR
--
-- Author: Carlos Sierra
--
-- Version: 2022/05/25
--
-- Usage: Execute connected to CDB or PDB
--
-- Example: $ sqlplus / as sysdba
-- SQL> @cs_ash_awr_peaks_bubble.sql
--
-- Notes: Developed and tested on 19c.
--
---------------------------------------------------------------------------------------
--
@@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_ash_awr_peaks_bubble';
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 To chart on Active Sessions over 1x the number of CPU Cores, then pass "1" (default) as Threshold value below
PRO
PRO 3. Threshold: [{1}|0-10]
DEF times_cpu_cores = '&3.';
UNDEF 3;
COL times_cpu_cores NEW_V times_cpu_cores NOPRI;
SELECT CASE WHEN TO_NUMBER(REPLACE(UPPER('&×_cpu_cores.'), 'X')) BETWEEN 0 AND 10 THEN REPLACE(UPPER('&×_cpu_cores.'), 'X') ELSE '1' END AS times_cpu_cores FROM DUAL
/
PRO
PRO 4. Dimension [{GLOBAL}|SQL_ID|WAIT_CLASS|TIMED_EVENT|PDB]
DEF dimension = '&4.';
UNDEF 4;
COL dimension NEW_V dimension NOPRI;
SELECT CASE WHEN UPPER(TRIM('&&dimension.')) IN ('GLOBAL', 'SQL_ID', 'WAIT_CLASS', 'TIMED_EVENT', 'PDB') THEN UPPER(TRIM('&&dimension.')) ELSE 'GLOBAL' END AS dimension FROM DUAL
/
COL grouping_expression NEW_V grouping_expression NOPRI;
SELECT CASE '&&dimension.'
WHEN 'GLOBAL' THEN q'[object_type]'
WHEN 'SQL_ID' THEN q'[statement_id||' '||SUBSTR(remarks, 1, 50)]'
WHEN 'WAIT_CLASS' THEN q'[CASE operation WHEN 'ON CPU' THEN operation ELSE options END]'
WHEN 'TIMED_EVENT' THEN q'[CASE operation WHEN 'ON CPU' THEN operation ELSE options||' - '||object_node END]'
WHEN 'PDB' THEN q'[object_owner||'('||plan_id||')']'
END AS grouping_expression
FROM DUAL
/
--
@@cs_internal/&&cs_set_container_to_cdb_root.
--
DEF include_hist = 'Y';
DEF include_mem = 'N';
SET SERVEROUT OFF;
@@cs_internal/cs_active_sessions_peaks_internal_v5.sql
--
SELECT '&&cs_file_prefix._&&cs_script_name._&&dimension.' cs_file_name FROM DUAL;
--
DEF report_title = "Peaks duration of Active Sessions in Concurrency contention exceeding &×_cpu_cores.x CPU_CORES by top value - &&dimension.";
DEF chart_title = "&&report_title.";
DEF xaxis_title = "between &&cs_sample_time_from. and &&cs_sample_time_to.";
-- DEF hAxis_maxValue = "&&cs_hAxis_maxValue.";
-- DEF cs_trendlines_series = ", 0:{}, 1:{}, 2:{}, 3:{}, 4:{}, 5:{}";
DEF vaxis_title = "Maximum Active Sessions";
--
-- (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) Bubble size indicates duration of contention. Label shows the top#1 contributor.";
DEF chart_foot_note_3 = "";
DEF chart_foot_note_4 = "";
DEF report_foot_note = "";
DEF report_foot_note = 'SQL> @&&cs_script_name..sql "&&cs_sample_time_from." "&&cs_sample_time_to." "&×_cpu_cores." "&&dimension."';
--
DEF spool_chart_1st_column = 'ID';
@@cs_internal/cs_spool_head_chart.sql
PRO , 'Time', 'Total Maximum Active Sessions', 'Top#1 &&dimension.', 'Approximate duration in seconds'
PRO ]
--
SET HEA OFF PAGES 0;
/****************************************************************************************/
WITH
FUNCTION num_format (p_number IN NUMBER, p_round IN NUMBER DEFAULT 0)
RETURN VARCHAR2 IS
BEGIN
IF p_number IS NULL OR ROUND(p_number, p_round) <= 0 THEN
RETURN 'null';
ELSE
RETURN TO_CHAR(ROUND(p_number, p_round));
END IF;
END num_format;
/****************************************************************************************/
-- SELECT ', [''#'||ROW_NUMBER() OVER (ORDER BY cost DESC, timestamp)||' '||cost||''''||
SELECT ', ['''''||
', new Date('||
TO_CHAR(t.timestamp, 'YYYY')|| /* year */
','||(TO_NUMBER(TO_CHAR(t.timestamp, 'MM')) - 1)|| /* month - 1 */
','||TO_CHAR(t.timestamp, 'DD')|| /* day */
','||TO_CHAR(t.timestamp, 'HH24')|| /* hour */
','||TO_CHAR(t.timestamp, 'MI')|| /* minute */
','||TO_CHAR(t.timestamp, 'SS')|| /* second */
')'||
', '||num_format(t.cardinality, 0)|| -- sessions_peak
', '''||&&grouping_expression.||''''||
', '||num_format(t.cost, 0)|| -- seconds
']'
FROM plan_table t
ORDER BY
t.cost DESC
/
/****************************************************************************************/
SET HEA ON PAGES 100;
--
-- [Line|Area|SteppedArea|Scatter|Bubble]
DEF cs_chart_type = 'Bubble';
-- 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
--