Oracle存储过程查询性能优化：如何将耗时从26秒降至10-20秒？

原查询耗时约40-50秒，部分场景甚至长达1分钟。经过分析重构C#后端代码与查询逻辑后，当前耗时仍维持在26秒左右，希望找到进一步优化的方法，将耗时降至10-20秒区间。

重构后的存储过程 #

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.COL1 = v_MyParam1
          AND cd.COL2 = 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 GetContractInfos_Rewrite;

已做的优化 #

引入局部变量v_MyParam1和v_MyParam2，用于避免参数嗅探问题，当前应用场景为桌面应用。

C#调用存储过程的代码 #

try 
{
   using(OracleCommand cmd = (OracleCommand) mHibernateSession.Connection.CreateCommand()) 
   {
     cmd.CommandType = CommandType.StoredProcedure;
     cmd.CommandText = "PACKAGE_NAME.GetData_Rewrite";

     cmd.Parameters.Add("p_Id1", val1);
     cmd.Parameters.Add("p_Id2", val2);

     OracleParameter paramCursor = new OracleParameter("o_ResultSet", OracleDbType.RefCursor, 2000,
       ParameterDirection.Output, true, 0, 0, string.Empty, DataRowVersion.Default, DBNull.Value);
     cmd.Parameters.Add(paramCursor);

     using(OracleDataAdapter adapter = new OracleDataAdapter(cmd))
     {
       adapter.ReturnProviderSpecificTypes = false;
       adapter.Fill(dtRows);
     }
   }
 } 
 finally 
 {
 }

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