Hi,
I have huge XML file that is needed to be imported into 5 tables in one session. Our Procedure is quoted below.
In Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production, following procedure executed in 2 minutes. However, Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production gives poor performance and never gets imported. The declarations of tables are exactly same in both platforms. I tried to get explain plan for the sql however it doesnt give me proper explain plan (or maybe I am making mistake). I quoted my explain plan below.
can you please support me for tracing this performance issue to solve it?
thank you.
create or replace PACKAGE BODY PKG_RISK AS
PROCEDURE BATCH_GB_IMPORT (P_SRC_RISK IN XMLTYPE) AS
W_GB T_GB;
W_GB_GEB T_GB_GEB;
W_GB_GEB_MSD T_GB_GEB_MSD;
W_GB_GEB_RIS T_GB_GEB_RIS;
CURSOR C_GB IS
SELECT OBJ_GB(SYS_GUID(), xt.KurumKodu, xt.kurumAdi, xt.geribildirimDonemi, xt.riskSahibiSayisi, xt.geribildirimSayisi, xt.geb)
--BULK COLLECT INTO W_GB
FROM XMLTABLE('/geribildirimDosyasi'
PASSING P_SRC_RISK --, NLS_CHARSET_ID('UTF8')) --x.c1
COLUMNS
kurumKodu VARCHAR2(3) PATH 'dosyaBaslik/kurumKodu',
kurumAdi VARCHAR2(30) PATH 'dosyaBaslik/kurumAdi',
geribildirimDonemi NUMBER PATH 'dosyaBaslik/geribildirimDonemi',
riskSahibiSayisi NUMBER PATH 'dosyaBaslik/riskSahibiSayisi',
geribildirimSayisi NUMBER path 'dosyaBaslik/geribildirimSayisi',
GEB XMLTYPE PATH 'riskGeribildirimleri/geb'
) xt;
--where sira = 1;
CURSOR C_GB_GEB IS
--SELECT OBJ_GB_GEB(SYS_GUID(), X.G_ID, SYS_GUID(), xt.MSN, XT.CKS, XT.SUB, XT.MSD, XT.RIS)
SELECT SYS_GUID() GB_GEB_ID, X.G_ID GB_ID, NULL GB_GEB_MSD_ID, xt.MSN, XT.CKS, XT.SUB, XT.RIS,
XT.MSD_ULK, XT.MSD_STU, XT.MSD_SCN, XT.MSD_ADI, XT.MSD_SYD
--BULK COLLECT INTO W_GB_GEB
FROM TABLE(W_GB) x,
XMLTABLE('/geb'
PASSING x.geb --x.c1
COLUMNS
MSN NUMBER PATH 'msn',
CKS NUMBER PATH 'cks',
SUB VARCHAR2(70) PATH 'sub',
--MSD XMLTYPE PATH 'msd',
RIS XMLTYPE PATH 'ris',
MSD_ULK VARCHAR2(3) PATH 'msd/ulk',
MSD_STU VARCHAR2(2) PATH 'msd/stu',
MSD_SCN VARCHAR2(15) PATH 'msd/scn',
MSD_ADI VARCHAR2(150) PATH 'msd/adi',
MSD_SYD VARCHAR2(150) PATH 'msd/syd'
) xt;
CURSOR C_GB_GEB_RIS IS
SELECT OBJ_GB_GEB_RIS(SYS_GUID(), X.GB_GEB_ID, xt.GSN, XT.MUT, XT.FNS, XT.RSK, XT.LIM, XT.RV1, XT.RV2, XT.RV3, XT.FRS, XT.FTH, XT.BRS, XT.KFS)
--SELECT SYS_GUID() GB_GEB_RIS_ID, X.GB_GEB_ID, xt.GSN, XT.MUT, XT.FNS, XT.RSK, XT.LIM, XT.RV1, XT.RV2, XT.RV3, XT.FRS, XT.FTH, XT.BRS, XT.KFS
--BULK COLLECT INTO W_GB_GEB_RIS
FROM TABLE(W_GB_GEB) x,
XMLTABLE('/ris'
PASSING X.RIS
COLUMNS
--header_no for ORDINALITY,
GSN NUMBER PATH 'gsn',
MUT VARCHAR2(1) PATH 'mut',
FNS VARCHAR2(5) PATH 'fns',
RSK NUMBER PATH 'rsk',
LIM NUMBER(17,2) PATH 'lim',
RV1 NUMBER(17,2) PATH 'rv1',
RV2 NUMBER(17,2) PATH 'rv2',
RV3 NUMBER(17,2) PATH 'rv3',
FRS NUMBER(17,2) PATH 'frs',
FTH NUMBER PATH 'fth',
BRS NUMBER PATH 'brs',
KFS NUMBER PATH 'kfs'
) xt;
V_MSD_ID RAW(16);
BEGIN
--DELETE FROM TMP_XML;
--INSERT INTO TMP_XML VALUES (SYS_GUID(), P_SRC_RISK );
--RETURN;
OPEN C_GB;
FETCH C_GB BULK COLLECT INTO W_GB;
CLOSE C_GB;
W_GB_GEB := T_GB_GEB();
W_GB_GEB_MSD := T_GB_GEB_MSD();
FOR GEB IN C_GB_GEB
LOOP
V_MSD_ID := NULL;
BEGIN
SELECT GGM_ID INTO V_MSD_ID
FROM GB_GEB_MSD
WHERE GGM_ULK = GEB.MSD_ULK AND
GGM_SCN = TRIM(LEADING '0' FROM GEB.MSD_SCN) AND
GGM_STU = GEB.MSD_STU;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_MSD_ID := SYS_GUID();
W_GB_GEB_MSD.EXTEND(1);
W_GB_GEB_MSD(W_GB_GEB_MSD.COUNT) := OBJ_GB_GEB_MSD(V_MSD_ID, GEB.MSD_ULK, GEB.MSD_STU, TRIM(LEADING '0' FROM GEB.MSD_SCN), GEB.MSD_ADI, GEB.MSD_SYD, FIND_CRM_BY_ID(GEB.MSD_SCN, GEB.MSD_ULK, GEB.MSD_STU));
WHEN TOO_MANY_ROWS THEN
RAISE;
END;
W_GB_GEB.EXTEND(1);
W_GB_GEB(W_GB_GEB.COUNT) := OBJ_GB_GEB(GEB.GB_GEB_ID, GEB.GB_ID, V_MSD_ID, GEB.MSN, GEB.CKS, GEB.SUB, NULL, GEB.RIS);
END LOOP;
OPEN C_GB_GEB_RIS;
FETCH C_GB_GEB_RIS BULK COLLECT INTO W_GB_GEB_RIS;
CLOSE C_GB_GEB_RIS;
FORALL REC IN W_GB.FIRST..W_GB.LAST
INSERT INTO GB (G_ID, G_KURUM_KODU, G_KURUM_ADI, G_GERIBILDIRIM_DONEMI, G_RISK_SAHIBI_SAYISI, G_GERIBILDIRIM_SAYISI)
VALUES (W_GB(REC).G_ID, W_GB(REC).kurumKodu, W_GB(REC).KurumAdi, W_GB(REC).GeribildirimDonemi, W_GB(REC).riskSahibiSayisi, W_GB(REC).geribildirimSayisi);
FORALL REC IN W_GB_GEB_MSD.FIRST..W_GB_GEB_MSD.LAST
INSERT INTO GB_GEB_MSD (GGM_ID, GGM_ADI, GGM_SCN, GGM_STU, GGM_SYD, GGM_ULK)
VALUES (W_GB_GEB_MSD(REC).GB_GEB_MSD_ID, W_GB_GEB_MSD(REC).Adi, W_GB_GEB_MSD(REC).Scn, W_GB_GEB_MSD(REC).Stu, W_GB_GEB_MSD(REC).Syd, W_GB_GEB_MSD(REC).ulk);
FORALL REC IN W_GB_GEB.FIRST..W_GB_GEB.LAST
INSERT INTO GB_GEB (GG_ID, GG_CKS, GG_GB_ID, GG_MSD_ID, GG_MSN, GG_SUB)
VALUES (W_GB_GEB(REC).GB_GEB_ID, W_GB_GEB(REC).CKS, W_GB_GEB(REC).GB_ID, W_GB_GEB(REC).GB_GEB_MSD_ID, W_GB_GEB(REC).MSN, W_GB_GEB(REC).SUB);
FORALL REC IN W_GB_GEB_RIS.FIRST..W_GB_GEB_RIS.LAST
INSERT INTO GB_GEB_RIS (GGR_ID, GGR_GB_GEB_ID, GGR_GSN, GGR_MUT, GGR_FNS, GGR_RSK, GGR_LIM, GGR_RV1, GGR_RV2, GGR_RV3, GGR_FRS, GGR_FTH, GGR_BRS, GGR_KFS)
VALUES (W_GB_GEB_RIS(REC).GB_GEB_RIS_ID, W_GB_GEB_RIS(REC).GB_GEB_ID, W_GB_GEB_RIS(REC).GSN, W_GB_GEB_RIS(REC).MUT, W_GB_GEB_RIS(REC).FNS,
W_GB_GEB_RIS(REC).RSK, W_GB_GEB_RIS(REC).LIM, W_GB_GEB_RIS(REC).RV1, W_GB_GEB_RIS(REC).RV2, W_GB_GEB_RIS(REC).RV3, W_GB_GEB_RIS(REC).FRS,
W_GB_GEB_RIS(REC).FTH, W_GB_GEB_RIS(REC).BRS, W_GB_GEB_RIS(REC).KFS);
END BATCH_GB_IMPORT;
FUNCTION FIND_CRM_BY_ID(P_ID_NUMBER VARCHAR2, P_COUNTRY_CODE VARCHAR2, P_ID_ENTITY_CODE_CB VARCHAR2) RETURN NUMBER IS
V_ID_ENTITY_CODE_CB VARCHAR2(2) := LPAD(P_ID_ENTITY_CODE_CB,2, '0');
V_ID_NUMBER VARCHAR(15) := TRIM(LEADING '0' FROM P_ID_NUMBER);
V_COUNTRY_CODE VARCHAR2(3) := P_COUNTRY_CODE;
V_CRM_NO NUMBER;
BEGIN
SELECT MAX(CB_REFNO) INTO V_CRM_NO FROM CSBINSPLUS.CONTACTS_BASE WHERE TRIM(LEADING '0' FROM CB_ID) = V_ID_NUMBER AND CB_ID_TYPE IN
(
SELECT ID_CODE FROM CSBINSPLUS.ID_TYPES WHERE ID_NATIONALITY_CODE = V_COUNTRY_CODE AND ID_ENTITY_CODE_CB = V_ID_ENTITY_CODE_CB
);
IF (V_CRM_NO IS NULL) THEN
SELECT MAX(CII_REFNO) INTO V_CRM_NO FROM CSBINSPLUS.CONTACTS_ID_INFO WHERE TRIM(LEADING '0' FROM CII_ID) = V_ID_NUMBER AND CII_ID_TYPE IN
(
SELECT ID_CODE FROM CSBINSPLUS.ID_TYPES WHERE ID_NATIONALITY_CODE = V_COUNTRY_CODE AND ID_ENTITY_CODE_CB = V_ID_ENTITY_CODE_CB
);
END IF;
IF (V_CRM_NO IS NULL) THEN
SELECT MAX(CII_REFNO) INTO V_CRM_NO FROM CSBINSPLUS.LOG_CONTACTS_ID_INFO WHERE TRIM(LEADING '0' FROM CII_ID) = V_ID_NUMBER AND CII_ID_TYPE IN
(
SELECT ID_CODE FROM CSBINSPLUS.ID_TYPES WHERE ID_NATIONALITY_CODE = V_COUNTRY_CODE AND ID_ENTITY_CODE_CB = V_ID_ENTITY_CODE_CB
);
END IF;
RETURN V_CRM_NO;
END;
END PKG_RISK;
SQL_ID bdn53gur30297
--------------------
SELECT SYS_GUID() GB_GEB_ID, X.G_ID GB_ID, NULL GB_GEB_MSD_ID, XT.MSN,
XT.CKS, XT.SUB, XT.RIS, XT.MSD_ULK, XT.MSD_STU, XT.MSD_SCN, XT.MSD_ADI,
XT.MSD_SYD FROM TABLE(:B1 ) X, XMLTABLE('/geb' PASSING X.GEB COLUMNS
MSN NUMBER PATH 'msn', CKS NUMBER PATH 'cks', SUB VARCHAR2(70) PATH
'sub', RIS XMLTYPE PATH 'ris', MSD_ULK VARCHAR2(3) PATH 'msd/ulk',
MSD_STU VARCHAR2(2) PATH 'msd/stu', MSD_SCN VARCHAR2(15) PATH
'msd/scn', MSD_ADI VARCHAR2(150) PATH 'msd/adi', MSD_SYD VARCHAR2(150)
PATH 'msd/syd' ) XT
Plan hash value: 1479800447
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 58 (100)| |
| 1 | NESTED LOOPS | | 8168 | 32672 | 58 (0)| 00:00:01 |
| 2 | COLLECTION ITERATOR PICKLER FETCH| | 1 | 2 | 29 (0)| 00:00:01 |
| 3 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | 8168 | 16336 | 29 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------