SQL查询性能排查与优化：批量更新耗时过长问题咨询

我执行以下PL/SQL脚本时，仅更新表中23%的记录就耗时约2小时。该Employees表约有170K条记录，包含150列。

原始循环更新脚本：

DECLARE
    V_EMP_SAL NUMBER;
BEGIN

For EmpIDs in (SELECT EmpID From Employees Where Emp_Address is null and Emp_phone is null and Emp_active='Y')

LOOP

  SELECT SUM(EMP_SAL) into V_EMP_SAL FROM Employees 
  where Emp_ID=EmpIDs.EmpID and Emp_Active='Y';

  UPDATE Employees
  Set EMP_SAL_HIKE = SAL- V_EMP_SAL
  WHERE EMP_ID=EmpIDs.EmpID;

  COMMIT;

END LOOP;

EXCEPTION
 WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('ERROR OCCURED');
END;

改写为单条UPDATE语句后，耗时依然相近：

UPDATE Employees E1
Set E1.EMP_SAL_HIKE = E1.SAL- (SELECT SUM(EMP_SAL) FROM Employees 
                              where Emp_ID=E1.EmpID and Emp_Active='Y')
WHERE E1.Emp_Address is null 
AND E1.Emp_phone is null 
AND E1.Emp_active='Y';

我有以下技术问题：

1. 为何该更新操作耗时如此之久？
2. 如何定位操作中的耗时环节？
3. 如何优化该操作以大幅提升执行速度？

1. 耗时原因分析 #

• 循环脚本的核心问题：
  • 单次循环执行SELECT SUM+UPDATE，且每次循环都执行COMMIT，频繁提交会触发redo日志的高频刷新，产生大量事务开销；同时遍历约39K条记录，相当于重复执行39K次查询和更新，累加成本极高。
  • 无批量处理逻辑，单条SQL的执行次数直接放大了耗时。
• 单条UPDATE的核心问题：
  • 关联子查询会对每一条符合过滤条件的记录单独执行一次SUM计算，同样等价于39K次独立查询，效率极低。
  • 表包含150列，即使只更新一列，数据库仍需处理整行数据的读写（包括undo日志生成），大尺寸行数据会额外消耗资源。
  • 缺失针对性索引：若Emp_ID、Emp_Active、Emp_Address、Emp_phone无合适索引，无论是过滤条件筛选还是子查询匹配，都会触发全表扫描，170K行的全表扫描重复执行多次，耗时必然飙升。

2. 耗时环节定位方法 #

• 执行计划分析：
  用EXPLAIN PLAN FOR生成执行计划，查看具体执行逻辑：

  EXPLAIN PLAN FOR
  UPDATE Employees E1
  Set E1.EMP_SAL_HIKE = E1.SAL- (SELECT SUM(EMP_SAL) FROM Employees 
                                where Emp_ID=E1.EmpID and Emp_Active='Y')
  WHERE E1.Emp_Address is null 
  AND E1.Emp_phone is null 
  AND E1.Emp_active='Y';
  

  执行SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);查看结果，重点关注是否存在全表扫描（TABLE ACCESS FULL）、高开销嵌套循环，以及子查询的执行方式。
• SQL跟踪与性能视图：
  • 开启SQL_TRACE或使用DBMS_PROFILER跟踪脚本执行，查看各步骤的耗时分布，区分是查询、更新还是提交环节拖慢速度。
  • 查询V$SESSION_WAIT、V$SQL视图，查看该SQL的等待事件（如IO等待、锁等待），以及逻辑读、物理读数值，判断是IO瓶颈还是CPU瓶颈。

3. 优化方案 #

方案一：预计算聚合值，避免重复子查询 #

先批量计算每个EmpID对应的SUM(EMP_SAL)，再关联更新，彻底消除重复子查询：

-- 创建临时表存储聚合结果
CREATE GLOBAL TEMPORARY TABLE Emp_Sal_Agg (
    EmpID NUMBER,
    Total_Sal NUMBER
) ON COMMIT PRESERVE ROWS;

-- 批量计算符合条件的EmpID薪资总和
INSERT INTO Emp_Sal_Agg
SELECT EmpID, SUM(EMP_SAL)
FROM Employees
WHERE Emp_Active='Y'
GROUP BY EmpID;

-- 关联临时表批量更新
UPDATE Employees E1
SET E1.EMP_SAL_HIKE = E1.SAL - ESA.Total_Sal
FROM Employees E1
JOIN Emp_Sal_Agg ESA ON E1.EmpID = ESA.EmpID
WHERE E1.Emp_Address is null 
AND E1.Emp_phone is null 
AND E1.Emp_active='Y';

COMMIT;

方案二：添加针对性索引 #

• 为过滤条件创建复合索引，加速记录筛选：

  CREATE INDEX idx_employees_filter ON Employees(Emp_active, Emp_Address, Emp_phone);
  

• 为子查询关联字段创建覆盖索引，避免回表查询：

  -- Oracle 12c+支持INCLUDE语法，直接在索引中包含EMP_SAL，无需访问表数据
  CREATE INDEX idx_employees_empid_active ON Employees(EmpID, Emp_Active) INCLUDE (EMP_SAL);
  

方案三：调整事务提交策略（针对循环脚本） #

若必须使用循环，将单次提交改为批量提交，减少日志刷新开销：

DECLARE
    V_EMP_SAL NUMBER;
    V_COUNT NUMBER := 0;
BEGIN
    For EmpIDs in (SELECT EmpID From Employees Where Emp_Address is null and Emp_phone is null and Emp_active='Y')
    LOOP
        SELECT SUM(EMP_SAL) into V_EMP_SAL FROM Employees 
        where Emp_ID=EmpIDs.EmpID and Emp_Active='Y';

        UPDATE Employees
        Set EMP_SAL_HIKE = SAL- V_EMP_SAL
        WHERE EMP_ID=EmpIDs.EmpID;

        V_COUNT := V_COUNT + 1;
        -- 每1000条记录提交一次
        IF V_COUNT MOD 1000 = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
    -- 提交剩余未批量的记录
    COMMIT;
EXCEPTION
 WHEN OTHERS THEN
   ROLLBACK;
   DBMS_OUTPUT.PUT_LINE('ERROR OCCURED: ' || SQLERRM);
END;

方案四：使用FORALL批量更新 #

利用PL/SQL的FORALL语句实现批量处理，比普通循环效率提升显著：

DECLARE
    TYPE EmpID_Table IS TABLE OF Employees.EmpID%TYPE;
    V_EmpIDs EmpID_Table;
    TYPE Sal_Table IS TABLE OF NUMBER;
    V_Sals Sal_Table;
BEGIN
    -- 批量获取需要更新的EmpID
    SELECT EmpID BULK COLLECT INTO V_EmpIDs
    FROM Employees 
    WHERE Emp_Address is null and Emp_phone is null and Emp_active='Y';

    -- 批量计算每个EmpID的薪资总和
    SELECT SUM(EMP_SAL) BULK COLLECT INTO V_Sals
    FROM Employees
    WHERE EmpID IN (SELECT COLUMN_VALUE FROM TABLE(V_EmpIDs))
    AND Emp_Active='Y'
    GROUP BY EmpID;

    -- 批量更新数据
    FORALL i IN 1..V_EmpIDs.COUNT
        UPDATE Employees
        SET EMP_SAL_HIKE = SAL - V_Sals(i)
        WHERE EmpID = V_EmpIDs(i);

    COMMIT;
EXCEPTION
 WHEN OTHERS THEN
   ROLLBACK;
   DBMS_OUTPUT.PUT_LINE('ERROR OCCURED: ' || SQLERRM);
END;

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