ORACLE 存储过程性能疑问

Question

最近碰到了个问题，网上都找不到原因，望各位高手帮忙解释解释，谢谢了！
以下是存储过程的部分代码：CREATE OR REPLACE PROCEDURE SP_CMM_SELECT_FEES_QTY_VVL(V_CURSOR                 OUT SYS_REFCURSOR, --返回信息,
                                                       P_VESSEL_CODE            IN SVL_VOYAGE_PLAN.VESSEL_CODE%TYPE,
                                                       P_VOYAGE_NO              IN SVL_VOYAGE_PLAN.VOYAGE_NO%TYPE,
                                                       P_VOYAGE_LEG             IN SVL_VOYAGE_PLAN.VOYAGE_LEG%TYPE,
                                                       P_BL_NO                  IN VARCHAR2,
                                                       P_BALANCE_UNITS          IN VARCHAR2,
                                                       P_SAILLING_DATE_FROM     IN VARCHAR2,
                                                       P_SAILLING_DATE_TO       IN VARCHAR2,
                                                       P_VOYAGE_YEAR            IN VARCHAR2,
                                                       P_VOYAGE_MONTH           IN VARCHAR2,
                                                       P_ASSIGN_WEEK_FROM       IN VARCHAR2,
                                                       P_ASSIGN_WEEK_TO         IN VARCHAR2,
                                                       P_RP_FLAG                IN VARCHAR2,
                                                       P_PORT_CODE              IN VARCHAR2,
                                                       V_BK_FREIGHT_RECEIVE_IDS IN CLOB,
                                                       V_SOA_NO                 IN VARCHAR2, --SOA NO
                                                       V_CHARGE_CODE            IN VARCHAR2,
                                                       V_CURRENCY               IN VARCHAR2) ISBEGIN
  IF (P_RP_FLAG = 'I') THEN
    BEGIN
      --应收
      -- OPEN V_CURSOR FOR
      DELETE FROM CMM_TEMP_AUTO_COUNT_COMMISSION;
      INSERT INTO CMM_TEMP_AUTO_COUNT_COMMISSION C
        (VESSEL_CODE, VOYAGE_NO, VOYAGE_LEG, ETD, RATE_LIST_ID)
        (SELECT VVL.VESSEL_CODE,
                VVL.VOYAGE_NO,
                VVL.VOYAGE_LEG,
                VVL.SAILLING_DATE,
                WMSYS.WM_CONCAT(VVL.CURRENCY || ':' || VVL.AMOUNT) AS AMOUNT
           FROM (SELECT BH.CURRENT_VESSEL_CODE VESSEL_CODE,
                        BH.CURRENT_VOYAGE_NO VOYAGE_NO,
                        BH.CURRENT_VOYAGE_LEG VOYAGE_LEG,
                        PORT.ETD SAILLING_DATE,
                        BF.CURRENCY,
                        SUM(BF.AMOUNT) AMOUNT
                   FROM DOC_BK_FREIGHT_RECEIVE  BF,
                        VW_DOC_BOOKING_HEAD_OBM BH,
                        DOC_BK_ROUTING          ROUTING,
                        SVL_VOYAGE_PLAN         VP,
                        SVL_VOYAGE_PLAN_PORT    PORT
                  WHERE BF.BOOKING_HEAD_ID = BH.BOOKING_HEAD_ID
                    AND BF.PAY_MODEL = 0
                    AND BH.SPLITE_COMBIN_VISIBLE_FLAG = 1
                    AND ......
                    AND (--TRIM(P_PORT_CODE) IS NULL OR
                        BH.CURRENT_PORT = P_PORT_CODE)
                    AND (--TRIM(P_PORT_CODE) IS NULL OR
                        BF.PORT_CODE = P_PORT_CODE)
                    AND BF.PAYER <> 'CNSHA_HQ'
                    ......
                  GROUP BY BH.CURRENT_VESSEL_CODE,
                           BH.CURRENT_VOYAGE_NO,
                           BH.CURRENT_VOYAGE_LEG,
                           BF.CURRENCY,
                           PORT.ETD
) VVL
          GROUP BY VVL.VESSEL_CODE,
                   VVL.VOYAGE_NO,
                   VVL.VOYAGE_LEG,
                   VVL.SAILLING_DATE);
      OPEN V_CURSOR FOR
        SELECT C.VESSEL_CODE,
               C.VOYAGE_NO,
               C.VOYAGE_LEG,
               C.ETD          AS SAILLING_DATE,
               C.RATE_LIST_ID AS AMOUNT
          FROM CMM_TEMP_AUTO_COUNT_COMMISSION C;
    END;
END SP_CMM_SELECT_FEES_QTY_VVL;
这段代码中的AND (--TRIM(P_PORT_CODE) IS NULL OR
                        BH.CURRENT_PORT = P_PORT_CODE)
                    AND (--TRIM(P_PORT_CODE) IS NULL OR
                        BF.PORT_CODE = P_PORT_CODE)
把P_PORT_CODE为空的判断注释掉了，速度很快，之前这个是没有注释掉的，执行一次会卡到死机，请问大家为什么会这样？这个判断为空的语句对性能是因为什么造成了影响呢？说明下，这些表有的是上百万的。谢谢各位了！

Answer 1

我重新排版下，不然看得太乱了。
CREATE OR REPLACE PROCEDURE SP_CMM_SELECT_FEES_QTY_VVL
( V_CURSOR OUT SYS_REFCURSOR,             
  P_VESSEL_CODE IN SVL_VOYAGE_PLAN.VESSEL_CODE%TYPE,
  P_VOYAGE_NO IN SVL_VOYAGE_PLAN.VOYAGE_NO%TYPE,
  P_VOYAGE_LEG IN SVL_VOYAGE_PLAN.VOYAGE_LEG%TYPE,
  P_BL_NO IN VARCHAR2,
  ......
  P_PORT_CODE IN VARCHAR2,
  ......) ISBEGIN
    BEGIN
  DELETE FROM CMM_TEMP_AUTO_COUNT_COMMISSION;
  INSERT INTO CMM_TEMP_AUTO_COUNT_COMMISSION C
  (VESSEL_CODE, VOYAGE_NO, VOYAGE_LEG, ETD, RATE_LIST_ID)
  (SELECT VVL.VESSEL_CODE,
          VVL.VOYAGE_NO,
          VVL.VOYAGE_LEG,
          VVL.SAILLING_DATE,
          WMSYS.WM_CONCAT(VVL.CURRENCY || ':' || VVL.AMOUNT) AS AMOUNT
      FROM (SELECT BH.CURRENT_VESSEL_CODE VESSEL_CODE,
                   BH.CURRENT_VOYAGE_NO VOYAGE_NO,
                   BH.CURRENT_VOYAGE_LEG VOYAGE_LEG,
                   PORT.ETD SAILLING_DATE,
                   BF.CURRENCY,
                   SUM(BF.AMOUNT) AMOUNT
                FROM DOC_BK_FREIGHT_RECEIVE BF,
                     VW_DOC_BOOKING_HEAD_OBM BH,
                     DOC_BK_ROUTING ROUTING,
                     SVL_VOYAGE_PLAN VP,
                     SVL_VOYAGE_PLAN_PORT PORT
             WHERE BF.BOOKING_HEAD_ID = BH.BOOKING_HEAD_ID
               AND BF.PAY_MODEL = 0
               AND BH.SPLITE_COMBIN_VISIBLE_FLAG = 1
               AND ......
               AND (--TRIM(P_PORT_CODE) IS NULL OR
                      BH.CURRENT_PORT = P_PORT_CODE)
               AND (--TRIM(P_PORT_CODE) IS NULL OR
                      BF.PORT_CODE = P_PORT_CODE)
               AND BF.PAYER <> 'CNSHA_HQ'
                 ......
          GROUP BY BH.CURRENT_VESSEL_CODE,
                BH.CURRENT_VOYAGE_NO,
                BH.CURRENT_VOYAGE_LEG,
                BF.CURRENCY,
                PORT.ETD) VVL
  GROUP BY VVL.VESSEL_CODE,
  VVL.VOYAGE_NO,
  VVL.VOYAGE_LEG,
  VVL.SAILLING_DATE);  OPEN V_CURSOR FOR
     SELECT C.VESSEL_CODE,
            C.VOYAGE_NO,
            C.VOYAGE_LEG,
            C.ETD AS SAILLING_DATE,
            C.RATE_LIST_ID AS AMOUNT
     FROM CMM_TEMP_AUTO_COUNT_COMMISSION C;
  END;
END SP_CMM_SELECT_FEES_QTY_VVL;

Answer 2

is null / is not null 都不会用索引三个建议：
1、给这一列加一个默认值：如9999， 不要让他为null
2、在这一列上加一个函数索引 nvl(列,9999)
3、把结果存到中间临时表，然后再过滤

Answer 3

可是我这里P_PORT_CODE是传进来的参数，而不是字段名，这样跟索引也有影响吗？

Answer 4

⊙﹏⊙b汗，没注意看 sorry。
那就是数据量较大，语句写的有问题了。
速度应该影响在，你传入空值的时候吧。
看看你主要sql的执行计划，优化下sql。

Answer 5

TRIM(P_PORT_CODE)，TRIM放在Where中等于自寻短见，所有索引都会失效，上百万不卡死才怪

Answer 6

AND (--TRIM(P_PORT_CODE) IS NULL OR
  BH.CURRENT_PORT = P_PORT_CODE)
  AND (--TRIM(P_PORT_CODE) IS NULL OR
  BF.PORT_CODE = P_PORT_CODE)
用or时候看下执行计划，应该是不走索引了

Answer 7

简陋版:
explain  plan for select ....(你的查询语句) ;
select * from table(dbms_xplan.display);