forked from carlos-sierra/cscripts
-
Notifications
You must be signed in to change notification settings - Fork 0
/
cs_all_sysmetric_for_cdb_hist.sql
executable file
·130 lines (130 loc) · 4.5 KB
/
cs_all_sysmetric_for_cdb_hist.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
----------------------------------------------------------------------------------------
--
-- File name: cs_all_sysmetric_for_cdb_hist.sql
--
-- Purpose: All System Metrics as per DBA_HIST_SYSMETRIC_SUMMARY View for a CDB (text report)
--
-- Author: Carlos Sierra
--
-- Version: 2021/04/06
--
-- Usage: Execute connected to CDB and pass range of AWR snapshots.
--
-- Example: $ sqlplus / as sysdba
-- SQL> @cs_all_sysmetric_for_cdb_hist.sql
--
-- Notes: Stand-alone script
--
-- Developed and tested on 12.1.0.2 and 19c.
--
---------------------------------------------------------------------------------------
--
DEF view_name = 'dba_hist_sysmetric_summary';
DEF common_predicate = "";
DEF script_name = 'cs_all_sysmetric_for_cdb_hist';
--
COL cs_date NEW_V cs_date NOPRI;
COL cs_host NEW_V cs_host NOPRI;
COL cs_db NEW_V cs_db NOPRI;
COL cs_con_name NEW_V cs_con_name NOPRI;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD"T"HH24:MI:SS') AS cs_date, SYS_CONTEXT('USERENV','HOST') AS cs_host, UPPER(name) AS cs_db, SYS_CONTEXT('USERENV', 'CON_NAME') AS cs_con_name FROM v$database;
--
PRO
PRO Specify the number of days of snapshots to choose from
PRO
PRO Enter number of days: [{1}|0-60]
DEF num_days = '&1.'
UNDEF 1;
--
COL snap_id NEW_V snap_id FOR A7;
COL prior_snap_id NEW_V prior_snap_id FOR A7 NOPRI;
SELECT LPAD(TO_CHAR(snap_id), 7, ' ') AS snap_id, CAST(end_interval_time AS DATE) AS snap_time, LPAD(TO_CHAR(snap_id - 1), 7, ' ') AS prior_snap_id
FROM dba_hist_snapshot
WHERE instance_number = SYS_CONTEXT('USERENV','INSTANCE')
AND dbid = (SELECT dbid FROM v$database)
AND CAST(end_interval_time AS DATE) > SYSDATE - TO_NUMBER(NVL('&&num_days.', '1'))
ORDER BY
snap_id
/
--
PRO
PRO Enter begin snap_id: [{&&prior_snap_id.}]
DEF begin_snap_id = '&2.';
UNDEF 2;
COL begin_snap_id NEW_V begin_snap_id NOPRI;
SELECT NVL('&&begin_snap_id.', '&&prior_snap_id.') AS begin_snap_id FROM DUAL;
--
PRO
PRO Enter end snap_id: [{&&snap_id.}]
DEF end_snap_id = '&3.';
UNDEF 3;
COL end_snap_id NEW_V end_snap_id NOPRI;
SELECT NVL('&&end_snap_id.', '&&snap_id.') AS end_snap_id FROM DUAL;
--
COL cs_begin_time NEW_V cs_begin_time NOPRI;
COL cs_end_time NEW_V cs_end_time NOPRI;
COL cs_seconds NEW_V cs_seconds NOPRI;
SELECT TO_CHAR(MAX(CAST(end_interval_time AS DATE)), 'YYYY-MM-DD"T"HH24:MI:SS') AS cs_begin_time FROM dba_hist_snapshot WHERE snap_id = TO_NUMBER('&&begin_snap_id.');
SELECT TO_CHAR(MAX(CAST(end_interval_time AS DATE)), 'YYYY-MM-DD"T"HH24:MI:SS') AS cs_end_time FROM dba_hist_snapshot WHERE snap_id = TO_NUMBER('&&end_snap_id.');
SELECT TRIM(TO_CHAR(intsize/100, '999,999,990.00')) AS cs_seconds FROM &&view_name. WHERE snap_id > TO_NUMBER('&&begin_snap_id.') AND snap_id <= TO_NUMBER('&&end_snap_id.') AND ROWNUM = 1;
--
SET TERM ON HEA ON LIN 2490 PAGES 100 TAB OFF FEED OFF ECHO OFF VER OFF TRIMS ON TRIM ON TI OFF TIMI OFF LONG 240000 LONGC 2400 NUM 20 SERVEROUT OFF;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS';
COL report_date_time NEW_V report_date_time NOPRI;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD"T"HH24.MI.SS"Z"') AS report_date_time FROM DUAL;
--
-- @@cs_internal/&&cs_set_container_to_cdb_root.
ALTER SESSION SET container = CDB$ROOT;
--
SPO /tmp/&&script_name._&&report_date_time..txt
PRO /tmp/&&script_name._&&report_date_time..txt
PRO
PRO Date : &&cs_date.
PRO Host : &&cs_host.
PRO Database : &&cs_db.
--PRO Container: &&cs_con_name.
PRO Range : &&cs_begin_time. - &&cs_end_time. (&&cs_seconds. seconds)
--
COL metric_name FOR A45 TRUN;
COL metric_unit FOR A41 TRUN;
COL seconds FOR 9,900.00;
--
PRO
PRO System Metrics by Name (&&view_name.)
PRO ~~~~~~~~~~~~~~~~~~~~~~
SELECT metric_name,
AVG(average) AS average,
MAX(maxval) AS maxval,
metric_unit
FROM &&view_name.
WHERE &&common_predicate. snap_id > TO_NUMBER('&&begin_snap_id.') AND snap_id <= TO_NUMBER('&&end_snap_id.')
GROUP BY
metric_name, metric_unit
ORDER BY
metric_name, metric_unit
/
PRO
PRO System Metrics by Unit and Name (&&view_name.)
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT metric_unit,
metric_name,
AVG(average) AS average,
MAX(maxval) AS maxval
FROM &&view_name.
WHERE &&common_predicate. snap_id > TO_NUMBER('&&begin_snap_id.') AND snap_id <= TO_NUMBER('&&end_snap_id.')
GROUP BY
metric_unit, metric_name
ORDER BY
metric_unit, metric_name
/
--
PRO
PRO SQL> @&&script_name..sql
SPO OFF;
--
-- @@cs_internal/&&cs_set_container_to_curr_pdb.
ALTER SESSION SET CONTAINER = &&cs_con_name.;
--
PRO
PRO /tmp/&&script_name._&&report_date_time..txt
--