/
acquire_aspac.sql
133 lines (124 loc) · 4.25 KB
/
acquire_aspac.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
REM
REM Script: acquire_aspac.sql
REM Author: Quanwen Zhao
REM Dated: Nov 28, 2021
REM
REM Last tested:
REM 11.2.0.4
REM 19.3.0.0
REM 21.3.0.0
REM
REM Purpose:
REM Visualizing the oracle performance graph "Active Sessions Per Activity Class" (ASPAC) from EMCC 13.5 in last 1 hour and 1 minute
REM by the user defined report of SQL Developer.
REM
REM References:
REM https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SYSMETRIC.html#GUID-623748C3-F765-4149-8378-F5CDAD59909A
REM https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-WAITCLASSMETRIC.html#GUID-A73F50B3-67F4-4F34-B332-402CC29A8011
REM https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SYSTEM_WAIT_CLASS.html#GUID-142948EB-58E8-4FCE-9FD3-80DC179733C6
REM https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SYSMETRIC_HISTORY.html#GUID-5560D15E-9F02-4300-B4DD-85A88A280392
REM https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-WAITCLASSMETRIC_HISTORY.html#GUID-854BB495-19FC-4EB4-A81C-4D0EEA13B83C
REM
--
-- http://gongju.chinaadmin.cn/tupianquse/
--
-- Each Legend Color from the Graph of "Active Sessions Per Activity Class" of EMCC 13.5.
--
-- CPU , #00CF30 -> RGB (0 , 207, 48 )
-- User I/O, #004CE6 -> RGB (0 , 76 , 230)
-- Wait , #FA5F00 -> RGB (250, 95 , 0 )
-- Active Sessions Per Activity Class (CPU, User I/O and Wait) from EMCC 13.5 in Last 1 Hour.
SET LINESIZE 200
SET PAGESIZE 200
COLUMN sample_time FORMAT a11
COLUMN metric_name FORMAT a11
COLUMN active_sessions FORMAT 999,999.9999
WITH
cpu AS
(
SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
, DECODE(metric_name, 'CPU Usage Per Sec', 'CPU') metric_name
, ROUND(value/1e2, 4) active_sessions
FROM v$sysmetric_history
WHERE metric_name = 'CPU Usage Per Sec'
AND group_id = 2
AND end_time >= SYSDATE - INTERVAL '60' MINUTE
ORDER BY sample_time
),
user_io AS
(
SELECT TO_CHAR(wcmh.end_time, 'hh24:mi:ss') sample_time
, swc.wait_class metric_name
, ROUND(wcmh.time_waited/wcmh.intsize_csec, 4) active_sessions
FROM v$waitclassmetric_history wcmh
, v$system_wait_class swc
WHERE wcmh.wait_class_id = swc.wait_class_id
AND swc.wait_class = 'User I/O'
AND wcmh.end_time >= SYSDATE - INTERVAL '60' MINUTE
ORDER BY sample_time
),
wait AS
(
SELECT TO_CHAR(wcmh.end_time, 'hh24:mi:ss') sample_time
, 'Wait' metric_name
, SUM(ROUND(wcmh.time_waited/wcmh.intsize_csec, 4)) active_sessions
FROM v$waitclassmetric_history wcmh
, v$system_wait_class swc
WHERE wcmh.wait_class_id = swc.wait_class_id
AND (swc.wait_class NOT IN ('Idle', 'User I/O'))
AND wcmh.end_time >= SYSDATE - INTERVAL '60' MINUTE
GROUP BY TO_CHAR(wcmh.end_time, 'hh24:mi:ss')
ORDER BY sample_time
)
SELECT * FROM cpu
UNION ALL
SELECT * FROM user_io
UNION ALL
SELECT * FROM wait
;
-- Active Sessions Per Activity Class (CPU, User I/O and Wait) from EMCC 13.5 in Last 1 Minute.
SET LINESIZE 200
SET PAGESIZE 10
COLUMN sample_time FORMAT a11
COLUMN metric_name FORMAT a11
COLUMN active_sessions FORMAT 999,999.99
WITH
cpu AS
(
SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
, DECODE(metric_name, 'CPU Usage Per Sec', 'CPU') metric_name
, ROUND(value/1e2, 2) active_sessions
FROM v$sysmetric
WHERE metric_name = 'CPU Usage Per Sec'
AND group_id = 2
ORDER BY sample_time
),
user_io AS
(
SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
, swc.wait_class metric_name
, ROUND(wcm.time_waited/wcm.intsize_csec, 2) active_sessions
FROM v$waitclassmetric wcm
, v$system_wait_class swc
WHERE wcm.wait_class_id = swc.wait_class_id
AND swc.wait_class = 'User I/O'
ORDER BY sample_time
),
wait AS
(
SELECT TO_CHAR(end_time, 'hh24:mi:ss') sample_time
, 'Wait' metric_name
, SUM(ROUND(wcm.time_waited/wcm.intsize_csec, 2)) active_sessions
FROM v$waitclassmetric wcm
, v$system_wait_class swc
WHERE wcm.wait_class_id = swc.wait_class_id
AND (swc.wait_class NOT IN ('Idle', 'User I/O'))
GROUP BY TO_CHAR(end_time, 'hh24:mi:ss')
ORDER BY sample_time
)
SELECT * FROM cpu
UNION ALL
SELECT * FROM user_io
UNION ALL
SELECT * FROM wait
;