达梦数据库实例监控

继上一篇介绍到如何达梦安装集成使用今天主要文章就是介绍如何玩转达梦数据库以及一些sql语句分析排查监控等让我们开始吧。语句一监控数据库实例的运行状态模式事务等核心信息。sql语句如下SELECTTO_CHAR(START_TIME, YYYY-MM-DD HH24:MI:SS) AS START_TIME,CASE STATUS$WHEN OPEN THEN 1WHEN MOUNT THEN 2WHEN SUSPEND THEN 3ELSE 4END AS STATUS,CASE MODE$WHEN PRIMARY THEN 1WHEN NORMAL THEN 2WHEN STANDBY THEN 3ELSE 4END AS MODE,(SELECT COUNT(*) FROM V$TRXWAIT) AS TRXNUM,(SELECT COUNT(*) FROM V$THREADS) AS THREADSNUM,DATEDIFF(DAY, START_TIME, SYSDATE) AS DBSTARTDAYFROM V$INSTANCE;执行结果相关字段含义如下START_TIME- 实例启动时间 数据库实例最近一次启动的具体时间。用于判断实例运行时长、排查重启问题STATUS$- 实例状态 1 数据库已打开正常提供服务2数据库已装载但未打开用于维护操作3数据库挂起状态可能因资源问题4未知或异常状态MODE$- 数据库运行模式1主库模式用于数据守护环境2普通模式单实例运行3备库模式用于数据守护4特殊模式或异常TRXNUM- 等待事务数 0系统运行正常无事务阻塞 0存在锁等待或资源争用数值越大说明系统并发问题越严重THREADSNUM - 线程数量数据库当前活动的线程总数数据库的并发处理能力系统负载情况连接池使用状况DBSTARTDAY- 实例运行天数实例连续运行的天数可以用来判断系统稳定性以及故障排查时间参考等。如果这个值小于1则表示近期重启过V$INSTANCE显示当前数据库实例的基本信息包含实例状态、启动时间、运行模式等V$TRXWAIT显示当前正在等待锁或其他资源的事务信息用于监控系统锁等待和死锁情况V$THREADS显示数据库线程信息反映数据库的并发处理状态语句二会话状态监控。sql语句如下-- 查询会话状态统计和最大会话数SELECTDECODE(STATE, NULL, TOTAL, STATE) AS STATE_TYPE,COUNT(SESS_ID) AS COUNT_VALFROM V$SESSIONSWHERE STATE IN (IDLE, ACTIVE)GROUP BY ROLLUP(STATE)UNION ALLSELECT MAX_SESSION AS STATE_TYPE, para_value AS COUNT_VALFROM V$DM_INIWHERE para_name MAX_SESSIONS;-- 达梦数据库session修改记录达到大事务级别-- 查询最大使用记录数SELECT MAX(t.ins_cnt t.del_cnt t.upd_cnt t.upd_ins_cnt) AS used_urecFROM V$TRX t;第一个查询获取系统会话最大数量超过这个会话最大数量后后应用层无法再连接数据库TOTAL字段含义为当前已经使用的会话数量。第二个则是查询计算当前所有事务中最大的操作记录数相关参数字段如下ins_cnt插入记录数del_cnt删除记录数upd_cnt更新记录数upd_ins_cnt更新插入记录数监控意义用于识别大事务预防事务过大的性能问题其中可以借助TOTAL/MAX_SESSION字段的百分比如果这个百分比超过70%那么就发起预警警告同时通过v$trx视图查询可以看到指定事务的修改记录从而判断出是否是大事务比如说超过1w行修改就是大事务会话语句三表空间/数据文件监控sql语句如下SELECTF.TABLESPACE_NAME,T.TOTAL_SPACE AS TOTAL_SIZE,F.FREE_SPACE AS FREE_SIZEFROM (SELECTTABLESPACE_NAME,ROUND(SUM(BLOCKS * (SELECT PARA_VALUE / 1024 FROM V$DM_INI WHERE PARA_NAME GLOBAL_PAGE_SIZE) / 1024)) AS FREE_SPACEFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) FJOIN (SELECTTABLESPACE_NAME,ROUND(SUM(BYTES / 1048576)) AS TOTAL_SPACEFROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME) T ON F.TABLESPACE_NAME T.TABLESPACE_NAME;--数据文件自动拓展检查SELECT 表空间文件自动扩展设置,LISTAGG(CLIENT_PATH || : || CASE AUTO_EXTEND WHEN 1 THEN 自动扩展 ELSE 手动扩展 END, , ) WITHIN GROUP (ORDER BY CLIENT_PATH)FROM V$DATAFILE;第一个是查询数据库中所有表空间的总大小和剩余空闲大小。计算逻辑1. 从 V$DM_INI 获取 GLOBAL_PAGE_SIZE全局页面大小2. 将空闲块数(BLOCKS) × 页面大小 ÷ 1024 ÷ 1024 空闲空间(MB)主要是用来监控表空间使用情况预防空间不足同时根据使用趋势规划存储扩容(空间不足会影响数据库性能)第二个sql是用来检查所有数据文件的自动扩展设置状态。确保关键表空间启用自动扩展避免因空间满导致业务中断空间不足时需及时扩容或清理数据语句四sql语句执行统计SELECTNAME,STAT_VALFROMv$sysstatWHEREname IN (select statements initiated,insert statements initiated,delete statements initiated,update statements initiated, ddl statements,transaction total count,select statements in PL/SQL, insert statements in PL/SQL,delete statements in PL/SQL,update statements in PL/SQL, DDL in PL/SQL count,dynamic exec in PL/SQL,DB time(ms),parse time, hard parse time(ms),latch wait time(ms),mutex wait time(ms),IO wait time, trx lock wait time(ms),redo sync wait time(ms),redo sync wait time for commit, parse count,parser errors,hard parse count,plan total, plan cache hit,logic read count,recycle logical reads,physical read count,physical multi read count,physical write count);这个 SQL 语句用于查询达梦数据库的系统性能统计信息它监控了数据库的各种关键操作和性能指标。其中每个字段涵义如下SQL 操作统计select statements initiated执行的 SELECT 语句数量insert statements initiated执行的 INSERT 语句数量delete statements initiated执行的 DELETE 语句数量update statements initiated执行的 UPDATE 语句数量ddl statementsDDL数据定义语言语句数量事务统计transaction total count事务总数PL/SQL 中的 SQL 操作select statements in PL/SQLPL/SQL 中执行的 SELECT 数量insert statements in PL/SQLPL/SQL 中执行的 INSERT 数量delete statements in PL/SQLPL/SQL 中执行的 DELETE 数量update statements in PL/SQLPL/SQL 中执行的 UPDATE 数量DDL in PL/SQL countPL/SQL 中执行的 DDL 数量dynamic exec in PL/SQLPL/SQL 中动态执行的语句数量时间统计性能关键指标DB time(ms)数据库总处理时间毫秒parse timeSQL 解析时间hard parse time(ms)硬解析时间毫秒latch wait time(ms)闩锁等待时间毫秒mutex wait time(ms)互斥锁等待时间毫秒IO wait timeI/O 等待时间trx lock wait time(ms)事务锁等待时间毫秒redo sync wait time(ms)重做日志同步等待时间毫秒redo sync wait time for commit提交时的重做日志同步等待时间解析相关统计parse count解析总次数parser errors解析错误次数hard parse count硬解析次数plan total执行计划总数plan cache hit执行计划缓存命中次数I/O 操作统计logic read count逻辑读次数recycle logical reads回收的逻辑读次数physical read count物理读次数physical multi read count多块物理读次数physical write count物理写次数SELECT硬解析率 AS 指标,ROUND((硬解析次数/解析总次数)*100, 2) || % AS 值FROM (SELECTMAX(CASE WHEN name hard parse count THEN stat_val END) AS 硬解析次数,MAX(CASE WHEN name parse count THEN stat_val END) AS 解析总次数FROM v$sysstatWHERE name IN (hard parse count, parse count));语句五慢sql语句监控SELECTEXEC_TIME AS 执行时间(ms),SLOW_SQL AS 慢SQL语句,SESS_ID AS 会话ID,CURR_SCH AS 当前模式,THRD_ID AS 线程ID,LAST_RECV_TIME AS 最后接收时间,CONN_IP AS 客户端IP,ROUND(EXEC_TIME/1000, 2) AS 执行时间(秒)FROM (SELECTDATEDIFF(ms, LAST_RECV_TIME, SYSDATE) AS EXEC_TIME,DBMS_LOB.SUBSTR(SF_GET_SESSION_SQL(SESS_ID), 4000, 1) AS SLOW_SQL,SESS_ID,CURR_SCH,THRD_ID,LAST_RECV_TIME,SUBSTR(CLNT_IP, 8, 13) AS CONN_IPFROM V$SESSIONSWHERE STATE ACTIVEAND LAST_RECV_TIME IS NOT NULLAND LAST_RECV_TIME TO_DATE(2025-10-19 00:00:00, YYYY-MM-DD HH24:MI:SS)ORDER BY EXEC_TIME DESC)WHERE EXEC_TIME 1000AND ROWNUM 20;表示查询耗时在1000毫秒以上返回结果集前20条执行结果如下语句六数据库用户状态监控SELECTA.USERNAME,CASE B.RN_FLAG WHEN 0 THEN N WHEN 1 THEN Y END AS READ_ONLY,CASE A.ACCOUNT_STATUS WHEN LOCKED THEN 锁定 WHEN OPEN THEN 正常 ELSE 异常 END AS ACCOUNT_STATUS,TO_CHAR(A.EXPIRY_DATE, YYYY-MM-DD HH24:MI:SS) AS EXPIRY_DATE,TO_CHAR(ROUND(DATEDIFF(DAY, SYSDATE, A.EXPIRY_DATE), 2)) AS EXPIRY_DATE_DAY,A.DEFAULT_TABLESPACE,A.PROFILE,TO_CHAR(A.CREATED, YYYY-MM-DD HH24:MI:SS) AS CREATE_TIMEFROM DBA_USERS AJOIN SYSUSERS B ON A.USER_ID B.IDWHERE A.USERNAME NOT IN (SYS, SYSSSO, SYSAUDITOR);语句七数据库授权监控SELECTCASEWHEN expired_date IS NULL THEN ELSE TO_CHAR(expired_date, YYYYMMDD)END AS expired_dateFROM V$LICENSE;EXPIRED_DATE字段表示授权到期日期空表示永久授权。将该字段作为告警项避免因授权到期而导致实例出现异常语句八数据库缓存命中监控-- 数据库缓存性能综合查询SELECTITEM AS 缓存监控项,VALUE AS 数值FROM (-- 缓冲区命中率SELECT 1 AS SORT, 缓冲区命中率 AS ITEM,ROUND((1 - (PHY_READS / NULLIF(LOG_READS, 0))) * 100, 2) || % AS VALUEFROM (SELECTMAX(CASE WHEN NAME physical reads THEN STAT_VAL ELSE 0 END) AS PHY_READS,MAX(CASE WHEN NAME logical reads THEN STAT_VAL ELSE 0 END) AS LOG_READSFROM V$SYSSTATWHERE NAME IN (physical reads, logical reads))UNION ALL-- 物理读次数SELECT 2, 物理读次数, TO_CHAR(STAT_VAL)FROM V$SYSSTAT WHERE NAME physical readsUNION ALL-- 逻辑读次数SELECT 3, 逻辑读次数, TO_CHAR(STAT_VAL)FROM V$SYSSTAT WHERE NAME logical readsUNION ALL-- 缓存大小信息SELECT 4, 缓冲区大小(MB),(SELECT TO_CHAR(ROUND(PARA_VALUE/1024/1024, 2))FROM V$DM_INIWHERE PARA_NAME BUFFER AND ROWNUM 1)FROM DUALWHERE EXISTS (SELECT 1 FROM V$DM_INI WHERE PARA_NAME BUFFER))ORDER BY SORT;-- 查看关键缓存指标SELECTNAME AS 指标名称,STAT_VAL AS 当前值,CASEWHEN NAME buffer hit ratio THEN STAT_VAL || %WHEN NAME LIKE %time% THEN ROUND(STAT_VAL/1000, 2) || 秒ELSE TO_CHAR(STAT_VAL)END AS 显示值FROM V$SYSSTATWHERE NAME IN (buffer hit ratio, logical reads, physical reads, buffer gets)OR NAME LIKE %cache%;