title: performance tuning sql query
body: <p>I have this query which is candidate for a performance tuning as it is taking for than 4-5 hours to run.</p>
  
  <pre><code>explain plan for SELECT /*+ PARALLEL_INDEX(ssp, sub_svc_parm_ix2, 4) 
                            INDEX(ssp sub_svc_parm_ix2) */ 
               SUB_SVC_ID
          FROM SUB_SVC_PARM ssp
         WHERE PARM_ID = GET_PARM_ID('net_ppv_credit_limit', GET_CLASS_ID('SubSvcSpec'), GET_SVCID('smp_cpe_cas'))
           AND VAL &lt;&gt; '140.00'
           AND EXISTS (SELECT /*+ PARALLEL_INDEX(ss, sub_svc_pk, 4) */ 
                              1
                         FROM SUB_SVC ss
                        WHERE ss.SUB_SVC_ID = ssp.SUB_SVC_ID
                          AND ss.SUB_SVC_STATUS_ID NOT IN (FN_GET_STATUS_ID('SubSvcSpec', 'deleted'),
                                                           FN_GET_STATUS_ID('SubSvcSpec', 'inactive'),
                                                           FN_GET_STATUS_ID('SubSvcSpec', 'add_in_progress'),
                                                           FN_GET_STATUS_ID('SubSvcSpec', 'activation_in_progress'),
                                                           FN_GET_STATUS_ID('SubSvcSpec', 'courtesy_block_in_progress'),
                                                           FN_GET_STATUS_ID('SubSvcSpec', 'mso_block_in_progress'),
                                                           FN_GET_STATUS_ID('SubSvcSpec', 'delete_in_progress'),
                                                           FN_GET_STATUS_ID('SubSvcSpec', 'deactivation_in_progress'),
                                                           FN_GET_STATUS_ID('SubSvcSpec', 'change_in_progress')));
  </code></pre>
  
  <p>I am posting here the original query which is called as a cursor in a procedure.</p>
  
  <p>Explain plan for the query on the domain where it is taking time is --</p>
  
  <pre><code>Plan hash value: 4290343623
  
  ----------------------------------------------------------------------------           -----------------------------           
    | Id  | Operation                    | Name             | Rows  | Bytes      |TempSpc| Cost (%CPU)| Time     |
   ---------------------------------------------------------------------------   ------------------------------
   |   0 | SELECT STATEMENT             |                  |  1802K|    56M|       |   528K  (1)|730:11:02 |
   |*  1 |  HASH JOIN RIGHT SEMI        |                  |  1802K|    56M|    37M|   528K  (1)|730:11:02 |
   |*  2 |   TABLE ACCESS FULL          | SUB_SVC          |  1763K|    16M|       |   311K  (1)|430:15:33 |
   |   3 |   TABLE ACCESS BY INDEX ROWID| SUB_SVC_PARM     |  2394K|    52M|       |   209K  (0)|288:56:00 |
   |*  4 |    INDEX RANGE SCAN          | SUB_SVC_PARM_IX2 |  2394K|       |       |  1519   (0)| 02:05:59 |
  ---------------------------------------------------------------------------------------------------------
  
   Predicate Information (identified by operation id):
  ---------------------------------------------------
  
   1 - access("SS"."SUB_SVC_ID"="SSP"."SUB_SVC_ID")
   2 - filter("SS"."SUB_SVC_STATUS_ID"&lt;&gt;"FN_GET_STATUS_ID"('SubSvcSpec','deleted') AND 
            "SS"."SUB_SVC_STATUS_ID"&lt;&gt;"FN_GET_STATUS_ID"('SubSvcSpec','inactive') AND 
            "SS"."SUB_SVC_STATUS_ID"&lt;&gt;"FN_GET_STATUS_ID"('SubSvcSpec','add_in_progress') AND 
            "SS"."SUB_SVC_STATUS_ID"&lt;&gt;"FN_GET_STATUS_ID"('SubSvcSpec','activation_in_progress') AND 
            "SS"."SUB_SVC_STATUS_ID"&lt;&gt;"FN_GET_STATUS_ID"('SubSvcSpec','courtesy_block_in_progress') AND 
            "SS"."SUB_SVC_STATUS_ID"&lt;&gt;"FN_GET_STATUS_ID"('SubSvcSpec','mso_block_in_progress') AND 
            "SS"."SUB_SVC_STATUS_ID"&lt;&gt;"FN_GET_STATUS_ID"('SubSvcSpec','delete_in_progress') AND 
            "SS"."SUB_SVC_STATUS_ID"&lt;&gt;"FN_GET_STATUS_ID"('SubSvcSpec','deactivation_in_progress') AND 
            "SS"."SUB_SVC_STATUS_ID"&lt;&gt;"FN_GET_STATUS_ID"('SubSvcSpec','change_in_progress'))
  4 - access("PARM_ID"="GET_PARM_ID"('net_ppv_credit_limit',"GET_CLASS_ID"('SubSvcSpec'),"GET_SV
            CID"('smp_cpe_cas')))
     filter("VAL"&lt;&gt;'140.00')
  </code></pre>
  
  <p>Indexes build on both table are ---</p>
  
  <p>SUB_SVC TABLE</p>
  
  <pre><code>index_name      coulmn_name
  -----------     ------------
  SUB_SVC_PK      sub_svc_id
  SUB_SVC_IX4     PARENT_SUB_SVC_ID
  SUB_SVC_IX5     EXTERNAL_KEY
  SUB_SVC_IX6     SUB_SVC_IX6
  </code></pre>
  
  <p>SUB_SVC_PARM TABLE</p>
  
  <pre><code>index_name        coulmn_name
  -----------       ------------
  SUB_SVC_PARM_PK   SUB_SVC_ID, PARM_ID
  SUB_SVC_PARM_IX2  PARM_ID, VAL
  </code></pre>
  
  <p>CREATE TABLE SYNTAX FOR SUB_SVC_PARM TABLE </p>
  
  <pre><code> CREATE TABLE "SMPHOMCM"."SUB_SVC_PARM" 
     (    "SUB_SVC_ID" NUMBER(12,0) NOT NULL ENABLE, 
  "PARM_ID" NUMBER(12,0) NOT NULL ENABLE, 
  "VAL" VARCHAR2(2000 BYTE) NOT NULL ENABLE, 
   CONSTRAINT "SUB_SVC_PARM_PK" PRIMARY KEY ("SUB_SVC_ID", "PARM_ID")
    USING INDEX)
  
  CREATE TABLE SYNTAX FOR SUB_SVC TABLE
  
  CREATE TABLE "SMPHOMCM"."SUB_SVC" 
     (    "SUB_SVC_ID" NUMBER(12,0) NOT NULL ENABLE, 
  "SUB_ID" NUMBER(12,0) NOT NULL ENABLE, 
  "START_DT" DATE NOT NULL ENABLE, 
  "EXTERNAL_KEY" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
  "SAMP_VER" NUMBER(9,0) NOT NULL ENABLE, 
  "SUB_SVC_STATUS_ID" NUMBER(12,0) NOT NULL ENABLE, 
  "CREATED_DTM" DATE NOT NULL ENABLE, 
  "CREATED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
  "END_DT" DATE, 
  "PURCHASE_DT" DATE, 
  "PARENT_SUB_SVC_ID" NUMBER(12,0), 
  "PRE_STATUS_ID" NUMBER(12,0), 
  "MODIFIED_DTM" DATE, 
  "MODIFIED_BY" VARCHAR2(30 BYTE), 
  "SVC_ID" NUMBER(12,0), 
   CONSTRAINT "SUB_SVC_PK" PRIMARY KEY ("SUB_SVC_ID")
   USING INDEX)
  </code></pre>
  
  <p>Meanwhile i am trying to use WITH clause for the functions calling the where clause however in the procedure there functions are caught in the constant variables which are then used in the query !</p>
  
  <p>My DB is : Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production</p>
  
  <p>Please ask for more information if required. 
  Thanks  </p>
  
  <p>EDIT: This Procedure is using the query and is not written by me !
        I think this procedure itself needs to be tune other than the query </p>
  
  <p>please suggest </p>
  
  <pre><code> CREATE OR REPLACE PROCEDURE PPV_CREDIT_LIMIT(p_exid NUMBER)
   IS
   -- Flag 'N' is null
  
     TYPE tab_sub_svc_id               IS TABLE OF    SUB_SVC_PARM.SUB_SVC_ID%TYPE INDEX BY PLS_INTEGER;
     rs_sub_svc_id                     tab_sub_svc_id;
  
     c_class_SubSvcSpec                constant pls_integer := GET_CLASS_ID('SubSvcSpec');
     c_svc_smp_cpe_cas                 constant pls_integer := GET_SVCID('smp_cpe_cas');
     c_parm_net_ppv_credit_limit       constant pls_integer := GET_PARM_ID('net_ppv_credit_limit', c_class_SubSvcSpec, c_svc_smp_cpe_cas);
     c_deleted                         constant pls_integer := FN_GET_STATUS_ID('SubSvcSpec', 'deleted');
     c_inactive                        constant pls_integer := FN_GET_STATUS_ID('SubSvcSpec', 'inactive');
     c_add_in_progress                 constant pls_integer := FN_GET_STATUS_ID('SubSvcSpec', 'add_in_progress');
     c_activation_in_progress          constant pls_integer := FN_GET_STATUS_ID('SubSvcSpec', 'activation_in_progress');
     c_courtesy_block_in_progress      constant pls_integer := FN_GET_STATUS_ID('SubSvcSpec', 'courtesy_block_in_progress');
     c_mso_block_in_progress           constant pls_integer := FN_GET_STATUS_ID('SubSvcSpec', 'mso_block_in_progress');
     c_delete_in_progress              constant pls_integer := FN_GET_STATUS_ID('SubSvcSpec', 'delete_in_progress');
     c_deactivation_in_progress        constant pls_integer := FN_GET_STATUS_ID('SubSvcSpec', 'deactivation_in_progress');
     c_change_in_progress              constant pls_integer := FN_GET_STATUS_ID('SubSvcSpec', 'change_in_progress');
     c_ppv_credit_limit                constant varchar2(6) := '140.00';
     -- Added for net_creditthreshold parm
     c_parm_ppv_credit_threshold       constant pls_integer :=   GET_PARM_ID('net_ppv_creditthreshold', c_class_SubSvcSpec, c_svc_smp_cpe_cas);
     c_ppv_credit_threshold            constant varchar2(6) := '80.00';
     ilimit                            CONSTANT PLS_INTEGER := 1000;
     iCheck                            CONSTANT PLS_INTEGER := 10;
     l_total_recs                               PLS_INTEGER;
     l_rec_cnt                                  PLS_INTEGER;
     l_curr_cnt                                 PLS_INTEGER := 0;
     l_batch                                    PLS_INTEGER := 0;
     v_stop_flag                                CHAR(1) := 'N';
  
     cursor curPPV_CL IS
        SELECT /*+ PARALLEL_INDEX(ssp, sub_svc_parm_ix2, 4) 
                            INDEX(ssp sub_svc_parm_ix2) */ 
               SUB_SVC_ID
          FROM SUB_SVC_PARM ssp
         WHERE PARM_ID = c_parm_net_ppv_credit_limit
           AND VAL &lt;&gt; '140.00'
           AND EXISTS (SELECT /*+ PARALLEL_INDEX(ss, sub_svc_pk, 4) */ 
                              1
                         FROM SUB_SVC ss
                        WHERE ss.SUB_SVC_ID = ssp.SUB_SVC_ID
                          AND ss.SUB_SVC_STATUS_ID NOT IN (c_deleted,
                                                           c_inactive,
                                                           c_add_in_progress,
                                                           c_activation_in_progress,
                                                           c_courtesy_block_in_progress,
                                                           c_mso_block_in_progress,
                                                           c_delete_in_progress,
                                                           c_deactivation_in_progress,
                                                           c_change_in_progress));
  
  BEGIN
  
     DBMS_APPLICATION_INFO.set_action (NULL);
     DBMS_APPLICATION_INFO.set_module (NULL, NULL);
     DBMS_APPLICATION_INFO.set_client_info (NULL);
  
     DBMS_APPLICATION_INFO.set_module (module_name =&gt; 'Procedure: PPV_CREDIT_LIMIT',
                                   action_name =&gt; 'Counting total updatable records');
  
     SELECT /*+ PARALLEL_INDEX(ssp, sub_svc_parm_ix2, 4) 
                     INDEX(ssp sub_svc_parm_ix2) */ 
        COUNT(SUB_SVC_ID)
   INTO l_total_recs
   FROM SUB_SVC_PARM ssp
  WHERE PARM_ID = c_parm_net_ppv_credit_limit
    AND VAL &lt;&gt; '140.00'
    AND EXISTS (SELECT /*+ PARALLEL_INDEX(ss, sub_svc_pk, 4) */ 
                       1
                  FROM SUB_SVC ss
                 WHERE ss.SUB_SVC_ID = ssp.SUB_SVC_ID
                   AND ss.SUB_SVC_STATUS_ID NOT IN (c_deleted,
                                                    c_inactive,
                                                    c_add_in_progress,
                                                    c_activation_in_progress,
                                                    c_courtesy_block_in_progress,
                                                    c_mso_block_in_progress,
                                                    c_delete_in_progress,
                                                    c_deactivation_in_progress,
                                                    c_change_in_progress));
  
     DBMS_OUTPUT.PUT_LINE('Total records for updating are : ' || l_total_recs);
  
     OPEN curPPV_CL;
  
     LOOP
         FETCH curPPV_CL
          BULK COLLECT INTO rs_sub_svc_id limit ilimit;
  
         l_rec_cnt := rs_sub_svc_id.COUNT;
         l_curr_cnt := l_curr_cnt + l_rec_cnt;
  
         DBMS_APPLICATION_INFO.set_module (module_name =&gt; 'Procedure: PPV_CREDIT_LIMIT',
                                       action_name =&gt; 'Updating ' ||    l_curr_cnt || ' of ' || l_total_recs);
  
         for idx in 1 .. l_rec_cnt
          LOOP 
            UPDATE SUB_SVC_PARM
           SET VAL = c_ppv_credit_limit
         WHERE SUB_SVC_ID = rs_sub_svc_id(idx)
           AND PARM_ID = c_parm_net_ppv_credit_limit;
  
        UPDATE SUB_SVC_PARM
           SET VAL = c_ppv_credit_threshold
         WHERE SUB_SVC_ID = rs_sub_svc_id(idx)
           AND PARM_ID = c_parm_ppv_credit_threshold;
  
      END LOOP;
      COMMIT;
  
  
     l_batch := l_batch + 1;
     DBMS_APPLICATION_INFO.set_client_info ('BATCH:' || l_batch * ilimit);
     EXIT WHEN l_rec_cnt &lt; ilimit;
  
     IF   MOD(l_batch, iCheck) = 0
     THEN
          SELECT STOP_FLAG
            INTO v_stop_flag
            FROM DM_PPV_CREDIT_LIMIT
           WHERE EXECUTION_ID = p_exid;
     END IF;
  
     EXIT WHEN v_stop_flag = 'Y';
  
     END LOOP;
  
     CLOSE curPPV_CL;
  
  DBMS_OUTPUT.PUT_LINE('Have updated records : ' || l_curr_cnt || ' out of total records : ' || l_total_recs  );
  
  EXCEPTION
      WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
  END PPV_CREDIT_LIMIT;
  </code></pre>
  
  <p>EXPLAIN PLAN AFTER CREATING TWO INDEXES -</p>
  
  <pre><code>create index sub_svc_parm_ixpvs on sub_svc_parm (parm_id, val, sub_svc_id) ;
  
  create index sub_svc_ixss on sub_svc (sub_svc_id, sub_svc_status_id) TABLESPACE "SMP_IDX_SUB_SVC" ;
  
  
  Plan hash value: 176576580
  
  ----------------------------------------------------------------------------       --------------------
  | Id  | Operation         | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  ----------------------------------------------------------------------------    --------------------
  |   0 | SELECT STATEMENT  |                    |  1802K|    56M|       | 43296   (1)| 59:49:34 |
  |*  1 |  HASH JOIN        |                    |  1802K|    56M|    37M| 43296   (1)| 59:49:34 |
  |*  2 |   INDEX FULL SCAN | SUB_SVC_IXSS       |  1763K|    16M|       | 33679   (1)| 46:32:15 |
  |*  3 |   INDEX RANGE SCAN| SUB_SVC_PARM_IXPVS |  2308K|    50M|       |  1857   (0)| 02:34:00 |
  ------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     1 - access("SS"."SUB_SVC_ID"="SSP"."SUB_SVC_ID")
     2 - filter("SS"."SUB_SVC_STATUS_ID"&lt;&gt;"FN_GET_STATUS_ID"('SubSvcSpec','deleted') AND 
            "SS"."SUB_SVC_STATUS_ID"&lt;&gt;"FN_GET_STATUS_ID"('SubSvcSpec','inactive') AND 
            "SS"."SUB_SVC_STATUS_ID"&lt;&gt;"FN_GET_STATUS_ID"('SubSvcSpec','add_in_progress') AND 
            "SS"."SUB_SVC_STATUS_ID"&lt;&gt;"FN_GET_STATUS_ID"('SubSvcSpec','activation_in_progress') AND 
            "SS"."SUB_SVC_STATUS_ID"&lt;&gt;"FN_GET_STATUS_ID"('SubSvcSpec','courtesy_block_in_progress') 
            AND "SS"."SUB_SVC_STATUS_ID"&lt;&gt;"FN_GET_STATUS_ID"('SubSvcSpec','mso_block_in_progress') 
            AND "SS"."SUB_SVC_STATUS_ID"&lt;&gt;"FN_GET_STATUS_ID"('SubSvcSpec','delete_in_progress') AND 
            "SS"."SUB_SVC_STATUS_ID"&lt;&gt;"FN_GET_STATUS_ID"('SubSvcSpec','deactivation_in_progress') 
            AND "SS"."SUB_SVC_STATUS_ID"&lt;&gt;"FN_GET_STATUS_ID"('SubSvcSpec','change_in_progress'))
     3 - access("SSP"."PARM_ID"="GET_PARM_ID"('net_ppv_credit_limit',"GET_CLASS_ID"('SubSv
            cSpec'),"GET_SVCID"('smp_cpe_cas')))
     filter("SSP"."VAL"&lt;&gt;'140.00')
  </code></pre>