My Oracle Support Banner

XMLTABLE Query Returning Unexpected Rows Since Applying 19.13.0.0.211019DBRU (Doc ID 2851371.1)

Last updated on APRIL 17, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 19.13.0.0.0 to 19.13.0.0.0 [Release 19]
Information in this document applies to any platform.

Symptoms

A XMLTABLE query is returning unexpected number of rows after applying 19.13.0.0.211019 DBRU. The query that was returning ONE row in 19.12, before applying the patch, is now returning THREE rows in 19.13.

The Execution Plan in 19.12 shows:

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
         1          1          1 PX COORDINATOR (cr=20 pr=0 pw=0 time=31550 us starts=1)
         0          0          0  PX SEND QC (RANDOM) :TQ10002 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=21 size=100419 card=179)
         0          0          0  HASH JOIN OUTER BUFFERED (cr=0 pr=0 pw=0 time=0 us starts=0 cost=21 size=100419 card=179)
<snip>
         0          0          0        PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us starts=0)
         0          0          0         TABLE ACCESS STORAGE FULL SOME_TABLE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=3 size=140 card=1)
         0          0          0        XPATH EVALUATION (cr=0 pr=0 pw=0 time=0 us starts=0)
         0          0          0       NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us starts=0 cost=6 size=12460 card=89)
         0          0          0        PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us starts=0)
         0          0          0         TABLE ACCESS STORAGE FULL SOME_TABLE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=3 size=140 card=1)
         0          0          0        XPATH EVALUATION (cr=0 pr=0 pw=0 time=0 us starts=0)

While the new execution plan in 19.13 shows:

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
         3          3          3 PX COORDINATOR (cr=20 pr=0 pw=0 time=42255 us starts=1)
         0          0          0  PX SEND QC (RANDOM) :TQ10002 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=21 size=9165057 card=16337)
         0          0          0   HASH JOIN OUTER BUFFERED (cr=0 pr=0 pw=0 time=0 us starts=0 cost=21 size=9165057 card=16337)
<snip>
         0          0          0         PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us starts=0)
         0          0          0          TABLE ACCESS STORAGE FULL SOME_TABLE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=3 size=140 card=1)
         0          0          0         VIEW (cr=0 pr=0 pw=0 time=0 us starts=0)
         0          0          0          FILTER (cr=0 pr=0 pw=0 time=0 us starts=0)
         0          0          0           COLLECTION ITERATOR PICKLER FETCH XMLSEQUENCEFROMXMLTYPE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=0 card=8168)
         0          0          0        NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us starts=0 cost=6 size=1143520 card=8168)
         0          0          0         PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us starts=0)
         0          0          0          TABLE ACCESS STORAGE FULL SOME_TABLE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=3 size=140 card=1)
         0          0          0         VIEW (cr=0 pr=0 pw=0 time=0 us starts=0)
         0          0          0          FILTER (cr=0 pr=0 pw=0 time=0 us starts=0)
         0          0          0           COLLECTION ITERATOR PICKLER FETCH XMLSEQUENCEFROMXMLTYPE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=0 card=81

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.