-1

Oracle version 11.2.0.3.0

XML content is fetched as xmltype from CLOB field. It could be 1Mb or more.

Below is an example of XML Im processing using XmlTable

<Stmt>
  <Ntry>
    <Amt Ccy="BYN">DDD</Amt>
    <CdtDbtInd>DDD</CdtDbtInd>
    <Sts>
        <Prtry>DDD</Prtry>
    </Sts>
    <BookgDt>
        <DtTm>2021-09-20T11:14:40+03:00</DtTm>
    </BookgDt>
    <ValDt>
        <Dt>2021-09-20</Dt>
    </ValDt>
    <BkTxCd>
        <Prtry>
            <Cd>910</Cd>
        </Prtry>
    </BkTxCd>
    <AddtlInfInd>
        <MsgNmId>DDD</MsgNmId>
        <MsgId>050BISS202109200000000001537729</MsgId>
    </AddtlInfInd>
    <NtryDtls>
        <TxDtls>
            <RltdAgts>
                <DbtrAgt>
                    <FinInstnId>
                        <BICFI>DDDD</BICFI>
                    </FinInstnId>
                </DbtrAgt>
            </RltdAgts>
        </TxDtls>
    </NtryDtls>
  </Ntry>
</Stmt>

Amount of Ntry tag inside Stmt is 1500 but could vary.

Ntry tag itself is a structure, which could contain about 500 nested tags. Here represented just a simple version.

Below is an example of how I read XML data

procedure pParseNtry(
  pXmlType XmlType,

for i in (select *from xmltable('/Ntry'  PASSING pXmlType
                        COLUMNS
                        
                ...here declared 500 columns                
                
                )loop
  -- do something
end loop;
end;

If I use " select all from xmltable() " , then performance of this query is very poor, because I query all 500 cols declared in xmltable

If I reduce number of queried cols from 500 to 10, query runs fast.

I have no idea, why this could happen.

There's a clue that even if there are 500 cols described in xmltable structure, but select expression queries only one, DOMParser doesn't parse all 500 cols, but just one - and performance is perfect

Seems like an Oracle 11.2.0.3.0 version bug, because on 12c same code works just fast.

On 11th version it takes 30 minutes to process query

On 12th version it takes 4 minutes to process query

Specs of hardware and two databases is equal.

Any ideas ?

Lagi
  • 495
  • 4
  • 12
  • Please [edit] your question title to something that clearly describes the question you're asking or problem you're having. Your current title is simply a verbatim repetition of the tags. Your title should be clear and descriptive enough to have meaning to a future site user who is scanning through a list of search results trying to find a solution to a problem, and your current title is useless in that regard. Thanks. – Ken White Nov 15 '21 at 18:16

1 Answers1

0

It's an Oracle 11g bug. In Oracle 12c exactly same query with same amount of data runs just perfect

For now, in 11th version, solution is to split big number of cols into small chunks and avoid using nested XML structures in the same query.

Lagi
  • 495
  • 4
  • 12