Oracle参数嗅探问题及存储过程性能优化求助

背景 #

此前使用SQL Server时，通过将输入参数赋值给局部变量的方式处理参数嗅探问题，示例代码：

CREATE PROCEDURE dbo.MyProcedure
    (@Param1 INT)
AS
    DECLARE @MyParam1 INT
    SET @MyParam1 = @Param1

    SELECT * 
    FROM dbo.MyTable 
    WHERE ColumnName = @MyParam1 
GO

现尝试优化Oracle存储过程，参照同样方式改写后，耗时仍维持在30-32秒，未达预期效果。改写后的存储过程代码如下：

CREATE OR REPLACE PROCEDURE GetData_Rewrite 
(
  p_Id1  IN NUMBER,
  p_Id2 IN NUMBER,
  o_ResultSet     OUT SYS_REFCURSOR
) AS

 v_MyParam1 NUMBER;
 v_MyParam2 NUMBER;

BEGIN
v_MyParam1 := p_Id1;
v_MyParam2 := p_Id2;

  OPEN o_ResultSet FOR
    SELECT * FROM (
      SELECT
         C.CONTRACTD_TYPE_RANK,
         C.CONTRACT_SET_ID,
         NVL(cte.Contract_type_Desc, CT.CONTRACT_TYPE_NAME) AS CONTRACT_TYPE_NAME,
         C.CONTRACT_SET_NAME,
         C.TERM_GROUP_NAME,
         C.LAST_UPDATE_DATE,
         C.CALC_START_DATE,
         CASE C.CALC_END_DATE WHEN TO_DATE('01-01-3000', 'dd-mm-yyyy') THEN NULL ELSE C.CALC_END_DATE END AS CALC_END_DATE,
         C.CALC_NUM_ROWS,
         C.EDIT_STATUS_CODE AS EDIT_STATUS_CODE
      FROM
        (SELECT
          cs.CONTRACT_SET_NAME,
          cs.enterprise_id,
          MAX(cd.GROUP_NAME) AS TERM_GROUP_NAME,
          MAX(cd.UPDATE_DATE) AS LAST_UPDATE_DATE,
          MIN(cd.START_DATE) AS CALC_START_DATE,
          MAX(NVL(cd.END_DATE, TO_DATE('01-01-3000', 'dd-mm-yyyy'))) AS CALC_END_DATE,
          COUNT(*) AS CALC_NUM_ROWS,
          cd.CONTRACTD_TYPE_RANK,
          cd.CONTRACT_SET_ID,
          MAX(
            CASE
              WHEN NVL(ced.EDIT_STATUS, 'Original') = 'Original' THEN 0
              WHEN NVL(ced.EDIT_STATUS, 'Original') = 'New' THEN 1
              WHEN NVL(ced.EDIT_STATUS, 'Original')  = 'Deleted' THEN 2
              WHEN INSTR(NVL(ced.EDIT_STATUS, 'Original'), 'Locked') = 1 THEN 3
              WHEN NVL(ced.EDIT_STATUS, 'Original') = 'Changed' THEN 4
              ELSE 0
            END
          ) AS EDIT_STATUS_CODE
        FROM CNTR_CONTRACTD cd
        JOIN CNTR_CONTRACT_SET cs ON (cd.CONTRACT_SET_ID = cs.CONTRACT_SET_ID AND cd.enterprise_id = cs.enterprise_id)
        LEFT JOIN CNTR_EDIT_DET ced ON (cd.CONTRACT_DETAIL_ID = ced.CONTRACT_DETAIL_ID)
        WHERE cs.ENTERPRISE_ID = v_MyParam1
          AND cd.CONTRACT_SET_ID = v_MyParam2
        GROUP BY
          cs.CONTRACT_SET_NAME,
          cs.enterprise_id,
          UPPER(cd.GROUP_NAME),
          cd.CONTRACTD_TYPE_RANK,
          cd.CONTRACT_SET_ID
        ) C
      JOIN CNTR_CONTRACTD_TYPE CT ON (C.CONTRACTD_TYPE_RANK = CT.CONTRACTD_TYPE_RANK)
      LEFT JOIN CNTR_CONTRACTD_TYPE_ENT CTE ON (CTE.CONTRACTD_TYPE_ID = CT.CONTRACTD_TYPE_ID AND CTE.enterprise_id = C.enterprise_id)
    )
    ORDER BY CONTRACT_TYPE_NAME, CONTRACT_SET_NAME, TERM_GROUP_NAME;
END GetData_Rewrite; 

一、参数嗅探针对性优化 #

Oracle确实存在参数嗅探问题：优化器会在存储过程首次执行时，根据传入的参数值生成执行计划并缓存，后续执行直接复用该计划。若后续传入参数对应的数据分布与首次差异极大，会导致执行效率低下。仅通过局部变量赋值的方式，在Oracle中可能无法完全规避该问题，可尝试以下方式：

1. 使用OPTIMIZE FOR提示 #

直接指定优化器针对特定高频参数值生成执行计划，避免因参数分布不均导致的计划偏差：

OPEN o_ResultSet FOR
  SELECT
     C.CONTRACTD_TYPE_RANK,
     C.CONTRACT_SET_ID,
     NVL(cte.Contract_type_Desc, CT.CONTRACT_TYPE_NAME) AS CONTRACT_TYPE_NAME,
     C.CONTRACT_SET_NAME,
     C.TERM_GROUP_NAME,
     C.LAST_UPDATE_DATE,
     C.CALC_START_DATE,
     CASE C.CALC_END_DATE WHEN TO_DATE('01-01-3000', 'dd-mm-yyyy') THEN NULL ELSE C.CALC_END_DATE END AS CALC_END_DATE,
     C.CALC_NUM_ROWS,
     C.EDIT_STATUS_CODE AS EDIT_STATUS_CODE
  FROM
    (SELECT
      -- 内层查询内容不变
    ) C
  JOIN CNTR_CONTRACTD_TYPE CT ON (C.CONTRACTD_TYPE_RANK = CT.CONTRACTD_TYPE_RANK)
  LEFT JOIN CNTR_CONTRACTD_TYPE_ENT CTE ON (CTE.CONTRACTD_TYPE_ID = CT.CONTRACTD_TYPE_ID AND CTE.enterprise_id = C.enterprise_id)
  ORDER BY CONTRACT_TYPE_NAME, CONTRACT_SET_NAME, TERM_GROUP_NAME
  OPTION (OPTIMIZE FOR (v_MyParam1 = 100, v_MyParam2 = 200)); -- 替换为实际业务中的高频参数值

2. 动态SQL强制生成适配计划 #

通过动态拼接SQL，让Oracle每次执行时根据实际参数生成适配的执行计划，适用于参数值数据分布差异极大的场景：

CREATE OR REPLACE PROCEDURE GetData_Rewrite 
(
  p_Id1  IN NUMBER,
  p_Id2 IN NUMBER,
  o_ResultSet     OUT SYS_REFCURSOR
) AS
  v_SQL VARCHAR2(4000);
BEGIN
  v_SQL := 'SELECT
             C.CONTRACTD_TYPE_RANK,
             C.CONTRACT_SET_ID,
             NVL(cte.Contract_type_Desc, CT.CONTRACT_TYPE_NAME) AS CONTRACT_TYPE_NAME,
             C.CONTRACT_SET_NAME,
             C.TERM_GROUP_NAME,
             C.LAST_UPDATE_DATE,
             C.CALC_START_DATE,
             CASE C.CALC_END_DATE WHEN TO_DATE(''01-01-3000'', ''dd-mm-yyyy'') THEN NULL ELSE C.CALC_END_DATE END AS CALC_END_DATE,
             C.CALC_NUM_ROWS,
             C.EDIT_STATUS_CODE AS EDIT_STATUS_CODE
           FROM
             (SELECT
               cs.CONTRACT_SET_NAME,
               cs.enterprise_id,
               MAX(cd.GROUP_NAME) AS TERM_GROUP_NAME,
               MAX(cd.UPDATE_DATE) AS LAST_UPDATE_DATE,
               MIN(cd.START_DATE) AS CALC_START_DATE,
               MAX(NVL(cd.END_DATE, TO_DATE(''01-01-3000'', ''dd-mm-yyyy''))) AS CALC_END_DATE,
               COUNT(*) AS CALC_NUM_ROWS,
               cd.CONTRACTD_TYPE_RANK,
               cd.CONTRACT_SET_ID,
               MAX(
                 CASE
                   WHEN NVL(ced.EDIT_STATUS, ''Original'') = ''Original'' THEN 0
                   WHEN NVL(ced.EDIT_STATUS, ''Original'') = ''New'' THEN 1
                   WHEN NVL(ced.EDIT_STATUS, ''Original'')  = ''Deleted'' THEN 2
                   WHEN INSTR(NVL(ced.EDIT_STATUS, ''Original''), ''Locked'') = 1 THEN 3
                   WHEN NVL(ced.EDIT_STATUS, ''Original'') = ''Changed'' THEN 4
                   ELSE 0
                 END
               ) AS EDIT_STATUS_CODE
             FROM CNTR_CONTRACTD cd
             JOIN CNTR_CONTRACT_SET cs ON (cd.CONTRACT_SET_ID = cs.CONTRACT_SET_ID AND cd.enterprise_id = cs.enterprise_id)
             LEFT JOIN CNTR_EDIT_DET ced ON (cd.CONTRACT_DETAIL_ID = ced.CONTRACT_DETAIL_ID)
             WHERE cs.ENTERPRISE_ID = :1
               AND cd.CONTRACT_SET_ID = :2
             GROUP BY
               cs.CONTRACT_SET_NAME,
               cs.enterprise_id,
               UPPER(cd.GROUP_NAME),
               cd.CONTRACTD_TYPE_RANK,
               cd.CONTRACT_SET_ID
             ) C
           JOIN CNTR_CONTRACTD_TYPE CT ON (C.CONTRACTD_TYPE_RANK = CT.CONTRACTD_TYPE_RANK)
           LEFT JOIN CNTR_CONTRACTD_TYPE_ENT CTE ON (CTE.CONTRACTD_TYPE_ID = CT.CONTRACTD_TYPE_ID AND CTE.enterprise_id = C.enterprise_id)
           ORDER BY CONTRACT_TYPE_NAME, CONTRACT_SET_NAME, TERM_GROUP_NAME';
  
  OPEN o_ResultSet FOR v_SQL USING p_Id1, p_Id2;
END GetData_Rewrite; 

二、查询逻辑与索引优化 #

1. 统一分组与聚合字段 #

原查询中GROUP BY使用UPPER(cd.GROUP_NAME)，但聚合时用MAX(cd.GROUP_NAME)，存在隐式转换，可统一为UPPER(cd.GROUP_NAME)减少计算开销：

MAX(UPPER(cd.GROUP_NAME)) AS TERM_GROUP_NAME
-- GROUP BY中保留UPPER(cd.GROUP_NAME)不变

2. 创建针对性复合索引 #

根据查询的过滤条件、连接条件和聚合字段创建复合索引，减少全表扫描和排序开销：

• 针对CNTR_CONTRACTD表：

  CREATE INDEX IDX_CNTR_CONTRACTD_FILTER ON CNTR_CONTRACTD (CONTRACT_SET_ID)
  INCLUDE (GROUP_NAME, UPDATE_DATE, START_DATE, END_DATE, CONTRACTD_TYPE_RANK, CONTRACT_DETAIL_ID);
  

• 针对CNTR_CONTRACT_SET表：

  CREATE INDEX IDX_CNTR_CONTRACT_SET_ENT ON CNTR_CONTRACT_SET (ENTERPRISE_ID, CONTRACT_SET_ID)
  INCLUDE (CONTRACT_SET_NAME);
  

• 针对CNTR_EDIT_DET表：

  CREATE INDEX IDX_CNTR_EDIT_DET_DETAIL ON CNTR_EDIT_DET (CONTRACT_DETAIL_ID);
  

3. 移除冗余嵌套查询 #

原查询外层的SELECT * FROM (...)无实际作用，可直接简化，减少查询层级：

OPEN o_ResultSet FOR
  SELECT
     C.CONTRACTD_TYPE_RANK,
     C.CONTRACT_SET_ID,
     NVL(cte.Contract_type_Desc, CT.CONTRACT_TYPE_NAME) AS CONTRACT_TYPE_NAME,
     C.CONTRACT_SET_NAME,
     C.TERM_GROUP_NAME,
     C.LAST_UPDATE_DATE,
     C.CALC_START_DATE,
     CASE C.CALC_END_DATE WHEN TO_DATE('01-01-3000', 'dd-mm-yyyy') THEN NULL ELSE C.CALC_END_DATE END AS CALC_END_DATE,
     C.CALC_NUM_ROWS,
     C.EDIT_STATUS_CODE AS EDIT_STATUS_CODE
  FROM
    (SELECT
      -- 内层聚合查询内容不变
    ) C
  JOIN CNTR_CONTRACTD_TYPE CT ON (C.CONTRACTD_TYPE_RANK = CT.CONTRACTD_TYPE_RANK)
  LEFT JOIN CNTR_CONTRACTD_TYPE_ENT CTE ON (CTE.CONTRACTD_TYPE_ID = CT.CONTRACTD_TYPE_ID AND CTE.enterprise_id = C.enterprise_id)
  ORDER BY CONTRACT_TYPE_NAME, CONTRACT_SET_NAME, TERM_GROUP_NAME;

三、执行计划分析定位瓶颈 #

使用EXPLAIN PLAN生成执行计划，定位全表扫描、排序等耗时环节：

EXPLAIN PLAN FOR
SELECT
  -- 完整查询内容
FROM ...
ORDER BY ...;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

重点关注TABLE ACCESS FULL（全表扫描）、SORT GROUP BY（分组排序）、SORT ORDER BY（最终排序）等操作，针对性优化。

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