default-asm-metrics.toml

[[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
'''