high cost
January 9th, 2009
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 
Presumably all stemming from a default assumption of 8168 rows per collection…
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).
Sorry, my mistake - t4 is fully calculated. It’s mid T5 that the limit is reached.
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>
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.
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)
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
Greg, the join is actually done by using t1.column_value in t2 and so on