使用字面量查询快、变量查询慢的Oracle SQL性能优化求助

我在大表KMDW.FT_DEPOSIT_TRANS上执行查询时遇到明显性能差异：

使用字面量的快速查询（耗时几秒） #

SELECT *
    FROM KMDW.FT_DEPOSIT_TRANS a
   WHERE a.DEBIT_CREDIT_IND IN ('C', 'D')
     AND a.CLIENT_NO = '03263872'
     AND a.sym_run_date BETWEEN '01-jan-2022' AND '01-mar-2023'
     AND a.ACCT_NO = a.ACCT_NO;

执行计划 #

Plan hash value: 3678647848                                                                                               

-----------------------------------------------------------------------------------------------------------------------    
| Id  | Operation                          | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    
-----------------------------------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT                   |                  |   193 | 52882 | 13021 (100)| 00:00:14 |       |       |    
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| FT_DEPOSIT_TRANS |   193 | 52882 | 13021 (100)| 00:00:14 | ROWID | ROWID |    
|*  2 |   INDEX SKIP SCAN                  | DEP_TRN_INDX3    |    87 |       | 13012 (100)| 00:00:14 |       |       |    
-----------------------------------------------------------------------------------------------------------------------    

Predicate Information (identified by operation id):                                                                       
---------------------------------------------------                                                                       

   1 - filter("A"."DEBIT_CREDIT_IND"='C' OR "A"."DEBIT_CREDIT_IND"='D')                                                   
   2 - access("A"."SYM_RUN_DATE">=TO_DATE(' 2022-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND                           
              "A"."CLIENT_NO"='03263872' AND "A"."SYM_RUN_DATE"<=TO_DATE(' 2023-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter("A"."CLIENT_NO"='03263872' AND "A"."ACCT_NO" IS NOT NULL)                                                   

使用带NVL变量的慢查询（耗时6分钟） #

SELECT * 
    FROM KMDW.FT_DEPOSIT_TRANS a
   WHERE a.DEBIT_CREDIT_IND IN ('C', 'D')
     AND a.CLIENT_NO = nvl(:IP_CLIENT_NO, a.CLIENT_NO)
     AND a.sym_run_date BETWEEN :IP_FROM_DATE AND :IP_TO_DATE
     AND a.ACCT_NO = nvl(:IP_ACCT_NO, a.ACCT_NO);

执行计划 #

Plan hash value: 3191324441
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                  |     1 |   274 |   384K (10)| 00:06:28 |       |       |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| FT_DEPOSIT_TRANS |     1 |   274 |   384K (10)| 00:06:28 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | DEP_TRN_INDX9    |   176 |       |   384K (10)| 00:06:28 |       |       |
-----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("A"."CLIENT_NO"=NVL('03263872',"A"."CLIENT_NO") AND ("A"."DEBIT_CREDIT_IND"='C' OR 
              "A"."DEBIT_CREDIT_IND"='D'))
   2 - access("A"."SYM_RUN_DATE">=TO_DATE(' 2022-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "A"."SYM_RUN_DATE"<=TO_DATE(' 2023-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter("A"."ACCT_NO"=NVL(NULL,"A"."ACCT_NO"))

我需要保留变量参数，但当前变量版本查询性能极差，请求优化方案。

1. 替换NVL为逻辑分支，恢复索引可用性 #

NVL函数会导致数据库无法使用CLIENT_NO和ACCT_NO上的索引，改成条件分支写法，让优化器能根据变量是否为空选择高效执行计划：

SELECT *
FROM KMDW.FT_DEPOSIT_TRANS a
WHERE a.DEBIT_CREDIT_IND IN ('C', 'D')
  AND a.sym_run_date BETWEEN :IP_FROM_DATE AND :IP_TO_DATE
  AND ( :IP_CLIENT_NO IS NULL OR a.CLIENT_NO = :IP_CLIENT_NO )
  AND ( :IP_ACCT_NO IS NULL OR a.ACCT_NO = :IP_ACCT_NO );

这种写法保留了索引访问路径，当变量不为空时，优化器可以复用字面量查询时的DEP_TRN_INDX3组合索引。

2. 强制指定高效索引（若分支写法仍不生效） #

如果优化器仍选择低效索引，可使用索引提示强制使用原高效索引：

SELECT /*+ INDEX(a DEP_TRN_INDX3) */ *
FROM KMDW.FT_DEPOSIT_TRANS a
WHERE a.DEBIT_CREDIT_IND IN ('C', 'D')
  AND a.sym_run_date BETWEEN :IP_FROM_DATE AND :IP_TO_DATE
  AND ( :IP_CLIENT_NO IS NULL OR a.CLIENT_NO = :IP_CLIENT_NO )
  AND ( :IP_ACCT_NO IS NULL OR a.ACCT_NO = :IP_ACCT_NO );

3. 更新统计信息，修正执行计划估算 #

如果数据库因统计信息过时生成错误执行计划，更新表和索引的统计信息：

EXEC DBMS_STATS.GATHER_TABLE_STATS('KMDW', 'FT_DEPOSIT_TRANS', CASCADE => TRUE);

4. 固化高效执行计划 #

对于稳定的查询模式，可使用SQL Profile或SQL Plan Baseline固化高效执行计划，避免优化器选择低效路径：

• 创建SQL Profile：通过DBMS_SQLTUNE.CREATE_SQL_PROFILE绑定高效计划
• 创建SQL Plan Baseline：通过DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE保存现有高效计划

5. 检查索引有效性 #

确认DEP_TRN_INDX3索引包含SYM_RUN_DATE、CLIENT_NO等关键列且未失效：

SELECT index_name, status, column_name
FROM all_ind_columns
WHERE table_name = 'FT_DEPOSIT_TRANS' AND owner = 'KMDW'
AND index_name = 'DEP_TRN_INDX3';

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