DM8更新列与索引统计信息环境介绍1 DM8更新列统计信息2 更新索引统计信息3 打印表更新统计信息环境介绍CPU X86OS Kylin v10DM8 03134284552-20260414-322369-20221本文基于达梦数据库DM8编写用于解决因数据频繁增删改导致统计信息过期、进而引发执行计划偏差的问题。通过定时收集列与索引的统计信息可显著提升查询性能与稳定性。适用场景与前置条件如下数据库版本达梦 DM8建议使用较新补丁版本以支持STAT 100 ON语法与SF_GET_SESSION_SQL等系统函数。权限要求执行用户需具备DBA角色或相应系统权限能够访问DBA_TAB_COLUMNS、DBA_INDEXES、V$SESSIONS等数据字典视图并拥有创建存储过程、作业的权限。模式与表名脚本中的模式名、表名为占位符使用前请替换为实际对象名。调度方式建议通过 DM 管理工具或系统作业调度每 2 分钟执行一次统计信息收集每 1 分钟执行一次“看门狗”监控用于中断超时任务。注意事项脚本会创建日志表STAT_EXEC_COL_LOG与STAT_EXEC_INDEX_LOG用于记录每次收集的状态、耗时与执行 SQL便于排查问题。1 DM8更新列统计信息--创建更新记录表--更新列COL需要对模式名表名进行替换--1创建/重建列执行日志表 DROP TABLEIFEXISTS STAT_EXEC_COL_LOG;CREATE TABLE STAT_EXEC_COL_LOG(OWNER VARCHAR(128),TABLE_NAME VARCHAR(128),--记录表名 COLUMN_NAME VARCHAR(128),--记录列名 STATE INT,--0:执行中,1:成功,2:超时,3:失败 MEMO VARCHAR(1000),EXEC_SQL VARCHAR(4000),--记录实际执行的统计信息SQL START_TIME DATETIME,END_TIME DATETIME,DURATION_SEC INT,--专门记录耗时单位秒 IDT DATETIME DEFAULT SYSDATE);--2.主调度存储过程同步执行更新列统计信息 CREATE OR REPLACE PROCEDURE STAT_COL()IS V_SQL VARCHAR(4000);V_FLAG INT;V_START DATETIME;V_END DATETIME;V_DIFF_SEC INT;V_TIMEOUT INT : 600;--10分钟 600秒BEGIN--2.遍历需要收集统计信息的列已排除大字段FORI IN(SELECTOWNER,TABLE_NAME,COLUMN_NAMEFROMDBA_TAB_COLUMNS WHERE OWNER 模式名AND TABLE_NAME IN(表名)AND COLUMN_NAME NOT LIKESYS_%AND DATA_TYPE NOT IN(TEXT,CLOB,NCLOB,BLOB,IMAGE,LONG,LONGVARCHAR,LONGVARBINARY)ORDER BY 1,2,3)LOOPSELECTNVL(MAX(STATE),-1)INTO V_FLAGFROMSTAT_EXEC_COL_LOG WHERE OWNER I.OWNER AND TABLE_NAME I.TABLE_NAME AND COLUMN_NAME I.COLUMN_NAME;IFV_FLAG NOT IN(1,2,3)THENBEGIN--3.记录开始时间标记为“执行中(0)”并将实际SQL写入表中 V_START : SYSDATE;--使用正确的达梦单列收集语法 V_SQL :STAT 100 ON ||I.OWNER||.||I.TABLE_NAME||(||I.COLUMN_NAME||);INSERT INTO STAT_EXEC_COL_LOG(OWNER,TABLE_NAME,COLUMN_NAME,STATE,START_TIME,EXEC_SQL)VALUES(I.OWNER,I.TABLE_NAME,I.COLUMN_NAME,0,V_START,V_SQL);COMMIT;--4.同步执行统计信息收集 EXECUTE IMMEDIATE V_SQL;--5.执行完毕计算耗时并更新状态 V_END : SYSDATE;V_DIFF_SEC : TIMESTAMPDIFF(SECOND,V_START,V_END);IFV_DIFF_SEC V_TIMEOUT THEN--超过10分钟标记为超时(2)UPDATE STAT_EXEC_COL_LOGSETSTATE 2,END_TIME V_END,DURATION_SEC V_DIFF_SEC,MEMO 耗时:||V_DIFF_SEC||秒, 超过10分钟WHERE OWNER I.OWNER AND TABLE_NAME I.TABLE_NAME AND COLUMN_NAME I.COLUMN_NAME AND STATE 0;ELSE--正常完成标记为成功(1)UPDATE STAT_EXEC_COL_LOGSETSTATE 1,END_TIME V_END,DURATION_SEC V_DIFF_SEC,MEMO 执行成功, 耗时:||V_DIFF_SEC||秒WHERE OWNER I.OWNER AND TABLE_NAME I.TABLE_NAME AND COLUMN_NAME I.COLUMN_NAME AND STATE 0;ENDIF;COMMIT;EXCEPTION WHEN OTHERS THEN--6.执行报错标记为失败(3)V_END : SYSDATE;V_DIFF_SEC : TIMESTAMPDIFF(SECOND,V_START,V_END);UPDATE STAT_EXEC_COL_LOGSETSTATE 3,END_TIME V_END,DURATION_SEC V_DIFF_SEC,MEMO SUBSTR(SQLERRM,1,1000)WHERE OWNER I.OWNER AND TABLE_NAME I.TABLE_NAME AND COLUMN_NAME I.COLUMN_NAME AND STATE 0;COMMIT;END;ENDIF;ENDLOOP;END;/--3.“看门狗”监控存储过程杀超时任务--看门狗逻辑同步更新精准匹配正在执行的 STAT 100 ON COLUMN 语句。 CREATE OR REPLACE PROCEDURE STAT_COL_MONITOR()IS V_KILLED_FLAG INT : 0;--标记位0表示没有超时1表示有超时 V_PARENT_SESS_ID BIGINT;--父会话ID V_MY_SESS_ID BIGINT;--当前看门狗自身的会话ID V_FULL_SQL VARCHAR(4000);--用于存储完整的SQL文本BEGIN--1.获取当前看门狗自身的会话ID防止误杀自己SELECTCAST(SYS_CONTEXT(USERENV,SESSIONID)AS BIGINT)INTO V_MY_SESS_IDFROMDUAL;----第一步检查是否有超时任务有则将日志状态更新为 2-- UPDATE STAT_EXEC_COL_LOGSETSTATE 2,MEMO 执行超过10分钟已被监控程序强制中断WHERE STATE 0 AND TIMESTAMPDIFF(SECOND,START_TIME,SYSDATE) 600;--检查是否有记录被更新如果有将标记位置为1IFSQL%ROWCOUNT 0 THEN V_KILLED_FLAG : 1;ENDIF;COMMIT;----第二步如果有超时直接遍历活跃会话强杀父会话--IFV_KILLED_FLAG 1 THENFORSESS_REC IN(SELECTSESS_IDFROMV$SESSIONSWHERE STATE ACTIVEAND SESS_ID! V_MY_SESS_ID--排除看门狗自身)LOOPBEGIN--使用系统函数获取该会话正在执行的完整SQL V_FULL_SQL : SF_GET_SESSION_SQL(SESS_REC.SESS_ID);--判断完整SQL是否包含列统计信息的主调度程序名称IFV_FULL_SQL LIKE%STAT_COL%THEN V_PARENT_SESS_ID : SESS_REC.SESS_ID;--找到后立即杀掉父会话整个列统计任务随之彻底终止 SP_CLOSE_SESSION(V_PARENT_SESS_ID);--找到并杀掉后退出循环EXIT;ENDIF;EXCEPTION WHEN OTHERS THEN--忽略获取SQL时的个别异常继续检查下一个会话CONTINUE;END;ENDLOOP;ENDIF;END;/CALL STAT_COL();--创建作业每 2 分钟执行一次列统计信息 CALL STAT_COL_MONITOR();--创建作业每 1 分钟执行一次若遇到超过10分钟未更新完成则结束更新2 更新索引统计信息--更新索引INDEX--创建/重建临时执行日志表-- 如果表已存在建议先删除重建或手动添加字段DROPTABLEIFEXISTSSTAT_EXEC_INDEX_LOG;CREATETABLESTAT_EXEC_INDEX_LOG(OWNERVARCHAR(128),OBJECT_NAMEVARCHAR(128),STATEINT,-- 0:执行中, 1:成功, 2:超时, 3:失败MEMOVARCHAR(1000),EXEC_SQLVARCHAR(4000),-- 记录实际执行的统计信息SQLSTART_TIMEDATETIME,END_TIMEDATETIME,DURATION_SECINT,-- 专门记录耗时单位秒IDTDATETIMEDEFAULTSYSDATE-- 记录插入时间);--2.更新索引CREATEORREPLACEPROCEDURESTAT_INDEX()ISV_SQLVARCHAR(4000);V_FLAGINT;V_STARTDATETIME;V_ENDDATETIME;V_DIFF_SECINT;V_TIMEOUTINT:600;-- 10分钟 600秒BEGIN-- 1. 重置上次遗留的“执行中(0)”状态为“失败(3)” 目前不需要重置要跳过-- UPDATE STAT_EXEC_INDEX_LOG-- SET STATE 3, MEMO 上次执行异常中断状态重置-- WHERE STATE 0;-- COMMIT;-- 2. 遍历需要收集统计信息的索引FORIIN(SELECTOWNER,INDEX_NAMEFROMDBA_INDEXESWHEREOWNER模式名ANDINDEX_NAMENOTLIKEINDEX33%ANDUNIQUENESSUNIQUEANDTABLE_NAMEIN(表名)ORDERBY1,2)LOOPSELECTNVL(MAX(STATE),-1)INTOV_FLAGFROMSTAT_EXEC_INDEX_LOGWHEREOWNERI.OWNERANDOBJECT_NAMEI.INDEX_NAME;IFV_FLAGNOTIN(1,2,3)THENBEGIN-- 3. 记录开始时间标记为“执行中(0)”并将实际SQL写入表中V_START :SYSDATE;V_SQL :STAT 100 ON INDEX ||I.OWNER||.||I.INDEX_NAME||;INSERTINTOSTAT_EXEC_INDEX_LOG(OWNER,OBJECT_NAME,STATE,START_TIME,EXEC_SQL)VALUES(I.OWNER,I.INDEX_NAME,0,V_START,V_SQL);COMMIT;-- 4. 同步执行统计信息收集EXECUTEIMMEDIATE V_SQL;-- 5. 执行完毕计算耗时并更新状态V_END :SYSDATE;V_DIFF_SEC :TIMESTAMPDIFF(SECOND,V_START,V_END);IFV_DIFF_SECV_TIMEOUTTHEN-- 超过10分钟标记为超时(2)【不记录 END_TIME】UPDATESTAT_EXEC_INDEX_LOGSETSTATE2,DURATION_SECV_DIFF_SEC,MEMO耗时:||V_DIFF_SEC||秒, 超过10分钟WHEREOWNERI.OWNERANDOBJECT_NAMEI.INDEX_NAMEANDSTATE0;ELSE-- 正常完成标记为成功(1)【记录 END_TIME】UPDATESTAT_EXEC_INDEX_LOGSETSTATE1,END_TIMEV_END,DURATION_SECV_DIFF_SEC,MEMO执行成功, 耗时:||V_DIFF_SEC||秒WHEREOWNERI.OWNERANDOBJECT_NAMEI.INDEX_NAMEANDSTATE0;ENDIF;COMMIT;EXCEPTIONWHENOTHERSTHEN-- 6. 执行报错标记为失败(3)【不记录 END_TIME】V_END :SYSDATE;V_DIFF_SEC :TIMESTAMPDIFF(SECOND,V_START,V_END);UPDATESTAT_EXEC_INDEX_LOGSETSTATE3,DURATION_SECV_DIFF_SEC,MEMOSUBSTR(SQLERRM,1,1000)WHEREOWNERI.OWNERANDOBJECT_NAMEI.INDEX_NAMEANDSTATE0;COMMIT;END;ENDIF;ENDLOOP;END;/--创建“看门狗”监控存储过程--这个存储过程需要配合数据库定时任务如每1分钟执行一次专门负责“杀超时任务”。--3. 重构“看门狗”监控存储过程核心--看门狗不再依赖日志表中的 sess_id而是通过关联 STAT_EXEC_INDEX_LOG 和 V$SESSIONS 视图实时精准定位当前正在执行超时任务的真实会话。CREATEORREPLACEPROCEDURESTAT_INDEX_MONITOR()ISV_KILLED_FLAGINT:0;-- 标记位0表示没有超时1表示有超时V_PARENT_SESS_IDBIGINT;-- 父会话IDV_MY_SESS_IDBIGINT;-- 当前看门狗自身的会话IDV_FULL_SQLVARCHAR(4000);-- 用于存储完整的SQL文本BEGIN-- 1. 获取当前看门狗自身的会话ID防止误杀自己SELECTCAST(SYS_CONTEXT(USERENV,SESSIONID)ASBIGINT)INTOV_MY_SESS_IDFROMDUAL;-- -- 第一步检查是否有超时任务有则将日志状态更新为 2-- UPDATESTAT_EXEC_INDEX_LOGSETSTATE2,MEMO执行超过10分钟已被监控程序强制中断WHERESTATE0ANDTIMESTAMPDIFF(SECOND,START_TIME,SYSDATE)600;-- 检查是否有记录被更新如果有将标记位置为1IFSQL%ROWCOUNT0THENV_KILLED_FLAG :1;ENDIF;COMMIT;-- -- 第二步如果有超时直接遍历活跃会话强杀父会话-- IFV_KILLED_FLAG1THENFORsess_recIN(SELECTSESS_IDFROMV$SESSIONSWHERESTATEACTIVEANDSESS_ID!V_MY_SESS_ID-- 排除看门狗自身)LOOPBEGIN-- 使用系统函数获取该会话正在执行的完整SQLV_FULL_SQL :SF_GET_SESSION_SQL(sess_rec.SESS_ID);-- 判断完整SQL是否包含主调度程序名称IFV_FULL_SQLLIKE%STAT_INDEX%THENV_PARENT_SESS_ID :sess_rec.SESS_ID;-- 找到后立即杀掉父会话整个统计任务随之彻底终止SP_CLOSE_SESSION(V_PARENT_SESS_ID);-- 找到并杀掉后退出循环EXIT;ENDIF;EXCEPTIONWHENOTHERSTHEN-- 忽略获取SQL时的个别异常继续检查下一个会话CONTINUE;END;ENDLOOP;ENDIF;END;/CALLSYSDBA.STAT_INDEX();--创建作业每 2 分钟执行一次 索引统计信息CALLSYSDBA.STAT_INDEX_MONITOR();--创建作业每 1 分钟执行一次若遇到超过10分钟未更新完成则结束更新3 打印表更新统计信息SELECTOWNER,TABLE_NAME,LAST_ANALYZED,CASEWHENLAST_ANALYZEDISNULLTHENCALL SYS.DBMS_STATS.GATHER_TABLE_STATS(||OWNER||,||TABLE_NAME||,NULL,100,TRUE,FOR ALL COLUMNS SIZE AUTO,8);ELSE无需更新ENDASUPDATE_SQLFROMDBA_TABLESWHEREOWNERNOTIN(SYS,SYSSSO,CTISYS,SYSAUDITOR,SYSDBA)ORDERBYLAST_ANALYZEDDESC;