Oracle数据库MERGE语句执行耗时近5小时 性能优化求助

问题基础信息 #

• 故障现象：Oracle数据库内执行MERGE更新语句总时长达到4小时56分钟
• 现有优化动作：已在目标表SRC_CREATED_DATE、SRC_LAST_UPDATE_DATE、PAY_RANGE_START_DATE_KEY字段创建位图索引
• 原始SQL如下：

MERGE
/*+ parallel(A) enable_parallel_dml*/
INTO
(
  SELECT
    AA_PERSON_ASSIGNMENT_KEY,
    SCHEDULE_LINE_ID,
    AA_PERSON_NATURAL_KEY,
    AA_PERSON_ASGN_NATURAL_KEY,
    AA_PROJECT_NATURAL_KEY,
    SRC_CREATED_DATE,
    SRC_LAST_UPDATE_DATE,
    PAY_RANGE_START_DATE_KEY
  FROM
    EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP
)
A USING
(
  SELECT
    /*+ PARALLEL(2) */
    AA_PERSON_ASSIGNMENT_KEY,
    SCHEDULE_LINE_ID,
    AA_PERSON_NATURAL_KEY,
    AA_PERSON_ASGN_NATURAL_KEY,
    AA_PROJECT_NATURAL_KEY,
    SRC_CREATED_DATE,
    SRC_LAST_UPDATE_DATE,
    NULL
  FROM
    EDWFIN.PSP_LABOR_SCHEDULE_DAY_F_FRS_352
)
B ON
(
  A.AA_PERSON_ASSIGNMENT_KEY                                = B.AA_PERSON_ASSIGNMENT_KEY AND
  A.SCHEDULE_LINE_ID                                        = B.SCHEDULE_LINE_ID AND A.AA_PERSON_NATURAL_KEY =
  B.AA_PERSON_NATURAL_KEY AND A.AA_PERSON_ASGN_NATURAL_KEY  =
  B.AA_PERSON_ASGN_NATURAL_KEY AND A.AA_PROJECT_NATURAL_KEY =
  B.AA_PROJECT_NATURAL_KEY
)
WHEN MATCHED THEN
  UPDATE
  SET
    A.SRC_CREATED_DATE     = B.SRC_CREATED_DATE,
    A.SRC_LAST_UPDATE_DATE = B.SRC_LAST_UPDATE_DATE
  WHERE
    A.SRC_CREATED_DATE       >='01-SEP-2021'
  AND A.SRC_LAST_UPDATE_DATE <= '31-AUG-2022'
  AND A.PAY_RANGE_START_DATE_KEY BETWEEN 2459443 AND 2459943;
  COMMIT;

根因定位 #

• 索引配置完全错配：现有位图索引全部建在过滤字段上，MERGE关联依赖的5个等值连接键无任何索引支撑，大表关联只能走全表扫描+哈希连接，千万级数据量下临时表空间读写、内存换出开销占总耗时70%以上；且位图索引锁粒度为数据块级，批量更新时索引维护成本、锁等待开销会被放大数倍，完全不适合频繁更新的事实表场景。
• 过滤条件后置：目标表的时间、范围过滤条件写在UPDATE子句的WHERE段，Oracle会先完成两表全量数据关联，再对匹配结果做过滤，90%以上的关联计算都是无效计算。
• 并行配置混乱：仅对目标表加了无度数的parallel(A)Hint，源表固定并行度为2，两边并行度不匹配，且未在会话级开启并行DML，Hint大概率不生效，并行资源完全没利用起来。
• 冗余写法干扰执行计划：INTO段用无过滤的子查询包装原表、源表SELECT列表输出无意义的NULL值，都会干扰优化器基数估算，容易生成错误执行计划。
• 日期隐式转换风险：日期条件用字符串写法，依赖数据库NLS_DATE_FORMAT参数，一旦参数不匹配会出现隐式转换，导致现有索引完全失效。

可落地优化步骤 #

1. 改写SQL，前置过滤逻辑 #

先在会话级开启并行DML，清理冗余写法，把目标表过滤条件移到ON子句，关联阶段就裁剪无效数据，统一日期为标准字面量写法，参考SQL：

-- 会话级开启并行DML，保证Hint生效
ALTER SESSION ENABLE PARALLEL DML;

MERGE
/*+ PARALLEL(8) LEADING(B) USE_HASH(A B) */
INTO EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP A
USING (
  SELECT /*+ PARALLEL(8) */
    AA_PERSON_ASSIGNMENT_KEY,
    SCHEDULE_LINE_ID,
    AA_PERSON_NATURAL_KEY,
    AA_PERSON_ASGN_NATURAL_KEY,
    AA_PROJECT_NATURAL_KEY,
    SRC_CREATED_DATE,
    SRC_LAST_UPDATE_DATE
  FROM EDWFIN.PSP_LABOR_SCHEDULE_DAY_F_FRS_352
) B
ON (
  A.AA_PERSON_ASSIGNMENT_KEY = B.AA_PERSON_ASSIGNMENT_KEY
  AND A.SCHEDULE_LINE_ID = B.SCHEDULE_LINE_ID
  AND A.AA_PERSON_NATURAL_KEY = B.AA_PERSON_NATURAL_KEY
  AND A.AA_PERSON_ASGN_NATURAL_KEY = B.AA_PERSON_ASGN_NATURAL_KEY
  AND A.AA_PROJECT_NATURAL_KEY = B.AA_PROJECT_NATURAL_KEY
  AND A.SRC_CREATED_DATE >= DATE '2021-09-01'
  AND A.SRC_LAST_UPDATE_DATE <= DATE '2022-08-31'
  AND A.PAY_RANGE_START_DATE_KEY BETWEEN 2459443 AND 2459943
)
WHEN MATCHED THEN UPDATE
  SET
    A.SRC_CREATED_DATE = B.SRC_CREATED_DATE,
    A.SRC_LAST_UPDATE_DATE = B.SRC_LAST_UPDATE_DATE;

COMMIT;

并行度可根据服务器CPU核数调整，建议设置为物理CPU核数的50%-75%，避免设置过高导致CPU、IO资源争抢。

2. 调整索引策略 #

• 直接删除现有三个字段上的位图索引，避免批量更新时的索引维护开销和锁等待。
• 在目标表上创建覆盖联合B树索引，关联键放前列，过滤条件放后列，关联时可以直接走索引快速定位匹配行，不需要回表：

CREATE INDEX IDX_PSP_ROLLUP_MERGE ON EDWFIN.PSP_LABOR_SCHD_DAY_F_ROLLUP
(AA_PERSON_ASSIGNMENT_KEY, SCHEDULE_LINE_ID, AA_PERSON_NATURAL_KEY, AA_PERSON_ASGN_NATURAL_KEY, AA_PROJECT_NATURAL_KEY, PAY_RANGE_START_DATE_KEY, SRC_CREATED_DATE, SRC_LAST_UPDATE_DATE)
PARALLEL 8 NOLOGGING;
-- 索引创建完成后改回常规属性
ALTER INDEX IDX_PSP_ROLLUP_MERGE NOPARALLEL LOGGING;

• 如果源表PSP_LABOR_SCHEDULE_DAY_F_FRS_352数据量大于100万，同步在该表的5个关联键上创建联合B树索引，提升关联阶段的数据源扫描效率。

3. 前置检查与大更新优化 #

• 执行前更新两表统计信息，避免优化器基数估算偏差：

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    OWNNAME => 'EDWFIN',
    TABNAME => 'PSP_LABOR_SCHD_DAY_F_ROLLUP',
    ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
    CASCADE => TRUE
  );
  DBMS_STATS.GATHER_TABLE_STATS(
    OWNNAME => 'EDWFIN',
    TABNAME => 'PSP_LABOR_SCHEDULE_DAY_F_FRS_352',
    ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
    CASCADE => TRUE
  );
END;
/

• 如果本次更新的数据量超过目标表总数据量的30%，不建议直接用MERGE更新，可改用CREATE TABLE AS SELECT的方式把更新后的数据写入新表，完成后重命名替换原表，效率比直接MERGE高3-10倍，同时不会产生大量行迁移和索引碎片。操作前记得做好数据备份，大表操作时可临时开启NOLOGGING减少Redo生成，操作完成后改回LOGGING并及时备份。

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