| statement with big cost [message #610563] |
Fri, 21 March 2014 05:17  |
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
|
Member |
|
|
Hello,
I have a sql statement which parses an XML and shows the result in tabular format. The problem is that the cost is enormous, and I don't understand why. The execution time is 1-2-3 seconds. In the explain plan, I can see FAST DUAL and some NESTED LOOPS, and COLLECTION ITERATOR PICKLER FETCH. Can this cost be a performance problem, or does CBO not estimate the cost correctly?
If I use the /*RULE*/ hint, the cost is reduced.
Any ideas?
Here is the SQL:
with sample_data as (
select xmltype('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Body>
<cdm:RoutingService_Response xmlns:cdm="http://www.accenture.com/RTc/OI/cdm">
<cdm:OutputData>
<cdm:OrderAttributes>
<cdm:List>
<cdm:name>SERVICES</cdm:name>
<!-- structura listei este valabila pentru serviciile : DSL, VOIP, IPTV -->
<cdm:List>
<cdm:name>SERVICE</cdm:name>
<cdm:value>DSL</cdm:value>
<cdm:List>
<cdm:name>TECHNOLOGY</cdm:name>
<cdm:value>ADSL</cdm:value>
<cdm:Attribute>
<cdm:name>PORTS</cdm:name>
<cdm:value>20</cdm:value>
</cdm:Attribute>
<cdm:Attribute>
<cdm:name>BANDWIDTH</cdm:name>
<cdm:value>40</cdm:value>
</cdm:Attribute>
</cdm:List>
<cdm:List>
<cdm:name>TECHNOLOGY</cdm:name>
<cdm:value>VDSL</cdm:value>
<cdm:Attribute>
<cdm:name>PORTS</cdm:name>
<cdm:value>40</cdm:value>
</cdm:Attribute>
<cdm:Attribute>
<cdm:name>BANDWIDTH</cdm:name>
<cdm:value>60</cdm:value>
</cdm:Attribute>
</cdm:List>
</cdm:List>
<cdm:List>
<cdm:name>SERVICE</cdm:name>
<cdm:value>IPTV</cdm:value>
<cdm:List>
<cdm:name>TECHNOLOGY</cdm:name>
<cdm:value>IPTV_TECHN1</cdm:value>
<cdm:Attribute>
<cdm:name>PORTS</cdm:name>
<cdm:value>25</cdm:value>
</cdm:Attribute>
<cdm:Attribute>
<cdm:name>BANDWIDTH</cdm:name>
<cdm:value>35</cdm:value>
</cdm:Attribute>
</cdm:List>
<cdm:List>
<cdm:name>TECHNOLOGY</cdm:name>
<cdm:value>IPTV_TECHN2</cdm:value>
<cdm:Attribute>
<cdm:name>PORTS</cdm:name>
<cdm:value>45</cdm:value>
</cdm:Attribute>
<cdm:Attribute>
<cdm:name>BANDWIDTH</cdm:name>
<cdm:value>66</cdm:value>
</cdm:Attribute>
</cdm:List>
</cdm:List>
<cdm:List>
<cdm:name>SERVICE</cdm:name>
<cdm:value>VOIP</cdm:value>
<cdm:List>
<cdm:name>TECHNOLOGY</cdm:name>
<cdm:value>FTTH</cdm:value>
</cdm:List>
</cdm:List>
<!-- structura listei este valabila pentru TDM -->
<cdm:List>
<cdm:name>SERVICE</cdm:name>
<cdm:value>TDM</cdm:value>
<cdm:Attribute>
<cdm:name>FREE_PORTS</cdm:name>
<cdm:value>20</cdm:value>
</cdm:Attribute>
</cdm:List>
<!-- structura listei este valabila pentru CABLE_TV -->
<cdm:List>
<cdm:name>SERVICE</cdm:name>
<cdm:value>CABLE_TV</cdm:value>
<cdm:List>
<cdm:name>TECHNOLOGY</cdm:name>
<cdm:value>CATV_FTTH</cdm:value>
<cdm:Attribute>
<cdm:name>SERVICE_READY</cdm:name>
<cdm:value>YES</cdm:value>
</cdm:Attribute>
</cdm:List>
</cdm:List>
</cdm:List>
</cdm:OrderAttributes>
<cdm:Error>
<cdm:code></cdm:code>
<cdm:details></cdm:details>
</cdm:Error>
</cdm:OutputData>
</cdm:RoutingService_Response>
</soapenv:Body>
</soapenv:Envelope>') xmldoc
from dual
)
select x1.root_name,
x2.list_name,
x2.list_value,
x3.sublist_name,
x3.sublist_value,
nvl(x4.attribute_name,x5.attribute_name) attribute_name,
nvl(x4.attribute_value,x5.attribute_value) attribute_value
from sample_data t,
xmltable(
xmlnamespaces(
'http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv",
default 'http://www.accenture.com/RTc/OI/cdm'
),
'/soapenv:Envelope/soapenv:Body/RoutingService_Response/OutputData/OrderAttributes/List'
passing t.xmldoc
columns
root_name varchar2(30) path 'name',
root_value varchar2(30) path 'value',
lists xmltype path 'List'
) x1,
xmltable(
xmlnamespaces(
'http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv",
default 'http://www.accenture.com/RTc/OI/cdm'
),
'List'
passing x1.lists
columns
list_name varchar2(30) path 'name',
list_value varchar2(30) path 'value',
lists xmltype path 'List',
attributes xmltype path 'Attribute'
)(+) x2,
xmltable(
xmlnamespaces(
'http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv",
default 'http://www.accenture.com/RTc/OI/cdm'
),
'List'
passing x2.lists
columns
sublist_name varchar2(30) path 'name',
sublist_value varchar2(30) path 'value',
lists xmltype path 'List',
attributes xmltype path 'Attribute'
)(+) x3,
xmltable(
xmlnamespaces(
'http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv",
default 'http://www.accenture.com/RTc/OI/cdm'
),
'Attribute'
passing x2.attributes
columns
attribute_name varchar2(30) path 'name',
attribute_value varchar2(30) path 'value'
)(+) x4,
xmltable(
xmlnamespaces(
'http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv",
default 'http://www.accenture.com/RTc/OI/cdm'
),
'Attribute'
passing x3.attributes
columns
attribute_name varchar2(30) path 'name',
attribute_value varchar2(30) path 'value'
)(+) x5
order by list_value
Thank you!
|
|
|
|
| Re: statement with big cost [message #610564 is a reply to message #610563] |
Fri, 21 March 2014 05:30  |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
If you use RULE the cost should disappear entirely as the RBO doesn't have that concept.
Doesn't make it faster though.
I suggest you post the explain plan.
|
|
|
|
|
|
|
|
| Re: statement with big cost [message #610567 is a reply to message #610566] |
Fri, 21 March 2014 05:46 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Use sqlplus and the following method to generate explain plans - not every will download images:
SQL> explain plan for select * from dual;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
SQL>
|
|
|
|
| Re: statement with big cost [message #610573 is a reply to message #610567] |
Fri, 21 March 2014 07:08 |
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
|
Member |
|
|
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
----------------------
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32P| 6821P| | 311T (3)|
| 1 | SORT ORDER BY | | 32P| 6821P| 7275P| 311T (3)|
| 2 | NESTED LOOPS OUTER | | 32P| 6821P| | 305T (1)|
| 3 | NESTED LOOPS OUTER | | 16T| 2997T| | 152G (1)|
| 4 | NESTED LOOPS OUTER | | 8000M| 1281G| | 76M (1)|
| 5 | NESTED LOOPS OUTER | | 4000K| 396M| | 38178 (1)|
| 6 | NESTED LOOPS | | 2000 | 4000 | | 21 (0)|
| 7 | FAST DUAL | | 1 | | | 2 (0)|
| 8 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | 2000 | 4000 | | 19 (0)|
| 9 | VIEW | | 2000 | 199K| | 19 (0)|
| 10 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | 2000 | 4000 | | 19 (0)|
| 11 | VIEW | | 2000 | 132K| | 19 (0)|
| 12 | COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE | 2000 | 4000 | | 19 (0)|
| 13 | VIEW | | 2000 | 68000 | | 19 (0)|
| 14 | COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE | 2000 | 4000 | | 19 (0)|
| 15 | VIEW | | 2000 | 68000 | | 19 (0)|
| 16 | COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE | 2000 | 4000 | | 19 (0)|
---------------------------------------------------------------------------------------------------------------
Ok.. but you can click on my uploads above, it's more clear there, in that execution plan. No need to download anything, it just pops up an image with the screenshot of execution plan ..
Thanks.
|
|
|
|
| Re: statement with big cost [message #610578 is a reply to message #610573] |
Fri, 21 March 2014 08:46 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I don't find the images any easier to read and as I already stated not everyone can/will bring up the images (you would be amazed what firewalls will restrict).
Did I read your first post correctly that the actual execution time is actually 1-3 seconds?
|
|
|
|
|
|
|
|
| Re: statement with big cost [message #610584 is a reply to message #610581] |
Fri, 21 March 2014 09:18 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I'm certain it is. Looks suspiciously like it's completely ignored the implications of the passing clause for working out the rows. I assume your query doesn't return anything in the same sport (never mind ballpark) as 32,000,000,000,000,000 rows.
I wouldn't worry about it. I'm pretty sure that the way the xml functions are implemented means that the optimizer doesn't really have any options when it comes to what plan to use, so the fact that it thinks far more work is involved than is actually the case isn't going to affect the outcome.
I would imagine Oracle will fix the estimates at some point, they might have done so already in 12c.
[Updated on: Fri, 21 March 2014 09:18] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
