-
Notifications
You must be signed in to change notification settings - Fork 241
/
default-asm-metrics.toml
139 lines (135 loc) · 7 KB
/
default-asm-metrics.toml
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
[[metric]]
context = "diskgroup_size"
labels = [ "inst_id", "node_name" , "instance_name", "diskgroup_name" ]
metricsdesc = { total = "Total size of ASM disk group in MB.", free = "Free space available on ASM disk group in MB." }
request = '''
SELECT instance_number AS inst_id,
host_name AS node_name,
instance_name,
name AS diskgroup_name,
total_mb * 1024 * 1024 AS total,
free_mb * 1024 * 1024 AS free
FROM v$asm_diskgroup_stat, v$instance
'''
ignorezeroresult = true
[[metric]]
context = "asmuptime"
labels = [ "inst_id", "node_name", "instance_name"]
metricsdesc = { uptime = "ASM uptime" }
request = '''
SELECT instance_number AS inst_id,
host_name AS node_name,
instance_name,
(SYSDATE - startup_time) * 86400 AS uptime
FROM v$instance
'''
#[[metric]]
#context = "asm_dg_stat"
#labels = [ "inst_id", "diskgroup_name", "node_name", "instance_name" ]
#metricsdesc = { reads = "Total number of I/O read requests for the DG.", writes = "Total number of I/O write requests for the DG.", bytes_read = "Total number of bytes read from the DG", bytes_written = "Total number of bytes written from the DG", iops = "Total number of I/O requests for the DG" }
#metricstype = { reads = "counter", writes = "counter", bytes_read = "counter", bytes_written = "counter", iops = "counter" }
#request = '''
# SELECT i.instance_number AS inst_id,
# i.host_name AS node_name,
# i.instance_name,
# g.name AS diskgroup_name,
# SUM (ds.reads) AS reads,
# SUM (ds.writes) AS writes,
# SUM (ds.bytes_read) AS bytes_read,
# SUM (ds.bytes_written) AS bytes_written,
# SUM (ds.reads + ds.writes) AS iops
# FROM v$asm_disk_stat ds, v$asm_diskgroup_stat g, v$instance i
# WHERE ds.mount_status = 'CACHED' AND ds.group_number = g.group_number
#GROUP BY i.instance_number,
# i.host_name,
# i.instance_name,
# g.name
#'''
[[metric]]
context = "asm_disk_stat"
labels = [ "inst_id", "node_name", "instance_name", "diskgroup_name", "disk_number", "failgroup", "path" ]
metricsdesc = { reads = "Total number of I/O read requests for the DG.", writes = "Total number of I/O write requests for the DG.", read_time = "Total I/O time (in hundreths of a second) for read requests for the disk", write_time = "Total I/O time (in hundreths of a second) for write requests for the disk", bytes_read = "Total number of bytes read from the DG", bytes_written = "Total number of bytes written from the DG", iops = "Total number of I/O requests for the DG" }
metricstype = { reads = "counter", writes = "counter", bytes_read = "counter", read_time = "counter", write_time = "counter", bytes_written = "counter", iops = "counter" }
request = '''
SELECT i.instance_number AS inst_id,
i.host_name AS node_name,
i.instance_name,
g.name AS diskgroup_name,
ds.disk_number AS disk_number,
ds.failgroup AS failgroup,
ds.reads AS reads,
ds.writes AS writes,
ds.read_time * 1000 AS read_time,
ds.write_time * 1000 AS write_time,
ds.bytes_read AS bytes_read,
ds.bytes_written AS bytes_written,
REGEXP_REPLACE (ds.PATH, '.*/\', '\') AS PATH,
ds.reads + ds.writes AS iops
FROM v$asm_disk_stat ds, v$asm_diskgroup_stat g, v$instance i
WHERE ds.mount_status = 'CACHED' AND ds.group_number = g.group_number
'''
[[metric]]
context = "asm_space_consumers"
labels = [ "inst_id", "diskgroup_name", "node_name", "instance_name", "sid", "file_type" ]
metricsdesc = { size_mb = "Total space usage by db by file_type" , files = "Number of files by db by type" }
request = '''
SELECT i.instance_number AS inst_id,
i.host_name AS node_name,
i.instance_name,
gname AS diskgroup_name,
dbname AS sid,
file_type,
ROUND (SUM (space) / 1024 / 1024) size_mb,
COUNT (*) AS files
FROM v$instance i,
(SELECT gname,
REGEXP_SUBSTR (full_alias_path,
'[[:alnum:]_]*',
1,
4) dbname,
file_type,
space,
aname,
system_created,
alias_directory
FROM ( SELECT CONCAT ('+' || gname,
SYS_CONNECT_BY_PATH (aname, '/'))
full_alias_path,
system_created,
alias_directory,
file_type,
space,
LEVEL,
gname,
aname
FROM (SELECT b.name gname,
a.parent_index pindex,
a.name aname,
a.reference_index rindex,
a.system_created,
a.alias_directory,
c.TYPE file_type,
c.space
FROM v$asm_alias a, v$asm_diskgroup b, v$asm_file c
WHERE a.group_number = b.group_number
AND a.group_number = c.group_number(+)
AND a.file_number = c.file_number(+)
AND a.file_incarnation = c.incarnation(+))
START WITH (MOD (pindex, POWER (2, 24))) = 0
AND rindex IN
(SELECT a.reference_index
FROM v$asm_alias a, v$asm_diskgroup b
WHERE a.group_number =
b.group_number
AND (MOD (a.parent_index,
POWER (2, 24))) =
0)
CONNECT BY PRIOR rindex = pindex)
WHERE NOT file_type IS NULL AND system_created = 'Y')
GROUP BY i.instance_number,
i.host_name,
i.instance_name,
gname,
dbname,
file_type
'''