Oracle逐行删除操作完成后进程仍长时间运行原因排查

我执行以下Oracle代码时耗时极长：

create table tmp$_mkemp_2 (id number generated by default on null as identity primary key, c1 varchar2 (30), c2 varchar2 (30))
/

set timing on serveroutput on size unlimited linesize 32767 pagesize 50000 trimspool on tab off feedback on heading on verify on
define svTableName = tmp$_mkemp_2
define svInsertHowMany = 500000
define svFetchRate = 2000
define svUpdateWhichMod = 2
define svDeleteWhichMod = 10
col ts format a20
col cnt format a10
<<s01_row_by_row_rbr_dml>> begin <<rbr_loop>> for iRec in (select dbms_random.string ('p', 10) c1, level id from dual connect by level <= &svInsertHowMany) loop
          insert into &svTableName (c1) values (iRec.c1);
          if mod (iRec.id, &svFetchRate) = 0 then commit; end if; end loop rbr_loop; commit;
     <<update_loop>> for iUpdateRec in (select id, c1 from &svTableName) loop
          update &svTableName set c2 = substr (c1, 1, 2) || ' ## ' || id where mod (id, &svUpdateWhichMod) = 0 and id = iUpdateRec.id;
          if mod (iUpdateRec.id, &svFetchRate) = 0 then commit; end if; end loop update_loop; commit;
     <<delete_loop>> for iDeleteRec in (select id, c1 from &svTableName) loop
          delete &svTableName where id = iDeleteRec.id and mod (iDeleteRec.id, &svDeleteWhichMod) = 0;
          if mod (iDeleteRec.id, &svFetchRate) = 0 then commit; end if; end loop delete_loop; commit;
     end s01_row_by_row_rbr_dml;
/
select to_char (sysdate, 'mm/dd/yyyy hh24:mi:ss') ts from dual
/
select count (0) cnt from &svTableName
/

当从另一个会话查看表计数时，显示为450000，说明delete_loop的实际删除操作已完成（耗时约10分钟），但原会话的进程仍持续运行数小时。
该问题在更小数据集下可复现：插入50000行时，删除完成（表剩余45000行）耗时约1分钟，但整个进程需6分钟才结束。

我知晓这并非正确的数据处理方式，但希望了解该方式的运行性能细节，请问为何工作完成后事务仍持续运行？

原因分析 #

• 一致性读快照的特性：delete_loop的游标基于select id, c1 from &svTableName打开时，Oracle会创建一个一致性读快照，这个快照记录了游标打开瞬间的数据状态。后续循环中即使删除了行，游标仍需遍历快照中的所有原始行（包括已被删除的行），直到游标完全耗尽——也就是说，哪怕物理上已经删除了10%的行，循环还是要走完最初的50万次迭代，大部分迭代只是执行无实际效果的delete判断。
• 频繁提交不影响游标遍历：每2000行提交一次的操作，只会提交当前事务的修改，但不会改变游标的一致性读快照。游标会持续基于初始快照完成所有行的遍历，不会因为数据的实时变化提前终止。
• 逐行处理的固有低效放大：逐行DML本身就远低于批量操作的效率，而这里的游标逻辑没有适配数据变化，导致删除完成后仍要执行大量无意义的循环迭代，这是进程长时间运行的核心原因。

内容的提问来源于stack exchange，提问作者M. Kemp