Hi...
I have performance issue with the bulk update. I have 2 tables one with 21 millions of data and the other table with 2 millions of data.here the requirement is to update the table which is having 21 millions with the other tables data(which is having 2 million records) based on some matching criteria.
The procedure is taking 9 hours to update the table(which is having 21 millions data).
Can someone please provide some performance tuning tips for this procedure.. I have created required indexes on table also.But the performance is not good.
Here my procedure:
CREATE OR REPLACE PROCEDURE AHM_GKPR.CLAIMS_WITHOUT_PARTITIONS IS TYPE T_PL_CO IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER; L_PL_CO T_PL_CO; TYPE T_PL_CD IS TABLE OF VARCHAR2(9) INDEX BY BINARY_INTEGER; L_PL_CD T_PL_CD; TYPE T_PL_NB IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; L_PL_NB T_PL_NB; TYPE T_SE_CD IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER; L_SE_CD T_SE_CD; TYPE T_BIR_DT IS TABLE OF DATE INDEX BY BINARY_INTEGER; L_BIR_DT T_BIR_DT; TYPE T_IS_AG IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER; L_IS_AG T_IS_AG; TYPE T_ISS_DT IS TABLE OF DATE INDEX BY BINARY_INTEGER; L_ISS_DT T_ISS_DT; TYPE T_ISS_ST IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER; L_ISS_ST T_ISS_ST; TYPE T_CUR_CODE IS TABLE OF VARCHAR2(7) INDEX BY BINARY_INTEGER; L_CUR_CODE T_CUR_CODE; TYPE T_SH_PL_CD IS TABLE OF VARCHAR2(7) INDEX BY BINARY_INTEGER; L_SH_PL_CD T_SH_PL_CD;
BEGIN
SELECT /*+ APPEND NOLOGGING PARALLEL(T 4)*/ DISTINCT a.PL_CO,a.PL_CD,a.PL_NB,a.SE_CD,a.BIR_DT, a.IS_AG,a.ISS_DT,a.ISS_ST,a.CUR_CODE,a.SH_PL_CD BULK COLLECT INTO L_PL_CO,L_PL_CD,L_PL_NB,L_SE_CD,L_BIR_DT,L_IS_AG, L_ISS_DT,L_ISS_ST,L_CUR_CODE,L_SH_PL_CD FROM (SELECT * FROM table2 WHERE BASE_RDR = 'B' AND PL_CO IN ('12','13','14')) a, (SELECT * FROM table1 WHERE PO_CM_CD IN ('12','13','14')) b where b.PO_CM_CD = a.PL_CO AND b.PL_NB = a.PL_NB AND b.CO_PL = a.PL_CD AND b.SE_CODE =a.SE_CD AND NVL(b.BIR_DT,'31DEC9999')=NVL(a.BIR_DT,'31DEC9999');
FORALL A IN L_PL_CO.FIRST..L_PL_CO.LAST UPDATE /*+ PARALLEL(T 4) */ table1 SET IS_AG = L_IS_AG(A), PO_IS_DT = L_ISS_DT(A), ISS_STATE = L_ISS_ST(A), BA_PL_CD = L_PL_CD(A), CUR_CODE = L_CUR_CODE(A), SH_PL_CD=L_SH_PL_CD(A) WHERE PO_CM_CD=L_PL_CO(A) AND PL_NB = L_PL_NB(A) AND CO_PL = L_PL_CD(A) AND SE_CODE = L_SE_CD(A) AND NVL(BIR_DT,'31DEC9999')=NVL(L_BIR_DT(A),'31DEC9999'); IF SQL%NOTFOUND THEN COMMIT; END IF; END; /
Please advice me the steps to tune the above procedure.
