default-metrics.yaml

metrics:
  - context: "sessions"
    labels: [ "inst_id", "status", "type" ]
    metricsdesc:
      value: "Gauge metric with count of sessions by inst_id, status and type."
    request: |
      select inst_id, status, type, count(*) as value
      from gv$session
      group by inst_id, status, type

  - context: "resource"
    labels: [ "inst_id", "resource_name" ]
    metricsdesc:
      current_utilization: "Generic counter metric from gv$resource_limit view in Oracle (current value)."
      limit_value: "Generic counter metric from v$resource_limit view in Oracle (UNLIMITED: -1)."
    request: |
      select inst_id, resource_name, current_utilization,
             case when trim(limit_value) like 'UNLIMITED' then '-1' else trim(limit_value) end as limit_value
      from gv$resource_limit
      group by inst_id, resource_name, current_utilization, limit_value
    ignorezeroresult: true

  - context: "asm_diskgroup"
    labels: [ "inst_id", "name" ]
    metricsdesc:
      total: "Total size of ASM disk group."
      free: "Free space available on ASM disk group."
    request: |
      select inst_id, name, total_mb*1024*1024 as total, free_mb*1024*1024 as free
      from gv$asm_diskgroup_stat
      where exists (select 1 from gv$datafile where name like '+%')
      and inst_id = (select max(inst_id) from gv$instance)
      group by inst_id, name, total_mb, free_mb
    ignorezeroresult: true

  - context: "activity"
    labels: [ "inst_id" ]
    metricsdesc:
      value: "Generic counter metric from gv$sysstat view in Oracle."
    fieldtoappend: "name"
    request: |
      select inst_id, name, value from gv$sysstat
      where name in ('parse count (total)', 'execute count', 'user commits', 'user rollbacks')
      group by inst_id, name, value

  - context: "process"
    labels: [ "inst_id" ]
    metricsdesc:
      count: "Gauge metric with count of processes."
    request: |
      select inst_id, count(*) as count
      from gv$process
      group by inst_id

  - context: "wait_time"
    labels: [ "inst_id", "wait_class", "con_id" ]
    metricsdesc:
      time_waited_sec_total: "counter metric from system_wait_class view in Oracle."
    metricstype:
      time_waited_sec_total: "counter"
    fieldtoappend: "wait_class"
    request: |
      select
          inst_id,
          wait_class,
          round(time_waited/100,3) time_waited_sec_total,
          con_id
      from gv$system_wait_class
      where wait_class <> 'Idle'
      group by inst_id, wait_class, con_id, round(time_waited/100,3)
    ignorezeroresult: true

  - context: "tablespace"
    labels: [ "tablespace", "type" ]
    metricsdesc:
      bytes: "Generic counter metric of tablespaces bytes in Oracle."
      max_bytes: "Generic counter metric of tablespaces max bytes in Oracle."
      free: "Generic counter metric of tablespaces free bytes in Oracle."
      used_percent: "Gauge metric showing as a percentage of how much of the tablespace has been used."
    request: |
      SELECT
          dt.tablespace_name as tablespace,
          dt.contents as type,
          dt.block_size * dtum.used_space as bytes,
          dt.block_size * dtum.tablespace_size as max_bytes,
          dt.block_size * (dtum.tablespace_size - dtum.used_space) as free,
          dtum.used_percent
      FROM  dba_tablespace_usage_metrics dtum, dba_tablespaces dt
      WHERE dtum.tablespace_name = dt.tablespace_name
      and dt.contents != 'TEMPORARY'
      union
      SELECT
          dt.tablespace_name as tablespace,
          'TEMPORARY' as type,
          dt.tablespace_size - dt.free_space as bytes,
          dt.tablespace_size as max_bytes,
          dt.free_space as free,
          ((dt.tablespace_size - dt.free_space) / dt.tablespace_size)
      FROM  dba_temp_free_space dt
      order by tablespace

  - context: "db_system"
    labels: [ "inst_id", "name" ]
    metricsdesc:
      value: "Database system resources metric"
    request: |
      select inst_id, name, value
      from gv$parameter
      where name in ('cpu_count', 'sga_max_size', 'pga_aggregate_limit')
      group by inst_id, name, value

  - context: "db_platform"
    labels: [ "inst_id", "platform_name" ]
    metricsdesc:
      value: "Database platform"
    request: |
      SELECT inst_id, platform_name, 1 as value
      FROM gv$database
      GROUP BY inst_id, platform_name

  - context: "top_sql"
    labels: [ "inst_id", "sql_id", "sql_text" ]
    metricsdesc:
      elapsed: "SQL statement elapsed time running"
    request: |
      select * from (
      select inst_id, sql_id, elapsed_time / 1000000 as elapsed, substrb(replace(sql_text,'',' '),1,55) as sql_text
      from   gv$sqlstats
      order by elapsed_time desc
      ) where rownum <= 15
    ignorezeroresult: true
    querytimeout: "10s"
    # scrapeinterval: "5m"

  - context: "cache_hit_ratio"
    labels: [ "inst_id", "cache_hit_type" ]
    metricsdesc:
      value: "Cache Hit Ratio"
    request: |
      select inst_id,
      metric_name as cache_hit_type,
      value
      from gv$con_sysmetric
      where metric_name in (
      'Buffer Cache Hit Ratio',
      'Cursor Cache Hit Ratio'
      )
    ignorezeroresult: true