[[metric]]
context = "sessions"
labels = [ "status", "type" ]
metricsdesc = { value= "Gauge metric with count of sessions by status and type." }
request = "SELECT status, type, COUNT(*) as value FROM v$session GROUP BY status, type"

[[metric]]
context = "resource"
labels = [ "resource_name" ]
metricsdesc = { current_utilization= "Generic counter metric from v$resource_limit view in Oracle (current value).", limit_value="Generic counter metric from v$resource_limit view in Oracle (UNLIMITED: -1)." }
request = '''
SELECT resource_name, current_utilization, CASE WHEN TRIM(limit_value) LIKE 'UNLIMITED' THEN '-1' ELSE TRIM(limit_value) END as limit_value 
FROM v$resource_limit
'''
ignorezeroresult = true

[[metric]]
context = "asm_diskgroup"
labels = [ "name" ]
metricsdesc = { total = "Total size of ASM disk group.", free = "Free space available on ASM disk group." }
request = "SELECT name,total_mb*1024*1024 as total,free_mb*1024*1024 as free FROM v$asm_diskgroup_stat where exists (select 1 from v$datafile where name like '+%')"
ignorezeroresult = true

[[metric]]
context = "activity"
metricsdesc = { value="Generic counter metric from v$sysstat view in Oracle." }
fieldtoappend = "name"
request = "SELECT name, value FROM v$sysstat WHERE name IN ('parse count (total)', 'execute count', 'user commits', 'user rollbacks')"

[[metric]]
context = "process"
metricsdesc = { count="Gauge metric with count of processes." }
request = "SELECT COUNT(*) as count FROM v$process"

[[metric]]
context = "wait_time"
labels = ["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
  wait_class,
  round(time_waited/100,3) time_waited_sec_total,
  con_id
from v$system_wait_class
where wait_class <> 'Idle'
'''
ignorezeroresult = true

[[metric]]
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
ORDER by tablespace
'''

[[metric]]
context = "db_system"
labels = [ "name" ]
metricsdesc = { value = "Database system resources metric" }
request = '''
select name, value 
from v$parameter 
where name in ('cpu_count', 'sga_max_size', 'pga_aggregate_limit')
'''

[[metric]]
context = "db_platform"
labels = [ "platform_name" ]
metricsdesc = { value = "Database platform" }
request = '''
SELECT platform_name, 1 as value FROM v$database
'''

[[metric]]
context = "top_sql"
labels = [ "sql_id", "sql_text" ]
metricsdesc = { elapsed = "SQL statement elapsed time running" }
request = '''
select * from (
select sql_id, elapsed_time / 1000000 as elapsed, SUBSTRB(REPLACE(sql_text,'',' '),1,55) as sql_text
from   V$SQLSTATS
order by elapsed_time desc
) where ROWNUM <= 15
'''
ignorezeroresult = true

[[metric]]
context = "cache_hit_ratio"
labels = [ "cache_hit_type" ]
metricsdesc = { value = "Cache Hit Ratio" }
request = '''
select metric_name cache_hit_type, value
from v$sysmetric
where group_id=2 and metric_id in (2000,2050,2112,2110)
'''
ignorezeroresult = true