Need this plsql code tuned, can create object types if needed.

create or replace package body        y
as
PROCEDURE x
is
  v_cmt_run_id ENGINE_RUN_STATUS.ENG_RUN_ID%type;
  v_cmt_run_dt DATE;
cursor c_funds is
SELECT REFERENCE_CD, REFERENCE_VALUE_TXT FROM CAS_REFERENCE_CODE WHERE REFERENCE_DESC = 'RELEASE1_FUNDS';
cursor lcur_cmt_crd(v_cmt_run_id IN ENGINE_RUN_STATUS.ENG_RUN_ID%type,
                    v_ref_cd CAS_REFERENCE_CODE.REFERENCE_CD%type, 
                    v_ref_val_txt CAS_REFERENCE_CODE.REFERENCE_VALUE_TXT%type) is
SELECT CAS_BKT_CMP_CMT_CRD_SEQ.nextval RUN_ID,
    TRUNC(sysdate) RUN_DT,
    v_cmt_run_dt CMT_ENG_RUN_DT,
    CMT_INDUST_CLASS_CD,
    CRD_INDUST_CLASS_CD,
    CMT_FUND_SHRT_NAME,
    CRD_FUND_SHRT_NAME,
    CMT_RULE_KEY_ID,
    CRD_RULE_KEY_ID,
    CMT_RULE_ID_CURR_NM,
    CRD_RULE_ID_CURR_NM,
    CMT_TEST_CATEGORY,
    CRD_TEST_CATEGORY,
    CMT_BUCKET_VAL,
    CRD_BUCKET_VAL,
    CMT_MKT_VAL,
    CRD_MKT_VAL,
    CMT_MKT_QTY,
    CRD_MKT_QTY,
    CMT_TRADABLE_ENT_ID,
    CRD_TRADABLE_ENT_ID,
    CMT_TICKER,
    CRD_TICKER,
    CRD_KNOWN_DIFF,
    SYSTIMESTAMP INSERT_TIMESTAMP,
    CASE
      WHEN CMT_BUCKET_VAL = CRD_BUCKET_VAL
      THEN 'Y'
      ELSE 'N'
    END RESULT
  FROM
    (SELECT a.PC_NM CMT_INDUST_CLASS_CD,
      a.sname CMT_FUND_SHRT_NAME,
      a.RULE_KEY_ID CMT_RULE_KEY_ID,
      a.RULE_ID_CURR_NM CMT_RULE_ID_CURR_NM,
      a.TEST_CATEGORY CMT_TEST_CATEGORY,
      a.BUCKET_VAL CMT_BUCKET_VAL,
      a.PRORAT_SHARE_AMT CMT_MKT_VAL,
      a.PRORAT_MKT_VALUE_AMT CMT_MKT_QTY,
      TO_CHAR(a.TRADABLE_ENT_ID) CMT_TRADABLE_ENT_ID,
      a.TICKER CMT_TICKER,
      b.INDUST_CLASS_CD CRD_INDUST_CLASS_CD,
      b.FUND_SHRT_NAME CRD_FUND_SHRT_NAME,
      b.RULE_KEY_ID CRD_RULE_KEY_ID,
      b.RULE_ID_CURR_NM CRD_RULE_ID_CURR_NM,
      b.TEST_CATEGORY CRD_TEST_CATEGORY,
      b.BUCKET_VAL CRD_BUCKET_VAL,
      b.MKT_VAL_SOD CRD_MKT_VAL,
      b.QTY_SOD CRD_MKT_QTY,
      TO_CHAR(b.TRADABLE_ENT_ID) CRD_TRADABLE_ENT_ID,
      b.TICKER CRD_TICKER,
      b.CRD_KNOWN_DIFF CRD_KNOWN_DIFF    FROM
      (SELECT -- industry rules
        CASE
          WHEN SUBSTR(pc.PC_NM,instr(pc.PC_NM,'.',-4,1)+1) = 'INCO'
          THEN 'FACTS'
          WHEN SUBSTR(pc.PC_NM,instr(pc.PC_NM,'.',-4,1)+1) = 'GICS.1'
          THEN 'GSECT'
          WHEN SUBSTR(pc.PC_NM,instr(pc.PC_NM,'.',-4,1)+1) = 'GICS.3'
          THEN 'GIND'
          WHEN SUBSTR(pc.PC_NM,instr(pc.PC_NM,'.',-4,1)+1) = 'GICS.4'
          THEN 'GSIND'
          WHEN SUBSTR(pc.PC_NM,instr(pc.PC_NM,'.',-4,1)+1) = 'GICS.2'
          THEN 'GINDG'
          WHEN SUBSTR(pc.PC_NM,instr(pc.PC_NM,'.',-4,1)+1) = 'LEHM'
          THEN 'LBCC'
          ELSE SUBSTR(pc.PC_NM,instr(pc.PC_NM,'.',-4,1)+1)
        END PC_NM ,
        fn.sname,
        pc.RULE_KEY_ID,
        r.rule_id_curr_nm,
        CASE
          WHEN rv.TEST_METH_REF_ID IN (101)
          THEN NULL
          ELSE TO_CHAR(t.TRADABLE_ENT_ID)
        END TEST_CATEGORY,
        CASE
          WHEN rv.NUMERATOR_REF_ID = 91
          THEN pbd.PRORAT_MKT_VALUE_AMT
          WHEN rv.NUMERATOR_REF_ID = 92
          THEN pbd.PRORAT_SHARE_AMT
          WHEN rv.NUMERATOR_REF_ID = 94
          THEN pbd.PRORAT_SHARE_AMT
        END bucket_val,
        pbd.PRORAT_SHARE_AMT,
        pbd.PRORAT_MKT_VALUE_AMT,
        t.TRADABLE_ENT_ID,
        t.FMR_SYMBOL TICKER
      FROM PORTFOLIO_CONTAINER pc
      INNER JOIN PORTFOLIO_BUCKET_DETAIL pbd
      ON pbd.PC_KEY_ID = pc.PC_KEY_ID
      INNER JOIN TRADABLE_ENT_ALT_ID_EFF_PVT t
      ON t.FMR_ID = SUBSTR(pbd.SEC_CUSIP_NM,0,9)
      INNER JOIN fund fn
      ON fn.ts_cusip = SUBSTR(pc.FUND_CUSIP,0,9)
      INNER JOIN rule r
      ON r.rule_key_id = pc.rule_key_id
      INNER JOIN rule_version rv
      ON rv.rule_key_id      = r.rule_key_id
      AND rv.write_lock_ind IS NULL
      WHERE pc.ENG_RUN_ID = v_cmt_run_id
      AND pc.RULE_KEY_ID IN
        (SELECT fv.RULE_KEY_ID
        FROM RULE_VERSION fv
        LEFT JOIN FM_RULE_VERSION frv
        ON fv.RULE_KEY_ID   = frv.RULE_KEY_ID
        AND fv.RULE_VER_NUM = frv.RULE_VER_NUM
        LEFT JOIN benchmark_RULE_VERSION brv
        ON fv.RULE_KEY_ID            = brv.RULE_KEY_ID
        AND fv.RULE_VER_NUM          = brv.RULE_VER_NUM
        WHERE ((frv.TEST_CATG_REF_ID = 1051
        AND frv.RULE_TYPE_REF_ID    != 83)
        OR (brv.TEST_CATG_REF_ID     = 1051
        AND brv.RULE_TYPE_REF_ID!    = '83'))
        AND fv.WRITE_LOCK_IND       IS NULL
        )
      AND pbd.VALUE_STATE_REF_ID = 156 --EOD
      AND fn.sname = v_ref_cd
      UNION                            -- country rules
      SELECT pc.PC_NM,
        fn.sname ,
        pc.RULE_KEY_ID,
        r.rule_id_curr_nm,
        CASE
          WHEN rv.TEST_METH_REF_ID IN (101)
          THEN NULL
          ELSE TO_CHAR(t.TRADABLE_ENT_ID)
        END TEST_CATEGORY,
        CASE
          WHEN rv.NUMERATOR_REF_ID = 91
          THEN pbd.PRORAT_MKT_VALUE_AMT
          WHEN rv.NUMERATOR_REF_ID = 92
          THEN pbd.PRORAT_SHARE_AMT
          WHEN rv.NUMERATOR_REF_ID = 94
          THEN pbd.PRORAT_SHARE_AMT
        END bucket_val,
        pbd.PRORAT_SHARE_AMT,
        pbd.PRORAT_MKT_VALUE_AMT,
        t.TRADABLE_ENT_ID,
        t.FMR_SYMBOL TICKER
      FROM PORTFOLIO_CONTAINER pc
      INNER JOIN PORTFOLIO_BUCKET_DETAIL pbd
      ON pbd.PC_KEY_ID = pc.PC_KEY_ID
      INNER JOIN COUNTRY_CODES_V ccv
      ON ccv.COUNTRY_CD = pbd.SUMM_VAL_TXT
      INNER JOIN TRADABLE_ENT_ALT_ID_EFF_PVT t
      ON t.FMR_ID = SUBSTR(pbd.SEC_CUSIP_NM,0,9)
      INNER JOIN fund fn
      ON fn.ts_cusip = SUBSTR(pc.FUND_CUSIP,0,9)
      INNER JOIN rule r
      ON r.rule_key_id = pc.rule_key_id
      INNER JOIN rule_version rv
      ON rv.rule_key_id      = r.rule_key_id
      AND rv.write_lock_ind IS NULL
      WHERE pc.ENG_RUN_ID = v_cmt_run_id
      AND pc.RULE_KEY_ID IN
        (SELECT fv.RULE_KEY_ID
        FROM RULE_VERSION fv
        LEFT JOIN FM_RULE_VERSION frv
        ON fv.RULE_KEY_ID   = frv.RULE_KEY_ID
        AND fv.RULE_VER_NUM = frv.RULE_VER_NUM
        LEFT JOIN benchmark_RULE_VERSION brv
        ON fv.RULE_KEY_ID            = brv.RULE_KEY_ID
        AND fv.RULE_VER_NUM          = brv.RULE_VER_NUM
        WHERE ((frv.TEST_CATG_REF_ID = 112
        AND frv.RULE_TYPE_REF_ID    != 83)
        OR (brv.TEST_CATG_REF_ID     = 112
        AND brv.RULE_TYPE_REF_ID!    = '83'))
        AND fv.WRITE_LOCK_IND       IS NULL
        )
      AND pbd.VALUE_STATE_REF_ID = 156
      AND fn.sname = v_ref_cd
      UNION                             -- currency rules
      SELECT pc.PC_NM,
        fn.sname,
        pc.RULE_KEY_ID,
        r.rule_id_curr_nm,
        CASE
          WHEN rv.TEST_METH_REF_ID IN (101)
          THEN NULL
          ELSE TO_CHAR(t.TRADABLE_ENT_ID)
        END TEST_CATEGORY,
        CASE
          WHEN rv.NUMERATOR_REF_ID = 91
          THEN pbd.PRORAT_MKT_VALUE_AMT
          WHEN rv.NUMERATOR_REF_ID = 92
          THEN pbd.PRORAT_SHARE_AMT
          WHEN rv.NUMERATOR_REF_ID = 94
          THEN pbd.PRORAT_SHARE_AMT
        END bucket_val,
        pbd.PRORAT_SHARE_AMT,
        pbd.PRORAT_MKT_VALUE_AMT,
        t.TRADABLE_ENT_ID,
        t.FMR_SYMBOL TICKER
      FROM PORTFOLIO_CONTAINER pc
      INNER JOIN PORTFOLIO_BUCKET_DETAIL pbd
      ON pbd.PC_KEY_ID = pc.PC_KEY_ID
      INNER JOIN CURRENCY_CODES_V ccv
      ON ccv.CURRENCY_CD = pbd.SUMM_VAL_TXT
      INNER JOIN TRADABLE_ENT_ALT_ID_EFF_PVT t
      ON t.FMR_ID = SUBSTR(pbd.SEC_CUSIP_NM,0,9)
      INNER JOIN fund fn
      ON fn.ts_cusip = SUBSTR(pc.FUND_CUSIP,0,9)
      INNER JOIN rule r
      ON r.rule_key_id = pc.rule_key_id
      INNER JOIN rule_version rv
      ON rv.rule_key_id      = r.rule_key_id
      AND rv.write_lock_ind IS NULL
      WHERE pc.ENG_RUN_ID = v_cmt_run_id
      AND pc.RULE_KEY_ID IN
        (SELECT fv.RULE_KEY_ID
        FROM RULE_VERSION fv
        LEFT JOIN FM_RULE_VERSION frv
        ON fv.RULE_KEY_ID   = frv.RULE_KEY_ID
        AND fv.RULE_VER_NUM = frv.RULE_VER_NUM
        LEFT JOIN benchmark_RULE_VERSION brv
        ON fv.RULE_KEY_ID            = brv.RULE_KEY_ID
        AND fv.RULE_VER_NUM          = brv.RULE_VER_NUM
        WHERE ((frv.TEST_CATG_REF_ID = 113
        AND frv.RULE_TYPE_REF_ID    != 83)
        OR (brv.TEST_CATG_REF_ID     = 113
        AND brv.RULE_TYPE_REF_ID!    = '83'))
        AND fv.WRITE_LOCK_IND       IS NULL
        )
      AND pbd.VALUE_STATE_REF_ID = 156
      AND fn.sname = v_ref_cd
      UNION                           -- security rules
      SELECT pc.PC_NM,
        fn.sname,
        pc.RULE_KEY_ID,
        r.rule_id_curr_nm,
        CASE
          WHEN rv.TEST_METH_REF_ID IN (101)
          THEN NULL
          ELSE TO_CHAR(t.TRADABLE_ENT_ID)
        END TEST_CATEGORY,
        CASE
          WHEN rv.NUMERATOR_REF_ID = 91
          THEN pbd.PRORAT_MKT_VALUE_AMT
          WHEN rv.NUMERATOR_REF_ID = 92
          THEN pbd.PRORAT_SHARE_AMT
          WHEN rv.NUMERATOR_REF_ID = 94
          THEN pbd.PRORAT_SHARE_AMT
        END bucket_val,
        pbd.PRORAT_MKT_VALUE_AMT,
        pbd.PRORAT_SHARE_AMT,
        t.TRADABLE_ENT_ID,
        t.FMR_SYMBOL TICKER
      FROM PORTFOLIO_CONTAINER pc
      INNER JOIN PORTFOLIO_BUCKET_DETAIL pbd
      ON pbd.PC_KEY_ID = pc.PC_KEY_ID
      INNER JOIN TRADABLE_ENT_ALT_ID_EFF_PVT t
      ON t.FMR_ID = SUBSTR(pbd.SEC_CUSIP_NM,0,9)
      INNER JOIN fund fn
      ON fn.ts_cusip = SUBSTR(pc.FUND_CUSIP,0,9)
      INNER JOIN rule r
      ON r.rule_key_id = pc.rule_key_id
      INNER JOIN rule_version rv
      ON rv.rule_key_id      = r.rule_key_id
      AND rv.write_lock_ind IS NULL
      WHERE pc.ENG_RUN_ID = v_cmt_run_id
      AND pc.RULE_KEY_ID IN
        (SELECT fv.RULE_KEY_ID
        FROM RULE_VERSION fv
        LEFT JOIN FM_RULE_VERSION frv
        ON fv.RULE_KEY_ID   = frv.RULE_KEY_ID
        AND fv.RULE_VER_NUM = frv.RULE_VER_NUM
        LEFT JOIN benchmark_RULE_VERSION brv
        ON fv.RULE_KEY_ID            = brv.RULE_KEY_ID
        AND fv.RULE_VER_NUM          = brv.RULE_VER_NUM
        WHERE ((frv.TEST_CATG_REF_ID = 110
        AND frv.RULE_TYPE_REF_ID    != 83)
        OR (brv.TEST_CATG_REF_ID     = 110
        AND brv.RULE_TYPE_REF_ID!    = '83'))
        AND fv.WRITE_LOCK_IND       IS NULL
        )
      AND pbd.VALUE_STATE_REF_ID = 156
      AND fn.sname = v_ref_cd
      UNION                           -- state code rules
      SELECT pc.PC_NM,
        fn.sname,
        pc.RULE_KEY_ID,
        r.RULE_ID_CURR_NM,
        CASE
          WHEN rv.TEST_METH_REF_ID IN (101)
          THEN NULL
          ELSE TO_CHAR(t.TRADABLE_ENT_ID)
        END TEST_CATEGORY,
        CASE
          WHEN rv.NUMERATOR_REF_ID = 91
          THEN pbd.PRORAT_MKT_VALUE_AMT
          WHEN rv.NUMERATOR_REF_ID = 92
          THEN pbd.PRORAT_SHARE_AMT
          WHEN rv.NUMERATOR_REF_ID = 94
          THEN pbd.PRORAT_SHARE_AMT
        END bucket_val,
        pbd.PRORAT_SHARE_AMT,
        pbd.PRORAT_MKT_VALUE_AMT,
        t.TRADABLE_ENT_ID,
        t.FMR_SYMBOL TICKER
      FROM PORTFOLIO_CONTAINER pc
      INNER JOIN PORTFOLIO_BUCKET_DETAIL pbd
      ON pbd.PC_KEY_ID = pc.PC_KEY_ID
      INNER JOIN rule r
      ON r.RULE_KEY_ID = pc.RULE_KEY_ID
      INNER JOIN TRADABLE_ENT_ALT_ID_EFF_PVT t
      ON t.FMR_ID = SUBSTR(pbd.SEC_CUSIP_NM,0,9)
      INNER JOIN fund fn
      ON fn.ts_cusip = SUBSTR(pc.FUND_CUSIP,0,9)
      INNER JOIN rule r
      ON r.rule_key_id = pc.rule_key_id
      INNER JOIN rule_version rv
      ON rv.rule_key_id      = r.rule_key_id
      AND rv.write_lock_ind IS NULL
      WHERE pc.ENG_RUN_ID = v_cmt_run_id
      AND pc.RULE_KEY_ID IN
        (SELECT fv.RULE_KEY_ID
        FROM RULE_VERSION fv
        LEFT JOIN FM_RULE_VERSION frv
        ON fv.RULE_KEY_ID   = frv.RULE_KEY_ID
        AND fv.RULE_VER_NUM = frv.RULE_VER_NUM
        LEFT JOIN benchmark_RULE_VERSION brv
        ON fv.RULE_KEY_ID            = brv.RULE_KEY_ID
        AND fv.RULE_VER_NUM          = brv.RULE_VER_NUM
        WHERE ((frv.TEST_CATG_REF_ID = 118
        AND frv.RULE_TYPE_REF_ID    != 83)
        OR (brv.TEST_CATG_REF_ID     = 118
        AND brv.RULE_TYPE_REF_ID!    = '83'))
        AND fv.WRITE_LOCK_IND       IS NULL
        )
      AND pbd.VALUE_STATE_REF_ID = 156
      AND fn.sname = v_ref_cd
      ) a
      LEFT OUTER JOIN
      (WITH dt AS
      (SELECT t.FOR_EACH_VAL_1
        || ','
        || t.FOR_EACH_VAL_2 INDUST_CLASS_CD ,
        f.acct_cd FUND_SHRT_NAME,
        bm.test_id RULE_KEY_ID,
        TO_CHAR(SUBSTR(t.DESCRIPTION,instr(t.DESCRIPTION,'COMET ID:',1,1)+9,instr(t.DESCRIPTION,'DATASET ID:')-(instr(t.DESCRIPTION,'COMET ID:',1,1)+9)-1)) RULE_ID_CURR_NM,
        CASE
          WHEN SUBSTR(bm.BUCKET_CD,instr(bm.BUCKET_CD,'_')+1,instr(bm.BUCKET_CD,'_',1,2)-instr(bm.BUCKET_CD,'_')-1) = s.SEC_ID
          THEN s.EXT_SEC_ID
          ELSE SUBSTR(bm.BUCKET_CD,instr(bm.BUCKET_CD,'_')+1,instr(bm.BUCKET_CD,'_',1,2)-instr(bm.BUCKET_CD,'_')-1)
        END TEST_CATEGORY,
        bm.BUCKET_VAL BUCKET_VAL,
        pos.mkt_val_sod MKT_VAL_SOD,
        pos.qty_sod QTY_SOD,
        s.EXT_SEC_ID TRADABLE_ENT_ID,
        s.TICKER,
        TO_CHAR(SUBSTR(t.DESCRIPTION,instr(t.DESCRIPTION,'KNOWN DIFFERENCES:',1,1)+19,instr(t.DESCRIPTION,'COMET ID:')-(instr(t.DESCRIPTION,'KNOWN DIFFERENCES:',1,1)+19)-1)) CRD_KNOWN_DIFF
      FROM CS_BUCKET_MEMBER_V2 bm
      INNER JOIN cs_fund f
      ON f.ACCT_CD = bm.position_acct_cd
      INNER JOIN CS_TEST_V2 t
      ON t.TEST_ID = bm.TEST_ID
      INNER JOIN CSM_SECURITY s
      ON s.SEC_ID = bm.sec_id
      INNER JOIN CS_POSITION pos
      ON pos.acct_cd    = bm.POSITION_ACCT_CD
      AND pos.sec_id    = bm.SEC_ID
      WHERE BUCKET_TYPE = 'P'
      AND t.UNITS      != 'Exclusion' AND f.acct_cd = v_ref_cd
      )
    SELECT x.INDUST_CLASS_CD INDUST_CLASS_CD,
      x.FUND_SHRT_NAME FUND_SHRT_NAME,
      x.RULE_KEY_ID RULE_KEY_ID,
      x.TEST_CATEGORY TEST_CATEGORY,
      EXTRACT (VALUE (d), '//row/text()').getstringval () RULE_ID_CURR_NM,
      x.BUCKET_VAL BUCKET_VAL,
      x.MKT_VAL_SOD MKT_VAL_SOD,
      x.QTY_SOD QTY_SOD,
      x.TRADABLE_ENT_ID TRADABLE_ENT_ID,
      x.TICKER,
      x.CRD_KNOWN_DIFF CRD_KNOWN_DIFF
    FROM
      (SELECT dt.*,
        XMLTYPE ( '<rows><row>'
        || REPLACE (RULE_ID_CURR_NM, ',', '</row><row>')
        || '</row></rows>' ) AS xmlval
      FROM dt
      ) x,
      TABLE (XMLSEQUENCE (EXTRACT (x.xmlval, '/rows/row'))) d
            ) b ON a.sname            = b.FUND_SHRT_NAME
    AND trim(a.RULE_ID_CURR_NM) = trim(b.RULE_ID_CURR_NM)
    AND a.TRADABLE_ENT_ID       = b.TRADABLE_ENT_ID
    AND NVL(a.TEST_CATEGORY, 0) = NVL(b.TEST_CATEGORY, 0)
    );
  /********/
  /* type */
  /********/
  type lcur_cmt_crd_tab is table of lcur_cmt_crd%rowtype;
  /************/
  /* variable */
  /************/
  lv_lcur_cmt_crd_tab  lcur_cmt_crd_tab;
begin
SELECT MAX(ENG_RUN_ID) INTO v_cmt_run_id FROM ENGINE_RUN_STATUS WHERE ENGINE_KEY_ID = 2;

SELECT trunc(engine_run_tmstmp) INTO v_cmt_run_dt FROM ENGINE_RUN_STATUS WHERE ENGINE_KEY_ID = 2 and eng_run_id = v_cmt_run_id;

for c_funds_rec in c_funds
loop
open lcur_cmt_crd(v_cmt_run_id, c_funds_rec.REFERENCE_CD, c_funds_rec.REFERENCE_VALUE_TXT);
loop
fetch lcur_cmt_crd bulk collect into lv_lcur_cmt_crd_tab LIMIT 5000;
exit when lv_lcur_cmt_crd_tab.count = 0;
forall lrec_indx in indices of lv_lcur_cmt_crd_tab
INSERT INTO CAS_CMT_CRD_BUCKET_COMPARE(RUN_ID,
RUN_DATE,
CMT_ENG_RUN_DT,
CMT_INDUST_CLASS_CD,
CRD_INDUST_CLASS_CD,
CMT_FUND_SHRT_NAME,
CRD_FUND_SHRT_NAME,
CMT_RULE_KEY_ID,
CRD_RULE_KEY_ID,
CMT_RULE_ID_CURR_NM,
CRD_RULE_ID_CURR_NM,
CMT_TEST_CATEGORY,
CRD_TEST_CATEGORY,
CMT_BUCKET_VAL,
CRD_BUCKET_VAL,
CMT_MKT_VAL,
CRD_MKT_VAL,
CMT_MKT_QTY,
CRD_MKT_QTY,
CMT_TRADABLE_ENT_ID,
CRD_TRADABLE_ENT_ID,
CMT_TICKER,
CRD_TICKER,
KNOWN_DIFF,
INSERT_TIMESTAMP,
RESULT)
  VALUES(lv_lcur_cmt_crd_tab(lrec_indx).RUN_ID,
         lv_lcur_cmt_crd_tab(lrec_indx).RUN_DT,
         lv_lcur_cmt_crd_tab(lrec_indx).CMT_ENG_RUN_DT,
         lv_lcur_cmt_crd_tab(lrec_indx).CMT_INDUST_CLASS_CD,
         lv_lcur_cmt_crd_tab(lrec_indx).CRD_INDUST_CLASS_CD,
         lv_lcur_cmt_crd_tab(lrec_indx).CMT_FUND_SHRT_NAME,
         lv_lcur_cmt_crd_tab(lrec_indx).CRD_FUND_SHRT_NAME,
         lv_lcur_cmt_crd_tab(lrec_indx).CMT_RULE_KEY_ID,
         lv_lcur_cmt_crd_tab(lrec_indx).CRD_RULE_KEY_ID,
         lv_lcur_cmt_crd_tab(lrec_indx).CMT_RULE_ID_CURR_NM,
         lv_lcur_cmt_crd_tab(lrec_indx).CRD_RULE_ID_CURR_NM,
         lv_lcur_cmt_crd_tab(lrec_indx).CMT_TEST_CATEGORY,
         lv_lcur_cmt_crd_tab(lrec_indx).CRD_TEST_CATEGORY,
         lv_lcur_cmt_crd_tab(lrec_indx).CMT_BUCKET_VAL,
         lv_lcur_cmt_crd_tab(lrec_indx).CRD_BUCKET_VAL,
         lv_lcur_cmt_crd_tab(lrec_indx).CMT_MKT_VAL,
         lv_lcur_cmt_crd_tab(lrec_indx).CRD_MKT_VAL,
         lv_lcur_cmt_crd_tab(lrec_indx).CMT_MKT_QTY,
         lv_lcur_cmt_crd_tab(lrec_indx).CRD_MKT_QTY,
         lv_lcur_cmt_crd_tab(lrec_indx).CMT_TRADABLE_ENT_ID,
         lv_lcur_cmt_crd_tab(lrec_indx).CRD_TRADABLE_ENT_ID,
         lv_lcur_cmt_crd_tab(lrec_indx).CMT_TICKER,
         lv_lcur_cmt_crd_tab(lrec_indx).CRD_TICKER,
         lv_lcur_cmt_crd_tab(lrec_indx).CRD_KNOWN_DIFF,
         lv_lcur_cmt_crd_tab(lrec_indx).INSERT_TIMESTAMP,
         lv_lcur_cmt_crd_tab(lrec_indx).RESULT);
end loop;
close lcur_cmt_crd;
end loop;
commit;
EXCEPTION
WHEN OTHERS THEN
  raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
  rollback;
end x;
end y;

Open in new window

skanti1605Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
why convert your csv columns into xml?

search EE or google for the str2tbl function code can be found here or on TomKyte's site

Here's a comparison of functionality

WITH dt AS (SELECT '1,2,3,4,5' rule_id_curr_nm FROM DUAL)
SELECT EXTRACT(VALUE(d), '//row/text()').getstringval() RULE_ID_CURR_NM
  FROM (SELECT dt.*,
               XMLTYPE('<rows><row>' || REPLACE(RULE_ID_CURR_NM, ',', '</row><row>') || '</row></rows>') AS xmlval
          FROM dt) x,
       TABLE(XMLSEQUENCE(EXTRACT(x.xmlval, '/rows/row'))) d;

Open in new window


WITH dt AS (SELECT '1,2,3,4,5' rule_id_curr_nm FROM DUAL)
SELECT RULE_ID_CURR_NM
  FROM (SELECT COLUMN_VALUE RULE_ID_CURR_NM
          FROM dt, TABLE(str2tbl(dt.rule_id_curr_nm, ',')))

Open in new window



also, why fetch the rows if you're not really going to do anything with them?
why not just insert directly off the select?  You're adding overhead to pull the data out of sql and put it into pl/sql collections then turn around and push the collections into another table.

More efficient would be to just do something like

insert into CAS_CMT_CRD_BUCKET_COMPARE (....) select ....;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
skanti1605Author Commented:
The thing is no matter what I do, the query is still running for the same amount of time. Attached is the xplan for the query. Can you see if we can figure anything from it to modify the query to run faster?
skanti1605Author Commented:
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
have you tried the change I made above?

use dbms_xplan to generate your plans and post text.
PortletPaulEE Topic AdvisorCommented:
You have a large unioned query (5 parts, 4 union statements), all referencing tables we know nothing about; not even how large they are. There is some XML data and use of a table function I think.  

The large explain plan shows several full table scans which may indicate lack of indexes. There are function placed on some join predicates, possibly indicating need for function based indexes. e.g.
      INNER JOIN fund fn
      ON fn.ts_cusip = SUBSTR(pc.FUND_CUSIP,0,9)

Open in new window


You are using IN ( subquery )  which can be a performance problem.
     AND pc.RULE_KEY_ID IN
        (SELECT fv.RULE_KEY_ID
        FROM RULE_VERSION fv
        LEFT JOIN FM_RULE_VERSION frv
        ON fv.RULE_KEY_ID   = frv.RULE_KEY_ID
        AND fv.RULE_VER_NUM = frv.RULE_VER_NUM
        LEFT JOIN benchmark_RULE_VERSION brv
        ON fv.RULE_KEY_ID            = brv.RULE_KEY_ID
        AND fv.RULE_VER_NUM          = brv.RULE_VER_NUM
        WHERE ((frv.TEST_CATG_REF_ID = 112
        AND frv.RULE_TYPE_REF_ID    != 83)
        OR (brv.TEST_CATG_REF_ID     = 112
        AND brv.RULE_TYPE_REF_ID!    = '83'))
        AND fv.WRITE_LOCK_IND       IS NULL
        )

Open in new window

Is there are better way to achieve this filter?

Plus you are doing all of this to create a cursor, so you may be executing this rather large mass of sql over and over and over again

I suggest you take EACH query within the union separately and look at it very hard. Optimize just that one piece, using the explain plan of just that piece. Once improved, move on to the next part of the puzzle, also optimize that separately.

Of course it's worth noting that UNION is slower than UNION ALL, and/or that is you reduce the number of columns the UNION may be faster.
skanti1605Author Commented:
Thanks for your tips. I was able to re-write the SQL with a straight insert and no UNION clauses and it helped me achieve better performance.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.