custom-metrics.yaml

metrics:
- context: "slow_queries"
  metricsdesc:
    p95_time_usecs: "Gauge metric with percentile 95 of elapsed time."
    p99_time_usecs: "Gauge metric with percentile 99 of elapsed time."
  request: "select  percentile_disc(0.95)  within group (order by elapsed_time) as p95_time_usecs,
    percentile_disc(0.99)  within group (order by elapsed_time) as p99_time_usecs
    from v$sql where last_active_time >= sysdate - 5/(24*60)"

- context: "big_queries"
  metricsdesc:
    p95_rows: "Gauge metric with percentile 95 of returned rows."
    p99_rows: "Gauge metric with percentile 99 of returned rows."
  request: "select  percentile_disc(0.95)  within group (order by rownum) as p95_rows,
    percentile_disc(0.99)  within group (order by rownum) as p99_rows
    from v$sql where last_active_time >= sysdate - 5/(24*60)"

- context: "size_user_segments_top100"
  metricsdesc:
    table_bytes: "Gauge metric with the size of the tables in user segments."
  labels: [segment_name]
  request: "select * from (select segment_name,sum(bytes) as table_bytes
    from user_segments where segment_type='TABLE' group by segment_name)
    order by table_bytes DESC FETCH NEXT 100 ROWS ONLY"

- context: "size_user_segments_top100"
  metricsdesc:
    table_partition_bytes: "Gauge metric with the size of the table partition in user segments."
  labels: [segment_name]
  request: "select * from (select segment_name,sum(bytes) as table_partition_bytes
    from user_segments where segment_type='TABLE PARTITION' group by segment_name)
    order by table_partition_bytes DESC FETCH NEXT 100 ROWS ONLY"

- context: "size_user_segments_top100"
  metricsdesc:
    cluster_bytes: "Gauge metric with the size of the cluster in user segments."
  labels: [segment_name]
  request: "select * from (select segment_name,sum(bytes) as cluster_bytes
    from user_segments where segment_type='CLUSTER' group by segment_name)
    order by cluster_bytes DESC FETCH NEXT 100 ROWS ONLY"

- context: "size_dba_segments_top100"
  metricsdesc:
    table_bytes: "Gauge metric with the size of the tables in user segments."
  labels: [segment_name]
  request: "select * from (select segment_name,sum(bytes) as table_bytes
    from dba_segments where segment_type='TABLE' group by segment_name)
    order by table_bytes DESC FETCH NEXT 100 ROWS ONLY"

- context: "size_dba_segments_top100"
  metricsdesc:
    table_partition_bytes: "Gauge metric with the size of the table partition in user segments."
  labels: [segment_name]
  request: "select * from (select segment_name,sum(bytes) as table_partition_bytes
    from dba_segments where segment_type='TABLE PARTITION' group by segment_name)
    order by table_partition_bytes DESC FETCH NEXT 100 ROWS ONLY"

- context: "size_dba_segments_top100"
  metricsdesc:
    cluster_bytes: "Gauge metric with the size of the cluster in user segments."
  labels: [segment_name]
  request: "select * from (select segment_name,sum(bytes) as cluster_bytes
    from dba_segments where segment_type='CLUSTER' group by segment_name)
    order by cluster_bytes DESC FETCH NEXT 100 ROWS ONLY"

- context: "cache_hit_ratio"
  metricsdesc:
    percentage: "Gauge metric with the cache hit ratio."
  request: "select
    Round((
    (Sum(Decode(a.name, 'consistent gets', a.value, 0))
      + Sum(Decode(a.name, 'db block gets', a.value, 0))
      - Sum(Decode(a.name, 'physical reads', a.value, 0))
    )/
    (Sum(Decode(a.name, 'consistent gets', a.value, 0))
      + Sum(Decode(a.name, 'db block gets', a.value, 0)))) *100,2) as percentage
    FROM v$sysstat a"

- context: "startup"
  metricsdesc:
    time_seconds: "Database startup time in seconds."
  request: "SELECT (SYSDATE - STARTUP_TIME) * 24 * 60 * 60 AS time_seconds FROM V$INSTANCE"