high cost

What’s wrong with this query ?


select
(t6.column_value).getstringval() t6
from
table(xmlsequence(extract(xmltype(‘<x/>’),’/x’))) t1,
table(xmlsequence(t1.column_value))t2,
table(xmlsequence(t2.column_value))t3,
table(xmlsequence(t3.column_value))t4,
table(xmlsequence(t4.column_value))t5,
table(xmlsequence(t5.column_value))t6;
T6
————————
<x/>

Elapsed: 00:00:00.01

Well, let’s check the plan :

--------------------------------------------------------------------
| Id | Operation | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | 18E| 15E| 18E (0)|999:59:59 |
| 1 | NESTED LOOPS | 18E| 15E| 18E (0)|999:59:59 |
| 2 | NESTED LOOPS | 18E| 15E| 99P (3)|999:59:59 |
| 3 | NESTED LOOPS | 4451T| 31P| 12T (3)|999:59:59 |
| 4 | NESTED LOOPS | 544G| 3045G| 1490M (3)|999:59:59 |
| 5 | NESTED LOOPS | 66M| 254M| 182K (3)| 00:36:31 |
| 6 | COLLECTION I | | | | |
| 7 | COLLECTION I | | | | |
| 8 | COLLECTION IT | | | | |
| 9 | COLLECTION ITE | | | | |
| 10 | COLLECTION ITER | | | | |
| 11 | COLLECTION ITERA | | | | |
--------------------------------------------------------------------

It is returning 18 quadrillions of rows, 15 exabytes, the cost is 1.8E19 and the time is about one month :mrgreen:

8 thoughts on “high cost

  1. Dominic Brooks

    The 18E is presumably an optimizer calculation limit imposed at some level. If you exclude t6 and stop at t5 then the cost is the same.
    The limit is reached before the full cost of just t4 can be calculated (based on the 8168 default).

  2. Dominic Brooks

    Sorry – I should have put this all in one comment.

    dom@11g>explain plan for
    2 select *
    3 from
    4 table(xmlsequence(extract(xmltype(”),’/x’))) t1
    5 ,table(xmlsequence(t1.column_value))t2
    6 ,table(xmlsequence(t2.column_value))t3
    7 ,table(xmlsequence(t3.column_value))t4
    8 –,table(xmlsequence(t4.column_value))t5
    9 –,table(xmlsequence(t5.column_value))t6
    10 ;

    Explained.

    Elapsed: 00:00:00.00
    dom@11g>
    dom@11g>select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT
    —————————————————————————————————-

    ———————————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost |
    ———————————————————————————————–
    | 0 | SELECT STATEMENT | | 4451T| 31P| 11T|
    | 1 | NESTED LOOPS | | 4451T| 31P| 11T|
    | 2 | NESTED LOOPS | | 544G| 3045G| 1463M|
    | 3 | NESTED LOOPS | | 66M| 254M| 179K|
    | 4 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | | | |
    | 5 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | | | |
    | 6 | COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE | | | |
    | 7 | COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE | | | |
    ———————————————————————————————–

    Note
    —–
    – ‘PLAN_TABLE’ is old version

    17 rows selected.

    Elapsed: 00:00:00.01
    dom@11g>
    dom@11g>select 8168*8168*8168*8168/**8168*8168*/ from dual;

    8168*8168*8168*8168/**8168*8168*/
    ———————————
    4.4511E+15

    Elapsed: 00:00:00.00
    dom@11g>

    dom@11g>explain plan for
    2 select *
    3 from
    4 table(xmlsequence(extract(xmltype(”),’/x’))) t1
    5 ,table(xmlsequence(t1.column_value))t2
    6 ,table(xmlsequence(t2.column_value))t3
    7 ,table(xmlsequence(t3.column_value))t4
    8 ,table(xmlsequence(t4.column_value))t5
    9 –,table(xmlsequence(t5.column_value))t6
    10 ;

    Explained.

    Elapsed: 00:00:00.00
    dom@11g>
    dom@11g>select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT
    —————————————————————————————————-

    ————————————————————————————————
    | Id | Operation | Name | Rows | Bytes | Cost |
    ————————————————————————————————
    | 0 | SELECT STATEMENT | | 18E| 15E| 97P|
    | 1 | NESTED LOOPS | | 18E| 15E| 97P|
    | 2 | NESTED LOOPS | | 4451T| 31P| 11T|
    | 3 | NESTED LOOPS | | 544G| 3045G| 1463M|
    | 4 | NESTED LOOPS | | 66M| 254M| 179K|
    | 5 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | | | |
    | 6 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | | | |
    | 7 | COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE | | | |
    | 8 | COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE | | | |
    | 9 | COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE | | | |
    ————————————————————————————————

    Note
    —–
    – ‘PLAN_TABLE’ is old version

    19 rows selected.

    Elapsed: 00:00:00.01
    dom@11g>
    dom@11g>select 8168*8168*8168*8168*8168/**8168*/ from dual;

    8168*8168*8168*8168*8168/**8168*/
    ———————————
    3.6356E+19

    Elapsed: 00:00:00.00
    dom@11g>

  3. Laurent Schneider Post author

    I can get a cost 1e126 in 9i 🙂

    Note that if I do about 50 levels, it just ends with ORA-600…

    The best approach is probably to use XMLTABLE in 10gR2/11g, rather than encapsulating tables call.

  4. Marco Gralike

    A “COLLECTION ITERATOR PICKLER FETCH” is solely done in memory via a pickler fetch method regarding XML fragments (despite the fact they can be small 😉 ).

    Until I now I have for XML two variations XMLSEQUENCEFROMXMLTYPE and XQSEQUENCEFROMXMLTYPE.

    The first one is method table(xmlsequence(extract())) related, the second one for XQuery (so also XMLTable and all other XQuery statements)

  5. Greg

    Maybe you need to query each XML separately. I see no joins here so I am assuming you are getting a cartesian product of all 6 sets

Comments are closed.