Home » SQL & PL/SQL » SQL & PL/SQL » XML parsing performance (19c)
XML parsing performance [message #687468] Sun, 12 March 2023 01:30
mape
Messages: 298
Registered: July 2006
Location: Slovakia
Senior Member
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

Previous Topic: Different UTC time from SYSDATE on different databases
Next Topic: Ternary Operator in PL/SQL
Goto Forum:
  


Current Time: Thu Mar 28 17:54:31 CDT 2024