1 Reply Latest reply: Feb 17, 2013 5:39 AM by Srini Chavali-Oracle RSS

    Performance problem with String to Table

    225452
      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.