Oracle XmlTable performance

Question

Oracle version 11.2.0.3.0

XML内容作为xmltype从CLOB字段获取。可以是1Mb或更大

下面是使用XmlTable

处理XML Im的示例

<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>

Stmt内的Ntry标签数量为1500，但可能会有所不同。

Ntry标签本身是一个结构，它可以包含大约500个嵌套的标签。这里只代表一个简单的版本。

下面是一个如何读取XML数据的例子

procedure pParseNtry(
pXmlType XmlType,
for i in (select *from xmltable('/Ntry'  PASSING pXmlType
COLUMNS

...here declared 500 columns                

)loop
-- do something
end loop;
end;

如果我使用"Select all from xmltable() "，则此查询的性能非常差，因为我查询了xmltable

中声明的所有500个cols。如果我将查询的颜色数量从500减少到10，查询运行得很快。

我不知道为什么会发生这种事。

有一个线索，即使在xmltable结构中描述了500个颜色，但select表达式只查询一个，DOMParser也不会解析所有500个颜色，而只解析一个—并且性能是完美的

似乎是Oracle 11.2.0.3.0版本的错误，因为在12c上同样的代码工作得很快。

第11个版本需要30分钟来处理查询

在第12版中，处理查询需要4分钟

硬件规格和两个数据库规格相等。

有什么想法吗?

Answer 1

这是Oracle 11g的bug。在Oracle 12c中，具有相同数据量的完全相同的查询运行完美

目前，在第11个版本中，解决方案是将大量的颜色分割成小块，并避免在同一查询中使用嵌套的XML结构。