Oracle如何监控大批量INSERT等DML操作的执行进度

用户场景中的目标MERGE语句如下：

MERGE INTO huge_table ht
USING (
    SELECT column1, column2
    FROM another_huge_table 
) aht ON (ht.column1 = aht.column1)
WHEN NOT MATCHED THEN
INSERT (column1, column2)
VALUES (aht.column1, aht.column2);
COMMIT;

针对百万级以上大表的MERGE这类长时间运行的DML，Oracle提供了多个内置视图可以实现进度监控，常用方案如下： #

方案1：使用V$SESSION_LONGOPS内置视图（最常用） #

Oracle会自动将运行时间超过6秒的全表扫描、哈希连接、DML等操作记录到该视图，只要对应表的统计信息准确，即可得到较可靠的进度预估。

操作步骤： #

1. 先找到目标MERGE操作对应的会话SID、SERIAL#，可以通过V$SESSION视图过滤SQL_TEXT、用户名等字段定位：

SELECT SID, SERIAL#, SQL_ID, USERNAME, PROGRAM 
FROM V$SESSION 
WHERE SQL_TEXT LIKE '%MERGE INTO huge_table%' 
AND STATUS = 'ACTIVE';

2. 用拿到的SID查询进度：

SELECT 
  OPNAME,
  SOFAR, -- 已完成的工作量单位
  TOTALWORK, -- 预估总工作量
  ROUND(SOFAR/TOTALWORK*100,2) || '%' AS COMPLETE_RATE, -- 完成百分比
  ELAPSED_SECONDS, -- 已运行时长（秒）
  TIME_REMAINING -- 预估剩余时长（秒）
FROM V$SESSION_LONGOPS
WHERE SID = 替换为你的会话SID
AND TOTALWORK > 0
ORDER BY START_TIME DESC;

方案2：通过事务Undo块变化估算（适合统计信息不准的场景） #

大DML操作会持续生成Undo数据，通过监控事务消耗的Undo块增长速度，也可以估算整体进度：

SELECT 
  SID,
  USED_UBLK, -- 已使用的Undo块数量
  USED_UREC, -- 已生成的Undo记录数
  START_TIME
FROM V$TRANSACTION t
JOIN V$SESSION s ON t.ADDR = s.TADDR
WHERE s.SID = 替换为你的会话SID;

注：使用该方案需要先预估全量操作大概需要消耗的Undo块总数，可以先拿10%的测试数据跑一次得到单位数据量的Undo消耗，再乘以总数据量得到总预估Undo块数，再计算当前完成比例。

方案3：12c及以上版本使用SQL实时监控功能 #

Oracle 12c+默认会捕获执行时间超过5秒的SQL的运行细节，直接查V$SQL_MONITOR就能看到更细粒度的执行进度，包括每个执行节点的处理行数、耗时：

SELECT 
  SQL_ID,
  STATUS,
  ELAPSED_TIME/1000000 AS ELAPSED_SECONDS,
  CPU_TIME/1000000 AS CPU_SECONDS,
  BUFFER_GETS,
  DISK_READS,
  ROWS_PROCESSED
FROM V$SQL_MONITOR
WHERE SQL_TEXT LIKE '%MERGE INTO huge_table%'
AND STATUS = 'EXECUTING';

辅助优化技巧 #

执行MERGE之前可以先给当前会话打自定义标签，方便后续快速定位，无需模糊匹配SQL文本：

-- 执行MERGE前先运行
EXEC DBMS_APPLICATION_INFO.SET_MODULE('BULK_MERGE', 'MERGE_huge_table');
-- 后续直接过滤模块名即可找到对应会话
SELECT * FROM V$SESSION WHERE MODULE = 'BULK_MERGE';

注意事项 #

• V$SESSION_LONGOPS的剩余时长是基于当前执行速度估算的，若出现锁等待、IO带宽波动，剩余时间会存在偏差，建议同时查询V$SESSION_WAIT确认会话是否存在堵塞。
• 所有监控查询均为只读操作，不会影响正在运行的DML任务，不要随意执行杀会话、修改表结构等操作避免任务中断。

内容的提问来源于stack exchange，提问作者Aleksandr Beliavski