-
Notifications
You must be signed in to change notification settings - Fork 64
/
cs_dba_hist_parameter.sql
executable file
·157 lines (156 loc) · 4.72 KB
/
cs_dba_hist_parameter.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
----------------------------------------------------------------------------------------
--
-- File name: cs_dba_hist_parameter.sql
--
-- Purpose: System Parameters History
--
-- Author: Carlos Sierra
--
-- Version: 2021/12/06
--
-- Usage: Execute connected to CDB or PDB
--
-- Enter range of dates and filters when requested.
--
-- Example: $ sqlplus / as sysdba
-- SQL> @cs_dba_hist_parameter.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_dba_hist_parameter';
DEF cs_hours_range_default = '1440';
--
@@cs_internal/cs_sample_time_from_and_to.sql
@@cs_internal/cs_snap_id_from_and_to.sql
--
COL parameter_name FOR A43;
COL dist_values FOR 999,990;
--
SELECT h.parameter_name, COUNT(DISTINCT h.value) AS dist_values
FROM dba_hist_parameter h
WHERE h.dbid = TO_NUMBER('&&cs_dbid.')
AND h.instance_number = TO_NUMBER('&&cs_instance_number.')
AND h.snap_id BETWEEN TO_NUMBER('&&cs_snap_id_from.') - 1 AND TO_NUMBER('&&cs_snap_id_to.')
AND &&cs_con_id. IN (1, h.con_id)
GROUP BY
h.parameter_name
HAVING COUNT(DISTINCT h.value) > 1
ORDER BY
h.parameter_name
/
PRO
PRO 3. Parameter Name (opt):
DEF parameter_name = '&3.';
UNDEF 3;
--
SELECT '&&cs_file_prefix._&&cs_script_name.' cs_file_name FROM DUAL;
--
@@cs_internal/cs_spool_head.sql
PRO SQL> @&&cs_script_name..sql "&&cs_sample_time_from." "&&cs_sample_time_to." "&¶meter_name."
@@cs_internal/cs_spool_id.sql
--
@@cs_internal/cs_spool_id_sample_time.sql
--
--@@cs_internal/&&cs_set_container_to_cdb_root.
--
PRO PARAMETER : "&¶meter_name."
--
PRO
PRO PARAMETERS CHANGED
PRO ~~~~~~~~~~~~~~~~~~
SELECT h.parameter_name, COUNT(DISTINCT h.value) AS dist_values
FROM dba_hist_parameter h
WHERE h.dbid = TO_NUMBER('&&cs_dbid.')
AND h.instance_number = TO_NUMBER('&&cs_instance_number.')
AND h.snap_id BETWEEN TO_NUMBER('&&cs_snap_id_from.') - 1 AND TO_NUMBER('&&cs_snap_id_to.')
AND &&cs_con_id. IN (1, h.con_id)
GROUP BY
h.parameter_name
HAVING COUNT(DISTINCT h.value) > 1
ORDER BY
h.parameter_name
/
COL begin_time FOR A19;
COL end_time FOR A19;
COL parameter_name FOR A43;
COL prior_value FOR A50 HEA 'BEGIN_VALUE';
COL value FOR A50 HEA 'END_VALUE';
COL change FOR 999,999,999,999,990 HEA 'NET_CHANGE';
COL con_id FOR 999990;
COL pdb_name FOR A30 TRUNC;
--
BREAK ON begin_time SKIP PAGE ON end_time;
--
PRO
PRO PARAMETERS CHANGES
PRO ~~~~~~~~~~~~~~~~~~
WITH
parameter_hist AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h.snap_id,
h.dbid,
h.instance_number,
h.parameter_name,
LAG(h.value) OVER (PARTITION BY h.dbid, h.instance_number, h.parameter_name, h.con_id ORDER BY h.snap_id) AS prior_value,
h.value,
h.con_id
FROM dba_hist_parameter h
WHERE h.dbid = TO_NUMBER('&&cs_dbid.')
AND h.instance_number = TO_NUMBER('&&cs_instance_number.')
AND h.snap_id BETWEEN TO_NUMBER('&&cs_snap_id_from.') - 1 AND TO_NUMBER('&&cs_snap_id_to.')
AND &&cs_con_id. IN (1, h.con_id)
AND ('&¶meter_name.' IS NULL OR UPPER(parameter_name) = UPPER('&¶meter_name.'))
),
parameter_changes AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h.snap_id,
h.dbid,
h.instance_number,
h.parameter_name,
h.prior_value,
h.value,
h.con_id,
s.begin_interval_time,
s.end_interval_time
FROM parameter_hist h,
dba_hist_snapshot s
WHERE NVL(h.value, '-666') <> NVL(h.prior_value, '-666')
AND h.snap_id BETWEEN TO_NUMBER('&&cs_snap_id_from.') AND TO_NUMBER('&&cs_snap_id_to.')
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
)
SELECT CAST(p.begin_interval_time AS DATE) AS begin_time,
CAST(p.end_interval_time AS DATE) AS end_time,
p.parameter_name,
p.prior_value,
p.value,
CASE WHEN REGEXP_LIKE(p.prior_value, '^[^a-zA-Z]*$') AND REGEXP_LIKE(p.value, '^[^a-zA-Z]*$') THEN TO_NUMBER(p.value) - TO_NUMBER(p.prior_value) END AS change, -- https://asktom.oracle.com/pls/apex/asktom.search?tag=determine-whether-the-given-is-numeric-alphanumeric-and-hexadecimal
p.con_id,
(SELECT c.name AS pdb_name FROM v$containers c WHERE c.con_id = p.con_id) AS pdb_name
FROM parameter_changes p
ORDER BY
p.begin_interval_time,
p.parameter_name,
p.con_id
/
--
CLEAR BREAK;
--
PRO
PRO SQL> @&&cs_script_name..sql "&&cs_sample_time_from." "&&cs_sample_time_to." "&¶meter_name."
--
@@cs_internal/cs_spool_tail.sql
--
--@@cs_internal/&&cs_set_container_to_curr_pdb.
--
@@cs_internal/cs_undef.sql
@@cs_internal/cs_reset.sql
--