oracledb_exporter安装配置和避免踩坑小技巧

最新推荐文章于 2024-06-26 11:42:45 发布

想用的昵称都被占用了

最新推荐文章于 2024-06-26 11:42:45 发布

阅读量5.1k

点赞数 8

分类专栏：普罗米修斯监控文章标签： docker-compose 容器 docker oracle

本文链接：https://blog.csdn.net/ZacharyBacon/article/details/122108897

版权

普罗米修斯监控专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Oracledb_exporter导出器是模仿 MySQL 导出器。
官网地址：https://github.com/iamseth/oracledb_exporter#description
docker拉取oracledb_exporter镜像：
docker pull iamseth/oracledb_exporter:latest
1.Docker-compose配置文件参考：

# 这个容器主要负责抓取慢SQL，全盘扫描，逻辑读，物理读等等，由于查询耗时长，promethues抓取周期10分钟
 

在这里插入图片描述

 oracledb_exporter_custom:
    image: iamseth/oracledb_exporter:latest
    container_name: oracledb_exporter_static
    hostname: oracledb_exporter_custom
    restart: on-failure   
    ports:
      - "19161:9161"
    networks:
      monitor:
        ipv4_address: 172.28.0.8
    volumes:
      - "/prom/oracledb_exporter/metrics-static.toml:/default-metrics.toml"
#      - "/prom/oracledb_exporter/tnsnames.ora:/etc/to/tns_admin/tnsnames.ora"
    command: --default.metrics="/default-metrics.toml" --log.level="debug"
    environment:
      - TZ=Asia/Shanghai
      - DATA_SOURCE_NAME=用户名/密码@ip:端口/库名
      - QUERY_TIMEOUT=180  #查询超时时间，以秒为单位，oracledb_exporter查询oracle的超时时间，由于逻辑读，物理读，全盘扫描查询时间较长，很容易造成查询超时，需要设置长一些。
#      - TNS_ENTRY=12345
#      - TNS_ADMIN=/etc/to/tns_admin
#      - DATA_SOURCE_NAME=$TNS_ENTRY

# 此容器为常规oracle系统性能查询，promethues抓取周期为2分钟
  oracledb_exporter_default:
    image: iamseth/oracledb_exporter:latest
    container_name: oracledb_exporter_normal
    hostname: oracledb_exporter_default
    restart: on-failure   
    ports:
      - "19162:9161"
    networks:
      monitor:
        ipv4_address: 172.28.0.9
    volumes:
      - "/prom/oracledb_exporter/metrics-normal.toml:/default-metrics.toml"
    command: --default.metrics="/default-metrics.toml"
    environment:
      - TZ=Asia/Shanghai
      - DATA_SOURCE_NAME=用户名/密码@ip:端口/库名

2.metrics-static.toml配置文件参考：

#物理读
[[metric]]
context = "physical_disk"
labels = ["sql_id", "sql_text"]
metricsdesc = { reads= "Disk read.", exec= "EXECUTIONS.", reads_exec= "Reads_Exec." }
request = """
SELECT distinct
       A.SQL_ID as sql_id,
       B.SQL_TEXT as sql_text,
       A.DISK_READS AS reads,
       A.EXECUTIONS AS exec,
       round(A.DISK_READS / A.EXECUTIONS) AS reads_exec
  FROM V$SQLAREA A, V$SQL B
 WHERE A.DISK_READS > 1000
   AND A.EXECUTIONS > 200
   AND A.sql_id = B.sql_id
   AND A.MODULE = 'JDBC Thin Client'
   AND A.DISK_READS / A.EXECUTIONS > 500
"""

#逻辑读
[[metric]]
context = "logical_read"
labels = ["sql_id", "sql_text"]
metricsdesc = { gets_exec= "gets exec.", buffer_gets= "buffer gets.", exec= "EXECUTIONS." }
request = """
SELECT *
  FROM (
        SELECT  distinct
                A.SQL_ID,
                B.SQL_TEXT as sql_text,
                ROUND(A.BUFFER_GETS / A.EXECUTIONS) AS gets_exec,
                A.BUFFER_GETS as buffer_gets,
                A.EXECUTIONS as exec
          FROM V$SQLAREA A, V$SQL B
         WHERE A.BUFFER_GETS > 1000
           AND A.sql_id = B.sql_id
           AND A.MODULE = 'JDBC Thin Client'
           AND A.BUFFER_GETS / A.EXECUTIONS > 2000
           AND A.EXECUTIONS > 200
         ORDER BY gets_exec DESC)
 WHERE ROWNUM <= 10
"""

#[[metric]]
#context = "librarycache"
#metricsdesc = { value="Generic counter metric from V$LIBRARYCACHE view in Oracle."  }
#fieldtoappend= "name"
#request = "SELECT to_char('librarycache_hit_rate') as name, round(SUM(PINHITS) / SUM(PINS), 3) AS VALUE FROM V$LIBRARYCACHE"

[[metric]]
context = "parse"
labels = ["name"]
metricsdesc = { value= "Soft and hard analysis." }
request = "SELECT NAME as name, VALUE as value FROM V$SYSSTAT WHERE NAME LIKE 'parse%'"

#共享池
[[metric]]
context = "shared_pool"
labels = ["pool", "name"]
metricsdesc = { bytes= "shared pool bytes." }
request = """
SELECT POOL as pool,
       Name as name,
       BYTES as bytes
  FROM V$SGASTAT A
 WHERE A.POOL = 'shared pool'
   AND A.NAME = 'free memory'
   OR A.NAME = 'row cache'
"""

#[[metric]]
#context = "row_cache"
#metricsdesc = { gets= "gets.", getmisses= "getmisses.", hit_rate= "hit rate." }
#request = "SELECT SUM(GETS) as gets, SUM(GETMISSES) as getmisses, 100 * SUM(GETS - GETMISSES) / SUM(GETS) AS hit_rate FROM V$ROWCACHE"

#全盘扫描
[[metric]]
context = "full_access"
labels = ["sql_id", "sql_text", "obj_owner", "ogj_name"]
metricsdesc = { size_m= "size m.", all_exec= "all executions.", max_exec= "max executions." }
request = """
SELECT *
  FROM (SELECT distinct C.SQL_ID AS sql_id,
               D.SQL_TEXT as sql_text,
               OBJECT_OWNER AS obj_owner,
               OBJECT_NAME AS ogj_name,
               ROUND(B.BYTES / 1024 / 1024, 0) AS size_m,
               SUM(EXECUTIONS_DELTA) AS all_exec,
               MAX(EXECUTIONS_DELTA) AS max_exec,
               ROUND(SUM(EXECUTIONS_DELTA) * B.BYTES / 1024 / 1024 / 1024 / 800,0) AS all_io_large,
               ROUND(MAX(EXECUTIONS_DELTA) * B.BYTES / 1024 / 1024 / 1024 / 40,0) AS max_io_large
          FROM DBA_HIST_SQL_PLAN A, 
               (SELECT OWNER, SEGMENT_NAME, SUM(BYTES) BYTES FROM DBA_SEGMENTS GROUP BY OWNER, SEGMENT_NAME) B,
               DBA_HIST_SQLSTAT C,  V$SQL D
         WHERE A.OPERATION = 'TABLE ACCESS'
           AND A.OPTIONS = 'FULL'
           AND A.OBJECT_OWNER IN (SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS = 'OPEN' AND USERNAME NOT IN ('SYS', 'SYSMAN', 'SYSTEM', 'PATROL', 'MDSYS', 'ORDDATA', 'OLAPSYS', 'EXFSYS'))
           AND B.SEGMENT_NAME = A.OBJECT_NAME
           AND B.OWNER = A.OBJECT_OWNER
           AND B.BYTES > 1024 * 1024 * 10
           AND C.SQL_ID = A.SQL_ID
           AND C.PARSING_SCHEMA_NAME NOT IN ('SYS', 'SYSMAN', 'SYSTEM', 'PATROL', 'MDSYS', 'ORDDATA', 'OLAPSYS', 'EXFSYS')
           AND EXECUTIONS_DELTA > 100
           AND C.SNAP_ID > (SELECT MIN(SNAP_ID) FROM DBA_HIST_SNAPSHOT WHERE BEGIN_INTERVAL_TIME > SYSDATE - 7)
           AND A.SQL_ID = D.SQL_ID
         GROUP BY C.SQL_ID, D.SQL_TEXT, OBJECT_NAME, OBJECT_OWNER,B.BYTES
         ORDER BY ROUND(B.BYTES / 1024 / 1024, 0) DESC, OBJECT_NAME)
 WHERE ROWNUM <= 30
"""

3.metrics-normal.toml配置文件参考：

[[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"

#[[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"
metricsdesc = { value="Generic counter metric from v$waitclassmetric view in Oracle." }
fieldtoappend= "wait_class"
request = '''
SELECT
  n.wait_class as WAIT_CLASS,
  round(m.time_waited/m.INTSIZE_CSEC,3) as VALUE
FROM
  v$waitclassmetric  m, v$system_wait_class n
WHERE
  m.wait_class_id=n.wait_class_id AND n.wait_class != 'Idle'
'''

[[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." }
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
FROM  dba_tablespace_usage_metrics dtum, dba_tablespaces dt
WHERE dtum.tablespace_name = dt.tablespace_name
ORDER by tablespace
'''

4.注意事项：
敲黑板，划重点，这个是纯踩坑踩出来的，全身泪。。。
Promethues只能写入数字，不能写入因为字母，不过可以写入标签值（假值）
如下图所示：
在这里插入图片描述

Oracle查询结果有多列，字母则是假值，数字可以写入promethues中，数字不能有小数点。若是有2列，一个假值（含有字母和数字）对应一个值（只有数字），则可以有小数（具体待测）

想用的昵称都被占用了

关注

8
点赞
踩
11

收藏

觉得还不错? 一键收藏
19
评论
oracledb_exporter安装配置和避免踩坑小技巧

Oracledb_exporter导出器是模仿 MySQL 导出器。官网地址：https://github.com/iamseth/oracledb_exporter#descriptiondocker拉取oracledb_exporter镜像：docker pull iamseth/oracledb_exporter:latest1.Docker-compose配置文件参考：# 这个容器主要负责抓取慢SQL，全盘扫描，逻辑读，物理读等等，由于查询耗时长，promethues抓取周期10分钟
复制链接

扫一扫