Oracle Database Monitoring Example

# Collection of metrics and stats for Oracle database server.
#
# Metrics and queries are inspired by https://github.com/iamseth/oracledb_exporter

databases:
  oracle:
    dsn: !env ORACLE_DATABASE_DSN

metrics:
  oracle_sessions:
    type: gauge
    description: Number of sessions
    labels: [status, type]
  oracle_resouce_current_utilization:
    type: counter
    description: Current value from v$resource_limit view
    labels: [name]
  oracle_resource_limit:
    type: counter
    description: Limit value from v$resource_limit view
    labels: [name]
  oracle_asm_diskgroup_total:
    type: counter
    description: Total size of ASM disk group
    labels: [name]
  oracle_asm_diskgroup_free:
    type: counter
    description: Free space on ASM disk group
    labels: [name]
  oracle_activity:
    type: counter
    labels: [name]
    description: Database activity from v$sysstat view
  oracle_process:
    type: gauge
    description: Process count
  oracle_wait_time:
    type: counter
    description: Wait time from v$waitclassmetric view
    labels: [wait_class]
  oracle_tablespace_bytes:
    type: counter
    description: Tablespaces size
    labels: [tablespace, type]
  oracle_tablespace_max_bytes:
    type: counter
    description: Tablespaces max size
    labels: [tablespace, type]
  oracle_tablespace_free:
    type: counter
    description: Tablespaces free size
    labels: [tablespace, type]

queries:
  session_stats:
    databases: [oracle]
    metrics:
      - oracle_sessions
    sql: |
      SELECT
        status,
        type,
        COUNT(*) AS oracle_sessions
      FROM v$session
      GROUP BY status, type

  resource_stats:
    databases: [oracle]
    metrics:
      - oracle_resouce_current_utilization
      - oracle_resource_limit
    sql: |
      SELECT
        resource_name AS name,
        current_utilization AS oracle_resouce_current_utilization,
        limit_value AS oracle_resource_limit
      FROM v$resource_limit

  activity_stats:
    databases: [oracle]
    metrics:
      - oracle_activity
    sql: |
      SELECT
        name,
        value AS oracle_activity
      FROM v$sysstat
      WHERE name IN ('parse count (total)', 'execute count', 'user commits', 'user rollbacks')

  process_stats:
    databases: [oracle]
    metrics:
      - oracle_process
    sql: |
      SELECT COUNT(*) AS oracle_process FROM v$process

  wait_time_stats:
    databases: [oracle]
    metrics:
      - oracle_wait_time
    sql: |
      SELECT
        wait_class AS wait_class,
        time_waited AS oracle_wait_time
      FROM v$waitclassmetric
      WHERE wait_class != 'Idle'

  tablespace_stats:
    databases: [oracle]
    metrics:
      - oracle_tablespace_bytes
      - oracle_tablespace_max_bytes
      - oracle_tablespace_free
    sql: |
      SELECT
        z.name AS tablespace,
        dt.contents AS type,
        z.bytes AS oracle_tablespace_bytes,
        z.max_bytes AS oracle_tablespace_max_bytes,
        z.free_bytes AS oracle_tablespace_free
      FROM
        (
          SELECT
            x.name AS name,
            SUM(nvl(x.free_bytes,0)) AS free_bytes,
            SUM(x.bytes) AS bytes,
            SUM(x.max_bytes) AS max_bytes
          FROM
            (
              SELECT
                ddf.tablespace_name AS name,
                ddf.bytes AS bytes,
                sum(coalesce(dfs.bytes, 0)) AS free_bytes,
                CASE
                  WHEN ddf.maxbytes = 0 THEN ddf.bytes
                  ELSE ddf.maxbytes
                END AS max_bytes
              FROM
                sys.dba_data_files ddf,
                sys.dba_tablespaces dt,
                sys.dba_free_space dfs
              WHERE ddf.tablespace_name = dt.tablespace_name
              AND ddf.file_id = dfs.file_id(+)
              GROUP BY ddf.tablespace_name, ddf.file_name, ddf.status, ddf.bytes, ddf.maxbytes
            ) x
          GROUP BY x.name
        ) z,
        sys.dba_tablespaces dt
      WHERE z.name = dt.tablespace_name