Hello
I would like to know if its a possible to make a better performance over XML parse query ?
I have xml loaded in the table as BLOB (13MB).
When I run xml parse query, it takes a pretty long time to process (about 7seconds).
Number of parsing lines is 130 (see comments in the query).
This is my query:
select b.*, c.*
from ( SELECT XMLTYPE.createXML(blob_to_clob(CONTENT) ) as xml_data
FROM THE_TABLE a
) t,
--DalyData
xmltable(
xmlnamespaces(
default 'urn:iso:std:iso:20022:tech:xsd:head.003.001.01',
'urn:iso:std:iso:20022:tech:xsd:head.001.001.01' as "n",
'urn:iso:std:iso:20022:tech:xsd:auth.100.001.01' as "o"
),
'/BizData/Pyld/o:Document/o:SttlmFlsMnthlyRpt/o:DalyData'
passing xml_data
columns
--RptgDt
RptgDt varchar2(65) path './o:RptgDt'
) b,
--DalyData/ DalyRcrd/ Eqty/ SctiesBuyOrSell
xmltable(
xmlnamespaces(
default 'urn:iso:std:iso:20022:tech:xsd:head.003.001.01',
'urn:iso:std:iso:20022:tech:xsd:head.001.001.01' as "n",
'urn:iso:std:iso:20022:tech:xsd:auth.100.001.01' as "o"
),
-- '/BizData/Pyld/o:Document/o:SttlmFlsMnthlyRpt/o:DalyData/o:DalyRcrd/o:Eqty/o:Data/o:SctiesBuyOrSell/o:Data'
-- v_tag_name
'/BizData/Pyld/o:Document/o:SttlmFlsMnthlyRpt/o:DalyData/o:DalyRcrd/o:Eqty/o:Data/o:RpAgrmt/o:Data'
passing xml_data
columns
--IntraCSD --SctiesBuyOrSell/ IntraCSD / DlvryVrssPmt/ FaildScties
IntraCSD_DlvryVrssPmt_FaildScties_Sttld_Vol varchar2(65) path './o:IntraCSD/o:Data/o:DlvryVrssPmt/o:Data/o:FaildScties/o:Data/o:Sttld/o:Vol' ,
IntraCSD_DlvryVrssPmt_FaildScties_Sttld_Val varchar2(65) path './o:IntraCSD/o:Data/o:DlvryVrssPmt/o:Data/o:FaildScties/o:Data/o:Sttld/o:Val' ,
IntraCSD_DlvryVrssPmt_FaildScties_Faild_Vol varchar2(65) path './o:IntraCSD/o:Data/o:DlvryVrssPmt/o:Data/o:FaildScties/o:Data/o:Faild/o:Vol' ,
IntraCSD_DlvryVrssPmt_FaildScties_Faild_Val varchar2(65) path './o:IntraCSD/o:Data/o:DlvryVrssPmt/o:Data/o:FaildScties/o:Data/o:Faild/o:Val' ,
IntraCSD_DlvryVrssPmt_FaildScties_Ttl_Vol varchar2(65) path './o:IntraCSD/o:Data/o:DlvryVrssPmt/o:Data/o:FaildScties/o:Data/o:Ttl/o:Vol' ,
IntraCSD_DlvryVrssPmt_FaildScties_Ttl_Val varchar2(65) path './o:IntraCSD/o:Data/o:DlvryVrssPmt/o:Data/o:FaildScties/o:Data/o:Ttl/o:Val' ,
IntraCSD_DlvryVrssPmt_FaildScties_FaildRate_Vol varchar2(65) path './o:IntraCSD/o:Data/o:DlvryVrssPmt/o:Data/o:FaildScties/o:Data/o:FaildRate/o:Vol' ,
IntraCSD_DlvryVrssPmt_FaildScties_FaildRate_Val varchar2(65) path './o:IntraCSD/o:Data/o:DlvryVrssPmt/o:Data/o:FaildScties/o:Data/o:FaildRate/o:Val'
-- ... etc. number of parsing lines is 130
) c
This is how looks trace file information:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.28 0.30 0 506 0 0
Execute 1 28.79 30.20 36899 445595 2467980 400
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 29.07 30.51 36899 446101 2467980 400
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 162 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD AS SELECT WK_CSDR7_M_1_10 (cr=446342 pr=36899 pw=24 time=30328786 us starts=1)
400 400 400 OPTIMIZER STATISTICS GATHERING (cr=446331 pr=36897 pw=0 time=3026114 us starts=1 cost=227577 size=30489314368 card=66716224)
400 400 400 NESTED LOOPS (cr=445571 pr=36897 pw=0 time=2993324 us starts=1 cost=227577 size=30489314368 card=66716224)
20 20 20 NESTED LOOPS (cr=21311 pr=1757 pw=0 time=1304803 us starts=1 cost=56 size=1641768 card=8168)
1 1 1 TABLE ACCESS FULL ST_HI_CSDR_EXPORT (cr=98 pr=0 pw=0 time=362 us starts=1 cost=27 size=199 card=1)
20 20 20 XMLTABLE EVALUATION (cr=21213 pr=1757 pw=0 time=1304390 us starts=1)
400 400 400 XMLTABLE EVALUATION (cr=424260 pr=35140 pw=0 time=28856002 us starts=20)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 114 0.00 0.00
direct path read 504 0.00 0.08
db file sequential read 2 0.00 0.00
direct path write 2 0.00 0.00
direct path sync 1 0.00 0.00
Does anybody know how to make it better from point of performance ?
Thanks a lot
Martin
[Updated on: Sun, 12 March 2023 05:44]
Report message to a moderator