ROLL.DBF回滚表空间增长问题（达梦数据库）

环境介绍

• 达梦数据库ROLL.DBF 在某些业务系统厂商使用时异常增长到TB问题分析定位；
• 业务系统的INSERT , UPDATE , DELETE 操作都会占用回滚表空间，这类事务都存在可以回滚的情况；
• 当表空间数据文件大小增长后，就算事务提交，数据文件不会自动变小，但里面的空闲比率会变大；
• 下面介绍环境演练与总结的查询语句，提供问题分析方向；

1 环境搭建

1.1 创建表与测试数据

CREATE TABLE AT230828 (C1 INT);
TRUNCATE TABLE AT230828;

BEGIN
	FOR I IN 1..100000 LOOP
		INSERT INTO AT230828 VALUES(I);
	END LOOP;
END;

• 在事务不提交的情况下，查询

1.2 查询待提交的数据量

SELECT * FROM V$TRX TR LEFT JOIN V$SESSIONS VS ON TR.SESS_ID=VS.SESS_ID 
WHERE TR.INS_CNT <>0 OR TR.DEL_CNT<>0 OR UPD_CNT <>0;

• 结果集中 9,10,11 行/列 分别对应 数据插入，数据删除，数据更新未提交的数据量
• 结果集中 25 行/列 对应当前事务正在执行的SQL语句
• 结果集中 48 行/列 对应当前事务客户端 IP 地址
• 结果集中 66 行/列 对应当前事务客户端使用的驱动版本号

1.3 查询回滚表空间使用情况

SELECT T.TOTAL_MB,T.TOTAL_MB-T.FREE_MB USE_MB,T.FREE_MB,R._USAGE,P.PAGE_USE_MB FROM
(
SELECT TOTAL_SIZE*(SELECT PAGE()/1024)/1024 TOTAL_MB,FREE_SIZE*(SELECT PAGE()/1024)/1024 FREE_MB FROM V$DATAFILE WHERE CLIENT_PATH='ROLL.DBF'
)T,
(
SELECT ROUND((1-(FREE_SIZE*1.0/TOTAL_SIZE*1.0))*100,3) ||'%' _USAGE FROM V$DATAFILE WHERE CLIENT_PATH='ROLL.DBF'
)R,
(
SELECT((SELECT SUM(N_USED_PAGES) FROM V$PSEG_ITEMS)* (SELECT PAGE()/1024))/1024 PAGE_USE_MB
)P
;

1.3.1 插入数据前查询结果

1.3.2 插入数据后未提交事务查询结果

1.3.3 插入数据后提交事务查询结果

1.4 定时自动查询并记录未提交事务

• 创建表,创建代理环境,创建作业

--创建表
CREATE TABLE T_ROLL AS
(SELECT TR.ID,TR.NEXTID,TR.MIN_ACTIVE_ID,TR.STATUS,
TR.ISOLATION,TR.READ_ONLY,TR.SESS_SEQ,
TR.INS_CNT,TR.DEL_CNT,TR.UPD_CNT,TR.UPD_INS_CNT,UREC_SEQNO,
VS.THRD_ID,TR.SESS_ID,VS.SQL_TEXT,VS.STATE,VS.USER_NAME,
VS.CREATE_TIME,VS.CLNT_TYPE,VS.CLNT_IP,VS.LAST_RECV_TIME
FROM V$TRX TR LEFT JOIN V$SESSIONS VS ON TR.SESS_ID=VS.SESS_ID 
WHERE TR.INS_CNT <>0 OR TR.DEL_CNT<>0 OR UPD_CNT <>0);

--创建代理环境
SP_INIT_JOB_SYS(1);

--创建作业,每30分钟记录一次
call SP_CREATE_JOB('ROLL',1,0,'',0,0,'',0,'');
call SP_JOB_CONFIG_START('ROLL');
call SP_ADD_JOB_STEP('ROLL', 'ROLL', 0, 'INSERT INTO T_ROLL
(SELECT TR.ID,TR.NEXTID,TR.MIN_ACTIVE_ID,TR.STATUS,
TR.ISOLATION,TR.READ_ONLY,TR.SESS_SEQ,
TR.INS_CNT,TR.DEL_CNT,TR.UPD_CNT,TR.UPD_INS_CNT,UREC_SEQNO,
VS.THRD_ID,TR.SESS_ID,VS.SQL_TEXT,VS.STATE,VS.USER_NAME,
VS.CREATE_TIME,VS.CLNT_TYPE,VS.CLNT_IP,VS.LAST_RECV_TIME
FROM V$TRX TR LEFT JOIN V$SESSIONS VS ON TR.SESS_ID=VS.SESS_ID 
WHERE TR.INS_CNT <>0 OR TR.DEL_CNT<>0 OR UPD_CNT <>0);', 1, 1, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('ROLL', 'ROLL', 1, 1, 1, 0, 30, '00:00:00', '23:59:59', '2024-02-19 08:57:34', NULL, '');
call SP_JOB_CONFIG_COMMIT('ROLL');

--查询结果集
SELECT * FROM T_ROLL ORDER BY INS_CNT DESC;
SELECT * FROM T_ROLL ORDER BY DEL_CNT DESC;
SELECT * FROM T_ROLL ORDER BY UPD_CNT DESC;
SELECT * FROM T_ROLL ORDER BY UPD_INS_CNT DESC;