I have two almost identical Oracle 18c (Windows Database Bundle Patch : 18.11.0.0.200714) where a query on a view performs more than 100 times faster on one than on the other.
The view uses inline views and another view in 4 places where both the inline views and the other view uses constructions like: table( xmlsequence(extract(xxx, '/yyy/zzz')) ) p
and: extractvalue(value(p),'aaa/bbb')
The execution plan for query on the best performing database contains lines with:
XPATH EVALUATION
whereas the execution plan on the other database contains lines with:
COLLECTION ITERATOR PICKLER FETCH XMLSEQUENCEFROMXMLTYPE
On both databases, explain plan has the folowing Note:
- Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
I am aware that extractvalue has been deprecated for a long time, but this is a third party system that I can't just change.
I tried running explain plan in sqlplus with "set XMLOptimizationCheck on".
In the trace file for the slow execution, the Unparsed Query contains lines with:
EXTRACTVALUE(SYS_MAKEXML( ...
and the last line is:
Reason: extractValue
In the trace file for the slow execution, the Unparsed Query contains lines with:
XPATHTABLE( ...
and the last line is:
Reason: xq_seq2con
I would just like to be able to find out why these two databases behave differently (and hopefully find a way to make the slow one perform as the other).
Regards
Johannes