月结保障：回滚慢、行锁频发

问题背景

3.1号月结现场保障，到场了先让kill了一个账务的会话，回滚了20min+，巡检的时候发现报表库有几条行锁：enq: TX - row lock contention，sql：delete from table_name 语句已经失败，正在回滚。

观察会话事务，发现改事务回滚块最大的时候有 3204496，3百万，那就是3204496*16/1024/1024 = 55G,速度很慢。

@trans SID SERIAL# USERNAME TADDR SES_ADDR USED_UBLK USED_UREC 0xFLAG STATUS START_DATE XIDUSN XIDSLOT XIDSQN XID PRV_XID PTX_XID ------- ---------- ---------- ---------------- ---------------- ---------- ---------- --------- ----------------------------- ------------------- ---------- ---------- ---------- ---------------- ---------------- ---------------- 5956 36122 owner 0000000A93924120 0000000B841007F0 3204496 134656835 1E83 ACTIVE ROLLING BACK 2025-03-01 05:30:01 3560 30 253045118 E80D1E007E29150F 0000000000000000 0000000000000000 SQL> / SID SERIAL# USERNAME TADDR SES_ADDR USED_UBLK USED_UREC 0xFLAG STATUS START_DATE XIDUSN XIDSLOT XIDSQN XID PRV_XID PTX_XID ------- ---------- ---------- ---------------- ---------------- ---------- ---------- --------- ----------------------------- ------------------- ---------- ---------- ---------- ---------------- ---------------- ---------------- 5956 36122 owner 0000000A93924120 0000000B841007F0 3158778 132735745 1E83 ACTIVE ROLLING BACK 2025-03-01 05:30:01 3560 30 253045118 E80D1E007E29150F 0000000000000000 0000000000000000 SQL> / SID SERIAL# USERNAME TADDR SES_ADDR USED_UBLK USED_UREC 0xFLAG STATUS START_DATE XIDUSN XIDSLOT XIDSQN XID PRV_XID PTX_XID ------- ---------- ---------- ---------------- ---------------- ---------- ---------- --------- ----------------------------- ------------------- ---------- ---------- ---------- ---------------- ---------------- ---------------- 5956 36122 owner 0000000A93924120 0000000B841007F0 2301035 96692545 1E83 ACTIVE ROLLING BACK 2025-03-01 05:30:01 3560 30 253045118 E80D1E007E29150F 0000000000000000 0000000000000000

回滚的很慢，早上kill了一个5G的回滚块都20min。有没有快一点的办法？大神说可以杀了会话，做实例级并行恢复更快，我不会操作啊，和甲方沟通一下有Oracle原厂的人保障，让他们来？和局方沟通了一下，还是等着慢慢回滚了。

这有一个风险就是回滚块过大，再加上月结有大量的insert语句，可能导致undo空间不多，200G的undo表空间EXPIRED 只有 722MB 了。在一个就是ora-01555.

SQL> select tablespace_name,status,sum(bytes)/1024/1024 mb from DBA_UNDO_EXTENTS group by tablespace_name,status; TABLESPACE_NAME STATUS MB ------------------------------ --------- ---------- UNDOTBS1 ACTIVE 75294.9375 UNDOTBS1 EXPIRED 722.6875 UNDOTBS1 UNEXPIRED 169733.75 UNDOTBS2 EXPIRED 62278.4375 UNDOTBS2 UNEXPIRED 110715.25

临近下午下班的时候行锁又多了，回滚完一个又来一个。这业务到底是想删这个表呢还是想留着呢？2亿数据的表，列的选择性不是很好，没有索引。

SQL> select a.column_name, 2 b.num_rows, 3 a.num_distinct Cardinality, 4 round(a.num_distinct / b.num_rows * 100, 2) selectivity 5 from dba_tab_col_statistics a, dba_tables b 6 where a.owner = b.owner 7 and a.table_name = b.table_name 8 and a.owner = 'owner' 9 and a.table_name = 'table_name'; COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY ------------------- ---------- ----------- ----------- API_ID 233051892 0 0 ERROR_XXXXXXXXX 233051892 7 0 ERROR_XXXX 233051892 3 0 ERROR_XXXX 233051892 3 0 PROCESXXXX 233051892 18588 .01 ROW_NUM 233051892 6172 0 6 rows selected.

看程序是python连接过来的，记录一下客户端发给业务，业务说试试解决一下，当时有点惊讶，通常能跑就是好代码，一般是不会动的。

select osuser,machine,module,program ,CLIENT_IDENTIFIER,count(*) from v$session where sql_id='6mp492ha74kbx' group by machine,module,program,osuser,CLIENT_IDENTIFIER

但是，后面的得到的解释是“进程启的多，现在只留一个了。”。。。。。

那为什么不想想为什么起那么多进程， 这次杀了，下次还有啊 ，严重吐槽业务这边的日志，异常处理做的太差了，应用好几次抛出了几个错误信息都没封装直接给数据库了，都是ORA—XXX，出了问题没有接口文档，没有任何信息直接抛给数据库，业务是我们写的吗？？？

思考

为什么会出现这种情况呢？删了回滚再删，还是删全部，可能这个表放的是一些临时缓存的数据。

我猜测可能是业务逻辑写错了，在执行完这条sql之后，事务没有成功，导致回滚，回滚时间很长，期间再次调用此函数，导致了行锁，怎么解决呢？

最彻底的是修改业务，缩小事务，一个事务一个原子性，此函数中可能有一个位置导致未满足原子性导致的事务失败回滚，要想清楚什么时候删数据一定可以删，再删之前 if 语句判断一下。可以在执行删除操作前加一个锁，再次执行到这的时候发现没有执行完也就拿不到锁，可以避免数据库中的行锁，但是不能解决undo一直回滚。做正确的异常处理，执行失败一定要做异常处理，抛出异常日志，都留到数据库处理怎么行。如果确定是要删表的数据可以直接 truncate ，不产生undo。做并行恢复 FAST_START_PARALLEL_ROLLBACK。增加 UNDO_RETENTION 。 环境模拟

创建测试表 tab_roll

create tablespace rollspace datafile '/u01/oradata/rollspace01.dbf' size 2G autoextend off; create user PANDAS IDENTIFIED by "oracle" default tablespace rollspace ; create table tab_roll as select * from dba_objects where 1=0; conn pandas/oracle begin for i in 1..20 LOOP insert into /*+ append */ tab_roll nologing select * from dba_objects; commit; end LOOP; end; / PANDAS@orcl> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => '&TOWN' , tabname => ' &TNAME' , cascade => true, estimate_percent => dbms_stats.auto_sample_size, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', degree => 8,no_invalidate=>false); Enter value for town: PANDAS Enter value for tname: tab_roll PL/SQL procedure successfully completed. PANDAS@orcl> @seg pandas.tab_roll SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK ---------- ------ ------------ ------------- ------------- -------------------------- ---------- ---------- 200 PANDAS TAB_ROLL TABLE ROLLSPACE 25600 7 130 测试 1、并行回滚

业务的事管不着，来看看数据库层面怎么解决？

FAST_START_PARALLEL_ROLLBACK指定恢复终止的事务时使用的并行度。

财产

描述

参数类型

String

句法

FAST_START_PARALLEL_ROLLBACK = { HIGH | LOW | FALSE }

默认值

LOW

可修改

ALTER SYSTEM

在 PDB 中修改

从版本 19.17 开始，此参数可以在 Oracle Database 19c 中的 PDB 中修改。

基本的

不

终止事务是系统故障前处于活动状态的事务。如果系统在有未提交的并行 DML 或 DDL 事务时发生故障，则可以使用此参数加快启动期间的事务恢复。

Database Reference

测试FAST_START_PARALLEL_ROLLBACK各个值回滚速度

FAST_START_PARALLEL_ROLLBACK = FALSE;

SYS@orcl> ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = FALSE; System altered. SYS@orcl> @p FAST_START_PARALLEL_ROLLBACK NAME VALUE ---------------------------------------- ---------------------------------------- fast_start_parallel_rollback FALSE SYS@orcl> set timing on; SYS@orcl> delete from PANDAS.tab_roll; 1744280 rows deleted. Elapsed: 00:00:18.80 SYS@orcl> rollback; Rollback complete. Elapsed: 00:00:14.56

FAST_START_PARALLEL_ROLLBACK = LOW;

SYS@orcl> ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = LOW; System altered. SYS@orcl> @p FAST_START_PARALLEL_ROLLBACK NAME VALUE ---------------------------------------- ---------------------------------------- fast_start_parallel_rollback LOW SYS@orcl> set timing on; SYS@orcl> delete from PANDAS.tab_roll; 1744280 rows deleted. Elapsed: 00:00:16.75 SYS@orcl> rollback; Rollback complete. Elapsed: 00:00:17.65

FAST_START_PARALLEL_ROLLBACK = HIGH;

SYS@orcl> ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = HIGH; System altered. SYS@orcl> @p FAST_START_PARALLEL_ROLLBACK NAME VALUE ---------------------------------------- ---------------------------------------- fast_start_parallel_rollback HIGH SYS@orcl> set timing on; SYS@orcl> delete from PANDAS.tab_roll; 1744280 rows deleted. Elapsed: 00:00:19.17 SYS@orcl> rollback; Rollback complete. Elapsed: 00:00:15.63

FAST_START_PARALLEL_ROLLBACK 在不同设置下的影响：

FAST_START_PARALLEL_ROLLBACK

DELETE

ROLLBACK

FALSE（禁用并行回滚）

18.80s

14.56s

LOW（10g默认值，最大的rollback进程为2*cpu_count个）

16.75s

17.65s

HIGH（最大的rollback进程为4*cpu_count个）

19.17s

15.63s

测试结果并不符合想象。

为什么 FALSE 反而比 LOW/HIGH 更快？

idle很高说明CPU不是限制，测试环境两核CPU，可能是多个进程在访问undo的时候发生了死锁，注意iowait再次测试一下

[root@orcl:/u01]$ cat /proc/cpuinfo |grep pro processor : 0 processor : 1 [root@orcl:/u01]$ sar Linux 3.10.0-957.el7.x86_64 (orcl) 2025年03月04日 _x86_64_ (2 CPU) 23时00分01秒 CPU %user %nice %system %iowait %steal %idle 23时10分01秒 all 0.17 0.00 0.58 0.05 0.00 99.19 23时20分01秒 all 3.19 0.00 4.07 1.14 0.00 91.59 平均时间: all 1.68 0.00 2.32 0.59 0.00 95.41 [root@orcl:/u01]$ sar -d -f /var/log/sa/sa04 Linux 3.10.0-957.el7.x86_64 (orcl) 2025年03月04日 _x86_64_ (2 CPU) 23时00分01秒 DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 23时10分01秒 dev8-0 17.10 234.08 7049.70 426.04 0.08 4.56 0.32 0.55 23时10分01秒 dev11-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 23时10分01秒 dev253-0 1.96 24.09 40.89 33.13 0.00 0.55 0.44 0.09 23时10分01秒 dev253-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 23时10分01秒 dev253-2 16.16 209.99 7008.64 446.70 0.15 8.86 0.29 0.46 23时10分01秒 dev253-3 0.02 0.00 0.16 8.82 0.00 0.64 0.27 0.00 23时20分01秒 dev8-0 492.28 675.44 41397.50 85.47 0.10 0.20 0.16 7.75 23时20分01秒 dev11-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 23时20分01秒 dev253-0 356.29 161.71 37309.07 105.17 0.08 0.22 0.17 5.99 23时20分01秒 dev253-1 31.03 13.44 234.78 8.00 0.04 1.40 0.05 0.15 23时20分01秒 dev253-2 134.72 500.29 3853.50 32.32 0.02 0.16 0.12 1.65 23时20分01秒 dev253-3 0.02 0.00 0.15 9.20 0.00 0.20 0.20 0.00 平均时间: dev8-0 254.69 454.76 24223.74 96.90 0.09 0.35 0.16 4.15 平均时间: dev11-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 平均时间: dev253-0 179.13 92.90 18675.14 104.77 0.04 0.22 0.17 3.04 平均时间: dev253-1 15.51 6.72 117.39 8.00 0.02 1.40 0.05 0.07 平均时间: dev253-2 75.44 355.14 5431.06 76.70 0.09 1.09 0.14 1.06 平均时间: dev253-3 0.02 0.00 0.16 9.00 0.00 0.43 0.24 0.00

再次测试并行回滚时间比false长

FAST_START_PARALLEL_ROLLBACK

DELETE

ROLLBACK

FALSE（禁用并行回滚）

16.48s

18.21s

HIGH（最大的rollback进程为4*cpu_count个）

22.37s

15.07s

同时发现开并行时候 r_await 等待时间(ms) 比较长。

2、杀死后台进程

未测试

select 'ho kill -9 '||spid from v$process where addr in(select paddr from v$session where sid=);

此时为保证数据库一致性，应该有后台进程恢复