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:

Published by

Laurent Schneider

Oracle Certified Master

8 thoughts on “high cost”

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

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>