grafana监控添加Oracle数据库TOP SQL监控

【添加TOP SQL监控】

需要在监控面板上面添加一个TOP SQL的监控信息

【定义一个TOP SQL查询语句】

根据实际情况编写一个TOP SQL语句查询，这里如下：

SELECT sql_id,
       disk_reads,
       cpu_time,
       elapsed_time,
       SUBSTR(sql_text, 1, 500) as sql_text
  FROM v$sql
 where first_load_time >=to_char(sysdate - 10 / (24 * 60), 'yyyy-mm-dd/hh24:mi:ss') and rownum <= 20
 ORDER BY disk_reads + cpu_time DESC

在default-metrics.toml文件中添加采集信息

[[metric]]
context = "topsql"
labels = [ "sql_id", "sql_text" ]
metricsdesc = { disk_reads = "Generic counter metric of topsql disk_reads in Oracle.", cpu_time = "Generic counter metric of topsql cpu_time in Oracle.", elapsed_time = "Generic counter metric of topsql elapsed_time in Oracle."}
request = '''
SELECT sql_id,
disk_reads,
cpu_time,
elapsed_time,
SUBSTR(sql_text, 1, 500) as sql_text
FROM v$sql
where first_load_time >=to_char(sysdate - 10 / (24 * 60), 'yyyy-mm-dd/hh24:mi:ss') and rownum <= 20
ORDER BY disk_reads + cpu_time DESC
'''

重启采集服务

systemctl status oracledb_exporter
systemctl stop oracledb_exporter
systemctl start oracledb_exporter
systemctl status oracledb_exporter

测试采集数据是否正常

curl http://xxxx:9161/metrics

【grafana添加面板】

添加一个面板后选择table类型