This discussion is archived
1 Reply Latest reply: Feb 17, 2013 3:39 AM by Srini Chavali-Oracle RSS

Performance problem with String to Table

225452 Newbie
Currently Being Moderated
We are using following function for String to table
CREATE OR REPLACE PACKAGE BODY STAT_RPT.PKG_UTILITY
AS
  FUNCTION FN_string_to_tab (piv_string IN VARCHAR2)
      RETURN tabular_list
      PIPELINED
 DETERMINISTIC
   IS
      v_string   VARCHAR2 (4000) DEFAULT piv_string ;
      v_offset   PLS_INTEGER;
   BEGIN
      LOOP
         v_offset := INSTR (v_string, ',');
         IF v_offset > 0
         THEN
            PIPE ROW (NEW typ_list (SUBSTR (v_string, 1, v_offset - 1)));
            v_string := SUBSTR (v_string, v_offset + 1, LENGTH (v_string));
         END IF;
    EXIT WHEN v_string IS NULL OR v_offset = 0;
      END LOOP;
      IF v_string IS NOT NULL
      THEN
         PIPE ROW (NEW typ_list (v_string));
      END IF;

      RETURN;
   END;
END PKG_UTILITY;
/
When we are using this function in the query where this function is used many times, the performance becomes very slow. Taking 87 secs
 
SELECT V.*, ROW_NUMBER () OVER (ORDER BY 1) rn
FROM   v_rpt_filing_summary v
WHERE  SERIES_ID IN 
      (SELECT tabular_list
     FROM   TABLE (pkg_utility.fn_string_to_tab ('CC,ABC')))
       AND as_of_dt =
              (SELECT MAX (as_of_dt)
               FROM   rpt_series_as_of_dt
               WHERE  as_of_dt <= TRUNC (SYSDATE)
                      AND rpt_series_sk = (SELECT rpt_series_sk
                                           FROM   rpt_series
                                           WHERE  series_id = v.series_id))
       AND DISTRICT_NR IN 
              (SELECT tabular_list
              FROM   TABLE (pkg_utility.fn_string_to_tab ('12')))
       AND NSAM_USR.PKG_SECURITY.
           FN_VALIDATE_SERIES_DIST_ACCESS (UPPER ('l1cmj0b'),
                                           SERIES_ID,
                                           DISTRICT_NR) > 0
       AND district_nr IS NOT NULL
       AND v.RPT_SERIES_AS_OF_DT_SK NOT IN
              (SELECT DISTINCT x.RPT_SERIES_AS_OF_DT_SK
               FROM   rpt_series_filing x
               WHERE  X.FILING_STATUS_CD IN ('ACTIVE', 'HELD')
                      AND X.RSSD_ID = v.rssd_id)
UNION
SELECT *
FROM   (SELECT rs.SERIES_ID,
               FORM_TITLE_TXT,
               NULL CREATE_USER_ID,
               mrm.RSSD_ID,
               rsaodt.AS_OF_DT,
               NULL SUBMISSION_DT,
               NULL FILING_STATUS_CD,
               NULL ESTIMATED_FLG,
               NULL ESTIMATED_ALLOW_FLG,
               NULL MASTER_RESP_LIST_SK,
               NULL RPT_SERIES_GET_REVISED_FLG,
               NULL DUE_DAY,
               NULL DUE_DT,
               mrm.DISTRICT_NR ML_DISTRICT_NR,
               NULL SUBMITTER_ID,
               NULL SUBMITTER_NAME,
               NULL RPT_SERIES_SK,
               (SELECT ABBRV_CUST_NM
                FROM   STRUCTR.fi
                WHERE  rssd_id = mrm.RSSD_ID)
                  REPORTER_NAME,
               NULL RPT_SERIES_FILING_SK,
               NULL VERSION_NUMBER,
               NULL RPT_SERIES_AS_OF_DT_SK,
               NULL USER_TYPE,
               NULL EDIT_FLG,
               NULL DERIVED_STATUS,
               NULL ESTIMATED_BY,
               NULL FILING_TIMESTAMP,
               NULL DISTRICT_NAME,
               NULL EXT_DISPLAY_STATUS,
               NULL INT_DISPLAY_STATUS,
               NULL REPORT_VERSIONS,
               NULL create_dt,
               NULL LAST_UPDT_DT,
               NULL LAST_UPDT_USER_ID,
               PRIOR_OFFSET_DAYS_NR,
               PRIOR_OFFSET_DAYS_TS,
               ROW_NUMBER () OVER (ORDER BY 1) rn
        FROM   rpt_series rs,
               rpt_series_as_of_dt RSAODT,
               master_resp_member mrm,
               rpt_series_parm rsp
        WHERE  SERIES_ID IN 
                  (SELECT tabular_list
                 FROM   TABLE (pkg_utility.fn_string_to_tab ('FFIEC009')))
               AND as_of_dt =
                      (SELECT MAX (as_of_dt)
                       FROM   rpt_series_as_of_dt
                       WHERE  as_of_dt <= TRUNC (SYSDATE)
                              AND rpt_series_sk =
                                     (SELECT rpt_series_sk
                                      FROM   rpt_series
                                      WHERE  series_id = rs.series_id))
               AND DISTRICT_NR IN 
                      (SELECT tabular_list
                    FROM   TABLE (
                               pkg_utility.fn_string_to_tab ('12')))
               AND RS.RPT_SERIES_SK = RSAODT.RPT_SERIES_SK
               AND RS.RPT_SERIES_SK = RSP.RPT_SERIES_SK
               AND RSP.MASTER_RESP_LIST_SK = MRM.MASTER_RESP_LIST_SK
               AND as_of_dt BETWEEN MRM.MEMBER_FIRST_AS_OF_DT
                                AND MRM.MEMBER_LAST_AS_OF_DT
               AND AS_OF_DT BETWEEN RSP.RPT_SERIES_START_DT
                                AND RSP.RPT_SERIES_END_DT
               AND rsaodt.RPT_SERIES_AS_OF_DT_SK NOT IN
                      (SELECT x.RPT_SERIES_AS_OF_DT_SK
                       FROM   rpt_series_filing x
                       WHERE  rssd_id = mrm.rssd_id)
               AND NSAM_USR.PKG_SECURITY.
                   FN_VALIDATE_SERIES_DIST_ACCESS (
                      UPPER ('l1cmj0b'),
                      SERIES_ID,
                      DISTRICT_NR) > 0
               AND district_nr IS NOT NULL);
Plan
Plan
SELECT STATEMENT  ALL_ROWSCost: 39,655  Bytes: 38,089,924  Cardinality: 60,678                                                              
     69 SORT UNIQUE  Cost: 39,655  Bytes: 38,089,924  Cardinality: 60,678                                                         
          68 UNION-ALL                                                    
               3 COUNT STOPKEY                                               
                    2 TABLE ACCESS BY INDEX ROWID TABLE STAT_RPT.MASTER_RESP_MEMBER Cost: 3  Bytes: 29  Cardinality: 1                                          
                         1 INDEX RANGE SCAN INDEX (UNIQUE) STAT_RPT.UK_MASTER_RESP_MEMBER Cost: 2  Cardinality: 1                                     
               9 VIEW VIEW NSAM_USR.V_USER_ID_NAME Cost: 10  Bytes: 124  Cardinality: 2                                               
                    8 SORT UNIQUE  Cost: 10  Bytes: 41  Cardinality: 2                                          
                         7 UNION-ALL                                     
                              4 TABLE ACCESS FULL TABLE NSAM_USR.EXT_USER_PROFILE Cost: 7  Bytes: 18  Cardinality: 1                                
                              6 TABLE ACCESS BY INDEX ROWID TABLE NSAM_USR.INT_USER_PROFILE Cost: 1  Bytes: 23  Cardinality: 1                                
                                   5 INDEX UNIQUE SCAN INDEX (UNIQUE) NSAM_USR.UK_INT_USER_PROFILE Cost: 0  Cardinality: 1                           
               10 TABLE ACCESS FULL TABLE STAT_RPT.RPT_SERIES_VERSION Cost: 4  Bytes: 22  Cardinality: 1                                               
               15 TABLE ACCESS BY INDEX ROWID TABLE STRUCTR.FRB_DISTRICT Cost: 1  Bytes: 13  Cardinality: 1                                               
                    14 INDEX UNIQUE SCAN INDEX (UNIQUE) STRUCTR.PK_FRB_DISTRICT Cost: 0  Cardinality: 1                                          
                         13 COUNT STOPKEY                                     
                              12 TABLE ACCESS BY INDEX ROWID TABLE STAT_RPT.MASTER_RESP_MEMBER Cost: 3  Bytes: 29  Cardinality: 1                                
                                   11 INDEX RANGE SCAN INDEX (UNIQUE) STAT_RPT.UK_MASTER_RESP_MEMBER Cost: 2  Cardinality: 1                           
               19 SORT AGGREGATE  Bytes: 19  Cardinality: 1                                               
                    18 INDEX RANGE SCAN INDEX STAT_RPT.NX_RPT_SERIES_FILING Cost: 3  Bytes: 57  Cardinality: 3                                          
                         17 TABLE ACCESS BY INDEX ROWID TABLE STAT_RPT.RPT_SERIES_AS_OF_DT Cost: 2  Bytes: 15  Cardinality: 1                                     
                              16 INDEX UNIQUE SCAN INDEX (UNIQUE) STAT_RPT.UK_RPT_SERIES_AS_OF_DT Cost: 1  Cardinality: 1                                
               42 WINDOW NOSORT  Cost: 10,346  Bytes: 28,167,544  Cardinality: 9,794                                               
                    41 FILTER                                          
                         37 HASH JOIN  Cost: 4,460  Bytes: 28,196,157,324  Cardinality: 9,803,949                                     
                              22 VIEW VIEW SYS.VW_NSO_1 Cost: 30  Bytes: 1,053,672  Cardinality: 8,168                                
                                   21 HASH UNIQUE  Cost: 30  Bytes: 16,336  Cardinality: 8,168                           
                                        20 COLLECTION ITERATOR PICKLER FETCH PROCEDURE PKG_UTILITY.FN_STRING_TO_TAB Cost: 29  Bytes: 16,336  Cardinality: 8,168                      
                              36 HASH JOIN  Cost: 570  Bytes: 79,132,829  Cardinality: 28,807                                
                                   32 HASH JOIN RIGHT ANTI  Cost: 539  Bytes: 924,154  Cardinality: 353                           
                                        23 INDEX FAST FULL SCAN INDEX STAT_RPT.NX_RPT_SERIES_FILING Cost: 201  Bytes: 597,759  Cardinality: 31,461                      
                                        31 VIEW VIEW STAT_RPT.V_RPT_FILING_SUMMARY Cost: 337  Bytes: 91,661,532  Cardinality: 35,268                      
                                             30 HASH JOIN  Cost: 337  Bytes: 5,184,396  Cardinality: 35,268                 
                                                  28 HASH JOIN  Cost: 25  Bytes: 94,050  Cardinality: 1,254            
                                                       24 TABLE ACCESS FULL TABLE STAT_RPT.RPT_SERIES Cost: 8  Bytes: 264  Cardinality: 24       
                                                       27 HASH JOIN  Cost: 17  Bytes: 80,256  Cardinality: 1,254       
                                                            25 TABLE ACCESS FULL TABLE STAT_RPT.RPT_SERIES_PARM Cost: 6  Bytes: 1,176  Cardinality: 24  
                                                            26 TABLE ACCESS FULL TABLE STAT_RPT.RPT_SERIES_AS_OF_DT Cost: 10  Bytes: 48,990  Cardinality: 3,266  
                                                  29 TABLE ACCESS FULL TABLE STAT_RPT.RPT_SERIES_FILING Cost: 311  Bytes: 6,613,128  Cardinality: 91,849            
                                   35 VIEW VIEW SYS.VW_NSO_2 Cost: 30  Bytes: 1,053,672  Cardinality: 8,168                           
                                        34 HASH UNIQUE  Cost: 30  Bytes: 16,336  Cardinality: 8,168                      
                                             33 COLLECTION ITERATOR PICKLER FETCH PROCEDURE PKG_UTILITY.FN_STRING_TO_TAB Cost: 29  Bytes: 16,336  Cardinality: 8,168                 
                         40 SORT AGGREGATE  Bytes: 11  Cardinality: 1                                     
                              39 INDEX RANGE SCAN INDEX (UNIQUE) STAT_RPT.UK_RPT_SERIES_AS_OF_DT Cost: 2  Bytes: 792  Cardinality: 72                                
                                   38 TABLE ACCESS FULL TABLE STAT_RPT.RPT_SERIES Cost: 8  Bytes: 11  Cardinality: 1                           
               44 TABLE ACCESS BY INDEX ROWID TABLE STRUCTR.FI_EXT Cost: 3  Bytes: 25  Cardinality: 1                                               
                    43 INDEX UNIQUE SCAN INDEX (UNIQUE) STRUCTR.PK_FI_EXT Cost: 2  Cardinality: 1                                          
               67 VIEW STAT_RPT. Cost: 27,143  Bytes: 9,922,380  Cardinality: 50,884                                               
                    66 WINDOW NOSORT  Cost: 27,143  Bytes: 18,521,776  Cardinality: 50,884                                          
                         65 FILTER                                     
                              61 HASH JOIN  Cost: 27,133  Bytes: 444,968,302,324  Cardinality: 1,222,440,391                                
                                   47 VIEW VIEW SYS.VW_NSO_4 Cost: 30  Bytes: 1,053,672  Cardinality: 8,168                           
                                        46 HASH UNIQUE  Cost: 30  Bytes: 16,336  Cardinality: 8,168                      
                                             45 COLLECTION ITERATOR PICKLER FETCH PROCEDURE PKG_UTILITY.FN_STRING_TO_TAB Cost: 29  Bytes: 16,336  Cardinality: 8,168                 
                                   60 HASH JOIN  Cost: 279  Bytes: 422,047,310  Cardinality: 1,795,946                           
                                        56 HASH JOIN ANTI  Cost: 239  Bytes: 559,362  Cardinality: 5,277                      
                                             54 HASH JOIN  Cost: 38  Bytes: 506,592  Cardinality: 5,277                 
                                                  52 HASH JOIN  Cost: 27  Bytes: 4,374  Cardinality: 54            
                                                       50 HASH JOIN  Cost: 15  Bytes: 1,248  Cardinality: 24       
                                                            48 TABLE ACCESS FULL TABLE STAT_RPT.RPT_SERIES Cost: 8  Bytes: 264  Cardinality: 24  
                                                            49 TABLE ACCESS FULL TABLE STAT_RPT.RPT_SERIES_PARM Cost: 6  Bytes: 984  Cardinality: 24  
                                                       51 TABLE ACCESS FULL TABLE STAT_RPT.MASTER_RESP_MEMBER Cost: 12  Bytes: 57,304  Cardinality: 1,976       
                                                  53 TABLE ACCESS FULL TABLE STAT_RPT.RPT_SERIES_AS_OF_DT Cost: 10  Bytes: 48,990  Cardinality: 3,266            
                                             55 INDEX FAST FULL SCAN INDEX STAT_RPT.NX_RPT_SERIES_FILING Cost: 201  Bytes: 918,490  Cardinality: 91,849                 
                                        59 VIEW VIEW SYS.VW_NSO_3 Cost: 30  Bytes: 1,053,672  Cardinality: 8,168                      
                                             58 HASH UNIQUE  Cost: 30  Bytes: 16,336  Cardinality: 8,168                 
                                                  57 COLLECTION ITERATOR PICKLER FETCH PROCEDURE PKG_UTILITY.FN_STRING_TO_TAB Cost: 29  Bytes: 16,336  Cardinality: 8,168            
                              64 SORT AGGREGATE  Bytes: 11  Cardinality: 1                                
                                   63 INDEX RANGE SCAN INDEX (UNIQUE) STAT_RPT.UK_RPT_SERIES_AS_OF_DT Cost: 2  Bytes: 792  Cardinality: 72                           
                                        62 TABLE ACCESS FULL TABLE STAT_RPT.RPT_SERIES Cost: 8  Bytes: 11  Cardinality: 1                      
but when I do not use the function the performance is very good and the plan looks good too. Taking 6 secs
 
/* Formatted on 2/15/2013 3:31:02 PM (QP5 v5.139.911.3011) */
--FFIEC009,  AODT_C,  ,  ,  RR_All,  ,  0,  ,  12,  RV_L,  DRAFT-INT,DRAFT-EXT,HELD,RELEASED,ACTIVE,OBSOLETE,DISCARDED,  RV_L,  ,  ,  Actual,  Estimated,  N,  ,  ,  ,  I,  RSSD_ID,  ASC,  1,  25,  l1cmj0b
SELECT V.*, ROW_NUMBER () OVER (ORDER BY 1) rn
FROM   v_rpt_filing_summary v
WHERE  SERIES_ID IN ('FFIEC009')
  --    (SELECT tabular_list
   -- FROM   TABLE (pkg_utility.fn_string_to_tab ('FFIEC009')))
       AND as_of_dt =
              (SELECT MAX (as_of_dt)
               FROM   rpt_series_as_of_dt
               WHERE  as_of_dt <= TRUNC (SYSDATE)
                      AND rpt_series_sk = (SELECT rpt_series_sk
                                           FROM   rpt_series
                                           WHERE  series_id = v.series_id))
       AND DISTRICT_NR IN ('12')
 --             (SELECT tabular_list
        --      FROM   TABLE (pkg_utility.fn_string_to_tab ('12')))
       AND NSAM_USR.PKG_SECURITY.
           FN_VALIDATE_SERIES_DIST_ACCESS (UPPER ('l1cmj0b'),
                                           SERIES_ID,
                                           DISTRICT_NR) > 0
       AND district_nr IS NOT NULL
       AND v.RPT_SERIES_AS_OF_DT_SK NOT IN
              (SELECT DISTINCT x.RPT_SERIES_AS_OF_DT_SK
               FROM   rpt_series_filing x
               WHERE  X.FILING_STATUS_CD IN ('ACTIVE', 'HELD')
                      AND X.RSSD_ID = v.rssd_id)
UNION
SELECT *
FROM   (SELECT rs.SERIES_ID,
               FORM_TITLE_TXT,
               NULL CREATE_USER_ID,
               mrm.RSSD_ID,
               rsaodt.AS_OF_DT,
               NULL SUBMISSION_DT,
               NULL FILING_STATUS_CD,
               NULL ESTIMATED_FLG,
               NULL ESTIMATED_ALLOW_FLG,
               NULL MASTER_RESP_LIST_SK,
               NULL RPT_SERIES_GET_REVISED_FLG,
               NULL DUE_DAY,
               NULL DUE_DT,
               mrm.DISTRICT_NR ML_DISTRICT_NR,
               NULL SUBMITTER_ID,
               NULL SUBMITTER_NAME,
               NULL RPT_SERIES_SK,
               (SELECT ABBRV_CUST_NM
                FROM   STRUCTR.fi
                WHERE  rssd_id = mrm.RSSD_ID)
                  REPORTER_NAME,
               NULL RPT_SERIES_FILING_SK,
               NULL VERSION_NUMBER,
               NULL RPT_SERIES_AS_OF_DT_SK,
               NULL USER_TYPE,
               NULL EDIT_FLG,
               NULL DERIVED_STATUS,
               NULL ESTIMATED_BY,
               NULL FILING_TIMESTAMP,
               NULL DISTRICT_NAME,
               NULL EXT_DISPLAY_STATUS,
               NULL INT_DISPLAY_STATUS,
               NULL REPORT_VERSIONS,
               NULL create_dt,
               NULL LAST_UPDT_DT,
               NULL LAST_UPDT_USER_ID,
               PRIOR_OFFSET_DAYS_NR,
               PRIOR_OFFSET_DAYS_TS,
               ROW_NUMBER () OVER (ORDER BY 1) rn
        FROM   rpt_series rs,
               rpt_series_as_of_dt RSAODT,
               master_resp_member mrm,
               rpt_series_parm rsp
        WHERE  SERIES_ID IN ('FFIEC009')
              --    (SELECT tabular_list
            --       FROM   TABLE (pkg_utility.fn_string_to_tab (:series_list)))
               AND as_of_dt =
                      (SELECT MAX (as_of_dt)
                       FROM   rpt_series_as_of_dt
                       WHERE  as_of_dt <= TRUNC (SYSDATE)
                              AND rpt_series_sk =
                                     (SELECT rpt_series_sk
                                      FROM   rpt_series
                                      WHERE  series_id = rs.series_id))
               AND DISTRICT_NR IN ('12')
                     -- (SELECT tabular_list
                   --    FROM   TABLE (
                   --              pkg_utility.fn_string_to_tab (:dist_list)))
               AND RS.RPT_SERIES_SK = RSAODT.RPT_SERIES_SK
               AND RS.RPT_SERIES_SK = RSP.RPT_SERIES_SK
               AND RSP.MASTER_RESP_LIST_SK = MRM.MASTER_RESP_LIST_SK
               AND as_of_dt BETWEEN MRM.MEMBER_FIRST_AS_OF_DT
                                AND MRM.MEMBER_LAST_AS_OF_DT
               AND AS_OF_DT BETWEEN RSP.RPT_SERIES_START_DT
                                AND RSP.RPT_SERIES_END_DT
               AND rsaodt.RPT_SERIES_AS_OF_DT_SK NOT IN
                      (SELECT x.RPT_SERIES_AS_OF_DT_SK
                       FROM   rpt_series_filing x
                       WHERE  rssd_id = mrm.rssd_id)
               AND NSAM_USR.PKG_SECURITY.
                   FN_VALIDATE_SERIES_DIST_ACCESS (
                      UPPER ('l1cmj0b'),
                      SERIES_ID,
                      DISTRICT_NR) > 0
               AND district_nr IS NOT NULL);
           
Plan is
Plan
SELECT STATEMENT  ALL_ROWSCost: 579  Bytes: 2,813  Cardinality: 2                                                                   
     59 SORT UNIQUE  Cost: 579  Bytes: 2,813  Cardinality: 2                                                              
          58 UNION-ALL                                                         
               3 COUNT STOPKEY                                                    
                    2 TABLE ACCESS BY INDEX ROWID TABLE STAT_RPT.MASTER_RESP_MEMBER Cost: 3  Bytes: 29  Cardinality: 1                                               
                         1 INDEX RANGE SCAN INDEX (UNIQUE) STAT_RPT.UK_MASTER_RESP_MEMBER Cost: 2  Cardinality: 1                                          
               9 VIEW VIEW NSAM_USR.V_USER_ID_NAME Cost: 10  Bytes: 178  Cardinality: 2                                                    
                    8 SORT UNIQUE  Cost: 10  Bytes: 41  Cardinality: 2                                               
                         7 UNION-ALL                                          
                              4 TABLE ACCESS FULL TABLE NSAM_USR.EXT_USER_PROFILE Cost: 7  Bytes: 18  Cardinality: 1                                     
                              6 TABLE ACCESS BY INDEX ROWID TABLE NSAM_USR.INT_USER_PROFILE Cost: 1  Bytes: 23  Cardinality: 1                                     
                                   5 INDEX UNIQUE SCAN INDEX (UNIQUE) NSAM_USR.UK_INT_USER_PROFILE Cost: 0  Cardinality: 1                                
               10 TABLE ACCESS FULL TABLE STAT_RPT.RPT_SERIES_VERSION Cost: 4  Bytes: 22  Cardinality: 1                                                    
               15 TABLE ACCESS BY INDEX ROWID TABLE STRUCTR.FRB_DISTRICT Cost: 1  Bytes: 13  Cardinality: 1                                                    
                    14 INDEX UNIQUE SCAN INDEX (UNIQUE) STRUCTR.PK_FRB_DISTRICT Cost: 0  Cardinality: 1                                               
                         13 COUNT STOPKEY                                          
                              12 TABLE ACCESS BY INDEX ROWID TABLE STAT_RPT.MASTER_RESP_MEMBER Cost: 3  Bytes: 29  Cardinality: 1                                     
                                   11 INDEX RANGE SCAN INDEX (UNIQUE) STAT_RPT.UK_MASTER_RESP_MEMBER Cost: 2  Cardinality: 1                                
               19 SORT AGGREGATE  Bytes: 19  Cardinality: 1                                                    
                    18 INDEX RANGE SCAN INDEX STAT_RPT.NX_RPT_SERIES_FILING Cost: 3  Bytes: 57  Cardinality: 3                                               
                         17 TABLE ACCESS BY INDEX ROWID TABLE STAT_RPT.RPT_SERIES_AS_OF_DT Cost: 2  Bytes: 15  Cardinality: 1                                          
                              16 INDEX UNIQUE SCAN INDEX (UNIQUE) STAT_RPT.UK_RPT_SERIES_AS_OF_DT Cost: 1  Cardinality: 1                                     
               37 WINDOW NOSORT  Cost: 545  Bytes: 2,618  Cardinality: 1                                                    
                    36 FILTER                                               
                         32 HASH JOIN RIGHT ANTI  Cost: 534  Bytes: 39,270  Cardinality: 15                                          
                              20 INDEX FAST FULL SCAN INDEX STAT_RPT.NX_RPT_SERIES_FILING Cost: 201  Bytes: 597,759  Cardinality: 31,461                                     
                              31 VIEW VIEW STAT_RPT.V_RPT_FILING_SUMMARY Cost: 332  Bytes: 3,984,267  Cardinality: 1,533                                     
                                   30 SORT ORDER BY  Cost: 332  Bytes: 225,351  Cardinality: 1,533                                
                                        29 HASH JOIN  Cost: 331  Bytes: 225,351  Cardinality: 1,533                           
                                             27 HASH JOIN  Cost: 20  Bytes: 4,125  Cardinality: 55                      
                                                  25 NESTED LOOPS                 
                                                       23 NESTED LOOPS  Cost: 13  Bytes: 3,692  Cardinality: 142            
                                                            21 TABLE ACCESS FULL TABLE STAT_RPT.RPT_SERIES Cost: 8  Bytes: 11  Cardinality: 1       
                                                            22 INDEX RANGE SCAN INDEX (UNIQUE) STAT_RPT.UK_RPT_SERIES_AS_OF_DT Cost: 1  Cardinality: 142       
                                                       24 TABLE ACCESS BY INDEX ROWID TABLE STAT_RPT.RPT_SERIES_AS_OF_DT Cost: 5  Bytes: 2,130  Cardinality: 142            
                                                  26 TABLE ACCESS FULL TABLE STAT_RPT.RPT_SERIES_PARM Cost: 6  Bytes: 1,176  Cardinality: 24                 
                                             28 TABLE ACCESS FULL TABLE STAT_RPT.RPT_SERIES_FILING Cost: 311  Bytes: 6,613,128  Cardinality: 91,849                      
                         35 SORT AGGREGATE  Bytes: 11  Cardinality: 1                                          
                              34 INDEX RANGE SCAN INDEX (UNIQUE) STAT_RPT.UK_RPT_SERIES_AS_OF_DT Cost: 2  Bytes: 792  Cardinality: 72                                     
                                   33 TABLE ACCESS FULL TABLE STAT_RPT.RPT_SERIES Cost: 8  Bytes: 11  Cardinality: 1                                
               39 TABLE ACCESS BY INDEX ROWID TABLE STRUCTR.FI_EXT Cost: 3  Bytes: 25  Cardinality: 1                                                    
                    38 INDEX UNIQUE SCAN INDEX (UNIQUE) STRUCTR.PK_FI_EXT Cost: 2  Cardinality: 1                                               
               57 VIEW STAT_RPT. Cost: 33  Bytes: 195  Cardinality: 1                                                    
                    56 WINDOW NOSORT  Cost: 33  Bytes: 106  Cardinality: 1                                               
                         55 NESTED LOOPS ANTI  Cost: 23  Bytes: 106  Cardinality: 1                                          
                              53 NESTED LOOPS  Cost: 21  Bytes: 96  Cardinality: 1                                     
                                   50 NESTED LOOPS  Cost: 11  Bytes: 67  Cardinality: 1                                
                                        47 NESTED LOOPS  Cost: 9  Bytes: 26  Cardinality: 1                           
                                             41 TABLE ACCESS BY INDEX ROWID TABLE STAT_RPT.RPT_SERIES Cost: 8  Bytes: 11  Cardinality: 1                      
                                                  40 INDEX FULL SCAN INDEX (UNIQUE) STAT_RPT.PK_RPT_SERIES Cost: 1  Cardinality: 24                 
                                             46 TABLE ACCESS BY INDEX ROWID TABLE STAT_RPT.RPT_SERIES_AS_OF_DT Cost: 1  Bytes: 15  Cardinality: 1                      
                                                  45 INDEX UNIQUE SCAN INDEX (UNIQUE) STAT_RPT.UK_RPT_SERIES_AS_OF_DT Cost: 0  Cardinality: 1                 
                                                       44 SORT AGGREGATE  Bytes: 11  Cardinality: 1            
                                                            43 INDEX RANGE SCAN INDEX (UNIQUE) STAT_RPT.UK_RPT_SERIES_AS_OF_DT Cost: 2  Bytes: 792  Cardinality: 72       
                                                                 42 TABLE ACCESS FULL TABLE STAT_RPT.RPT_SERIES Cost: 8  Bytes: 11  Cardinality: 1  
                                        49 TABLE ACCESS BY INDEX ROWID TABLE STAT_RPT.RPT_SERIES_PARM Cost: 2  Bytes: 41  Cardinality: 1                           
                                             48 INDEX RANGE SCAN INDEX (UNIQUE) STAT_RPT.UK_MASTER_LIST Cost: 1  Cardinality: 1                      
                                   52 TABLE ACCESS BY INDEX ROWID TABLE STAT_RPT.MASTER_RESP_MEMBER Cost: 10  Bytes: 29  Cardinality: 1                                
                                        51 INDEX RANGE SCAN INDEX (UNIQUE) STAT_RPT.UK_MASTER_RESP_MEMBER Cost: 1  Cardinality: 86                           
                              54 INDEX RANGE SCAN INDEX STAT_RPT.NX_RPT_SERIES_FILING Cost: 2  Bytes: 36,150  Cardinality: 3,615                                     
Question is
What is the performance problem with the function FN_string_to_tab.
Do we have any other alternative in 11gR2.
We have to use some function to change the string to tab beacuse that is what web tier is passsing to the PL/SQL package. This is already on production and need to address with minmal change.
Thanks a lot.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points