title: Oracle Stored Procedure execution plan in case of IF ELSE
body: <p>I have a question, how is the execution plan of a stored procedure created if it has a IF ELSE statement wherein based on the value of a IN parameter IF or ELSE block is executed and the queries in the blocks are fetching same values from same tables but with a different column in where clause.</p>
  
  <p>As far as I know there will be only one execution plan per stored procedure. If a Java application is calling the stored procedure using plain JDBC, the execution plan gets created first time the procedure is called and the block(IF) gets executed is used for creating the execution plan.</p>
  
  <p>Next time when ELSE block is executed it uses the same execution plan and hence the execution will be slow.</p>
  
  <p>I am asking this question because I am calling similar Stored procedure and time of execution is very different based on what value I am passing and which block is executed.</p>
  
  <p>If this is the case what can be done to improve the performance. Should a seperate procedure be written instead of using IF ELSE.</p>
  
  <p>PFB the stored procedure code:</p>
  
  <pre><code>PACKAGE         COP00134_006_1 AS
      TYPE t_cur_type IS REF CURSOR;
      PROCEDURE GetConsignmentDetails(
        sPInputList     IN VARCHAR2,
        sPInputType     IN VARCHAR2,
        cPCon           OUT t_cur_type,
        cPStatus        OUT t_cur_type,
        sPError         OUT VARCHAR2);
  END COP00134_006_1;
  </code></pre>
  
  <p>Implementation:</p>
  
  <pre><code>PACKAGE BODY         COP00134_006_1 AS
      PROCEDURE GetConsignmentDetails(
        sPInputList     IN VARCHAR2,
        sPInputType     IN VARCHAR2,
        cPCon           OUT t_cur_type,
        cPStatus        OUT t_cur_type,
        sPError         OUT VARCHAR2)
      IS
      BEGIN
          sPError := '0';
          IF sPInputType = 'CON' THEN
           OPEN cPCon FOR
             SELECT 
                      con_id
                     ,con_legacy_id
                     ,con_create_td
                     ,con_pickup_lt
                     ,con_deliv_due_lt
                     ,CON_DELIV_END_LT
                     ,SRP_ID_AREA_DEST
                     ,CON_CLNT_REF_TX
                     ,BUL_CSYS_ID_ORIG
                     ,BUL_ID_ORIG
                     ,BUL_NM_ORIG
                     ,BUL_NM_DEST
                     ,cpn_oa_town_nm_r
                     ,cpn_oa_town_nm_d
                     ,cou_iso_id_orig
                     ,Cou_Nm_Orig
                     ,cou_iso_id_dest
                     ,Cou_Nm_Dest
                     ,PCE_QT
                     ,DUPLICATES
                     ,COS_SIGN_NM FROM (
               SELECT  /*+ cardinality (b 3) */ 
                      co.con_id
                     ,co.con_legacy_id
                     ,co.con_create_td
                     ,co.con_pickup_lt
                     ,co.con_deliv_due_lt
                     ,co.CON_DELIV_END_LT
                     ,co.SRP_ID_AREA_DEST
                     ,co.CON_CLNT_REF_TX
                     ,co.BUL_CSYS_ID_ORIG
                     ,blOrig.BUL_ID as BUL_ID_ORIG
                     ,blOrig.BUL_NM as BUL_NM_ORIG
                     ,blDest.BUL_NM as BUL_NM_DEST
                     ,cnr.cpn_oa_town_nm as cpn_oa_town_nm_r
                     ,cnd.cpn_oa_town_nm as cpn_oa_town_nm_d
                     ,co.cou_iso_id_orig
                     ,cuOrig.Cou_nm as Cou_Nm_Orig
                     ,co.cou_iso_id_dest
                     ,cuDest.Cou_nm as Cou_Nm_Dest
                     ,(select count(*) from CORPCV01 PC WHERE PC.CON_ID = CO.CON_ID) PCE_QT
                     ,CASE WHEN COUNT(*) OVER(PARTITION BY CON_LEGACY_ID) &gt; 1 THEN 'TRUE' ELSE 'FALSE' END AS DUPLICATES
                     ,cs.COS_SIGN_NM, ROW_NUMBER() OVER(PARTITION BY CS.CON_ID ORDER BY COS_EVENT_TD) SEQ_NR
                 FROM corcov01 co
                INNER JOIN corcnv01 cnr
                   ON cnr.con_id = co.con_id
                  AND cnr.cpn_type_cd = 'R'
                 LEFT OUTER JOIN corcnv01 cnd
                   ON cnd.con_id = co.con_id
                  AND cnd.cpn_type_cd = 'D'
                INNER JOIN ncrcuv01 cuOrig
                   ON cuOrig.COU_ISO_ID = co.COU_ISO_ID_ORIG
                  AND TRUNC(CURRENT_DATE) BETWEEN cuOrig.COU_EFFECT_DT AND cuOrig.COU_EFFECT_TO_DT
                INNER JOIN ncrcuv01 cuDest
                   ON cuDest.COU_ISO_ID = co.COU_ISO_ID_DEST
                  AND TRUNC(CURRENT_DATE) BETWEEN cuDest.COU_EFFECT_DT AND cuDest.COU_EFFECT_TO_DT
                LEFT OUTER JOIN ncrblv01 blOrig
                   ON blOrig.BUL_CSYS_ID = co.BUL_CSYS_ID_ORIG
                  AND TRUNC(CURRENT_DATE) BETWEEN blOrig.BUL_EFFECT_DT AND blOrig.BUL_EFFECT_TO_DT
                LEFT OUTER JOIN ncrblv01 blDest
                   ON blDest.BUL_CSYS_ID = co.BUL_CSYS_ID_DEST
                  AND TRUNC(CURRENT_DATE) BETWEEN blDest.BUL_EFFECT_DT AND blDest.BUL_EFFECT_TO_DT
                LEFT OUTER JOIN corcsv01 cs
                   ON co.con_id = cs.con_id 
                  AND cs.cos_sign_nm is not null
                  and cs.cos_delete_in = 'N'
                 WHERE co.con_Legacy_Id IN ( select dataItem from TABLE(ZYADMIN.ZYP90008.GetListFromCSV(sPInputList)) b )
                 )WHERE SEQ_NR = 1;
            OPEN cPStatus FOR
                SELECT csv.CON_ID AS CON_ID_COS
                      ,csv.XSF_ID
                      ,csv.XSS_ID
                      ,csv.XSG_ID
                      ,csv.XSD_ID
                      ,ndv.XSX_ID
                      ,ndv.xsd_customer_ds
                      ,BUL_CSYS_ID_OCC
                      ,nlv.BUL_NM as BUL_NM_OCC
                      ,csv.COS_EVENT_LT
                      ,ndv.XSD_SEVERITY_CD
                      ,qb.QLA_DS
                  FROM corcsv01 csv 
                 INNER JOIN corcov01 co
                    ON csv.con_id = co.con_id
                 INNER JOIN ncrsdv01 ndv 
                    ON ndv.XSF_ID=csv.XSF_ID 
                   AND ndv.XSS_ID=csv.XSS_ID 
                   AND ndv.XSG_ID=csv.XSG_ID 
                   AND ndv.XSD_ID=csv.XSD_ID 
                   AND TRUNC(CURRENT_DATE) BETWEEN ndv.XSD_EFFECT_DT AND ndv.XSD_EFFECT_TO_DT 
                 INNER JOIN ncrksv01 ks 
                    ON ndv.XSX_ID= ks.XSX_ID 
                   AND TRUNC(CURRENT_DATE) BETWEEN ks.SKT_EFFECT_DT AND ks.SKT_EFFECT_TO_DT 
                   AND ks.SCA_ID = 'WEB'
                 LEFT OUTER JOIN ncrqav01 qa
                   ON qa.XSX_ID = ndv.XSX_ID
                  AND qa.APP_ID = 'EXCO'
                  AND TRUNC(CURRENT_DATE) BETWEEN QAS_EFFECT_DT AND QAS_EFFECT_TO_DT
                 LEFT OUTER JOIN ncrqbv01 qb
                   ON qa.QLA_ID = qb.QLA_ID
                  AND qa.APP_ID = qb.APP_ID
                  AND TRUNC(CURRENT_DATE) BETWEEN QLA_EFFECT_DT AND QLA_EFFECT_TO_DT
                 INNER JOIN ncrblv01 nlv 
                    ON nlv.BUL_CSYS_ID=csv.BUL_CSYS_ID_OCC 
                   AND TRUNC(CURRENT_DATE)BETWEEN nlv.BUL_EFFECT_DT AND nlv.BUL_EFFECT_TO_DT 
                 WHERE co.con_Legacy_Id IN ( select dataItem from TABLE(ZYADMIN.ZYP90008.GetListFromCSV(sPInputList)) b );
          ELSE
           OPEN cPCon FOR
                      SELECT 
                      con_id
                     ,con_legacy_id
                     ,con_create_td
                     ,con_pickup_lt
                     ,con_deliv_due_lt
                     ,CON_DELIV_END_LT
                     ,SRP_ID_AREA_DEST
                     ,CON_CLNT_REF_TX
                     ,BUL_CSYS_ID_ORIG
                     ,BUL_ID_ORIG
                     ,BUL_NM_ORIG
                     ,BUL_NM_DEST
                     ,cpn_oa_town_nm_r
                     ,cpn_oa_town_nm_d
                     ,cou_iso_id_orig
                     ,Cou_Nm_Orig
                     ,cou_iso_id_dest
                     ,Cou_Nm_Dest
                     ,PCE_QT
                     ,DUPLICATES
                     ,COS_SIGN_NM FROM (
               SELECT  /*+ cardinality (b 3) */ 
                      co.con_id
                     ,co.con_legacy_id
                     ,co.con_create_td
                     ,co.con_pickup_lt
                     ,co.con_deliv_due_lt
                     ,co.CON_DELIV_END_LT
                     ,co.SRP_ID_AREA_DEST
                     ,co.CON_CLNT_REF_TX
                     ,co.BUL_CSYS_ID_ORIG
                     ,blOrig.BUL_ID as BUL_ID_ORIG
                     ,blOrig.BUL_NM as BUL_NM_ORIG
                     ,blDest.BUL_NM as BUL_NM_DEST
                     ,cnr.cpn_oa_town_nm as cpn_oa_town_nm_r
                     ,cnd.cpn_oa_town_nm as cpn_oa_town_nm_d
                     ,co.cou_iso_id_orig
                     ,cuOrig.Cou_nm as Cou_Nm_Orig
                     ,co.cou_iso_id_dest
                     ,cuDest.Cou_nm as Cou_Nm_Dest
                     ,(select count(*) from CORPCV01 PC WHERE PC.CON_ID = CO.CON_ID) PCE_QT
                     ,'FALSE' AS DUPLICATES
                     ,cs.COS_SIGN_NM, ROW_NUMBER() OVER(PARTITION BY CS.CON_ID ORDER BY COS_EVENT_TD) SEQ_NR
                 FROM corcov01 co
                INNER JOIN corcnv01 cnr
                   ON cnr.con_id = co.con_id
                  AND cnr.cpn_type_cd = 'R'
                 LEFT OUTER JOIN corcnv01 cnd
                   ON cnd.con_id = co.con_id
                  AND cnd.cpn_type_cd = 'D'
                INNER JOIN ncrcuv01 cuOrig
                   ON cuOrig.COU_ISO_ID = co.COU_ISO_ID_ORIG
                  AND TRUNC(CURRENT_DATE) BETWEEN cuOrig.COU_EFFECT_DT AND cuOrig.COU_EFFECT_TO_DT
                INNER JOIN ncrcuv01 cuDest
                   ON cuDest.COU_ISO_ID = co.COU_ISO_ID_DEST
                  AND TRUNC(CURRENT_DATE) BETWEEN cuDest.COU_EFFECT_DT AND cuDest.COU_EFFECT_TO_DT
                LEFT OUTER JOIN ncrblv01 blOrig
                   ON blOrig.BUL_CSYS_ID = co.BUL_CSYS_ID_ORIG
                  AND TRUNC(CURRENT_DATE) BETWEEN blOrig.BUL_EFFECT_DT AND blOrig.BUL_EFFECT_TO_DT
                LEFT OUTER JOIN ncrblv01 blDest
                   ON blDest.BUL_CSYS_ID = co.BUL_CSYS_ID_DEST
                  AND TRUNC(CURRENT_DATE) BETWEEN blDest.BUL_EFFECT_DT AND blDest.BUL_EFFECT_TO_DT
                LEFT OUTER JOIN corcsv01 cs
                   ON co.con_id = cs.con_id 
                  AND cs.cos_sign_nm is not null
                  and cs.cos_delete_in = 'N'
                 WHERE co.CON_CLNT_REF_CR IN ( select dataItem from TABLE(ZYADMIN.ZYP90008.GetListFromCSV(sPInputList)) b )
                 )WHERE SEQ_NR = 1;
            OPEN cPStatus FOR
                SELECT csv.CON_ID AS CON_ID_COS
                      ,csv.XSF_ID
                      ,csv.XSS_ID
                      ,csv.XSG_ID
                      ,csv.XSD_ID
                      ,ndv.XSX_ID
                      ,ndv.xsd_customer_ds
                      ,BUL_CSYS_ID_OCC
                      ,nlv.BUL_NM as BUL_NM_OCC
                      ,csv.COS_EVENT_LT
                      ,ndv.XSD_SEVERITY_CD
                      ,qb.QLA_DS
                  FROM corcsv01 csv 
                 INNER JOIN corcov01 co
                    ON csv.con_id = co.con_id
                 INNER JOIN ncrsdv01 ndv 
                    ON ndv.XSF_ID=csv.XSF_ID 
                   AND ndv.XSS_ID=csv.XSS_ID 
                   AND ndv.XSG_ID=csv.XSG_ID 
                   AND ndv.XSD_ID=csv.XSD_ID 
                   AND TRUNC(CURRENT_DATE) BETWEEN ndv.XSD_EFFECT_DT AND ndv.XSD_EFFECT_TO_DT 
                 INNER JOIN ncrksv01 ks 
                    ON ndv.XSX_ID= ks.XSX_ID 
                   AND TRUNC(CURRENT_DATE) BETWEEN ks.SKT_EFFECT_DT AND ks.SKT_EFFECT_TO_DT 
                   AND ks.SCA_ID = 'WEB'
                 LEFT OUTER JOIN ncrqav01 qa
                   ON qa.XSX_ID = ndv.XSX_ID
                  AND qa.APP_ID = 'EXCO'
                  AND TRUNC(CURRENT_DATE) BETWEEN QAS_EFFECT_DT AND QAS_EFFECT_TO_DT
                 LEFT OUTER JOIN ncrqbv01 qb
                   ON qa.QLA_ID = qb.QLA_ID
                  AND qa.APP_ID = qb.APP_ID
                  AND TRUNC(CURRENT_DATE) BETWEEN QLA_EFFECT_DT AND QLA_EFFECT_TO_DT
                 INNER JOIN ncrblv01 nlv 
                    ON nlv.BUL_CSYS_ID=csv.BUL_CSYS_ID_OCC 
                   AND TRUNC(CURRENT_DATE)BETWEEN nlv.BUL_EFFECT_DT AND nlv.BUL_EFFECT_TO_DT 
                 WHERE co.CON_CLNT_REF_CR IN ( select dataItem from TABLE(ZYADMIN.ZYP90008.GetListFromCSV(sPInputList)) b );
          END IF;
      EXCEPTION
          WHEN OTHERS THEN
            sPError := '1' ||' '||SQLCODE||' '||SUBSTR(SQLERRM, 1, 200);
      END;
  END COP00134_006_1;
  </code></pre>
  
  <p>Issue is with the queries executed in ELSE part.
  As mentioned in comments, I have run the same queries seperately and they are executed in less than a second while when same data is passed to procedure it takes more than a minute. I cannot run profiler as I don't have database access and the process of asking DBA for the same will take more than a week and I need to fix this as soon as possible</p>