Forum Stats

  • 3,824,780 Users
  • 2,260,417 Discussions
  • 7,896,310 Comments

Discussions

XML query performance difference

jvdmir
jvdmir Member Posts: 2 Blue Ribbon

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

Answers

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,094 Silver Trophy

    To clarify, you are saying the exact same query ran on two different machines runs as different speeds?

    If yes, the second one is slow because it does more in memory (builds XML objects then scans them) vs filtering on disk first. The standard set of questions apply (which I don't know where they moved to when the forum was moved). Are stats current on both, is data volume the same, is the storage the same for all columns involved (especially if an XMLType column is involved), are the machines and underlying disk the same in terms of performance, etc?

    You could generate a trace of the SQL statement executing on both systems and compare to maybe get an idea as to what the optimizer see different between the two. Just some thoughts to get started.

  • jvdmir
    jvdmir Member Posts: 2 Blue Ribbon

    Thanks for the reply.

    Yes, the same query ran on two different (but practically identical) machines and databases with different execution plans and very different speed.

    I did run with trace an found that the slower on was using more time on "COLLECTION ITERATOR PICKLER FETCH XMLSEQUENCEFROMXMLTYPE" where the faster one was using "XPATH EVALUATION".

    I have now fund that there is a difference in the (XML related) column_expression of the function based indexes on the involved tables, and it is currently being investigated why this is (should have been created with the same scripts on both databases ...)

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,094 Silver Trophy

    I suspect that is the root cause of your issue. If it is, please just post a comment to confirm it was the function based indexes.