Home » RDBMS Server » Performance Tuning » statement with big cost (Oracle Database 11.2.0.2.0)
statement with big cost [message #610563] Fri, 21 March 2014 05:17 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #610565 is a reply to message #610564] Fri, 21 March 2014 05:36 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
The initial plan attached.
  • Attachment: plan.JPG
    (Size: 167.28KB, Downloaded 1190 times)
Re: statement with big cost [message #610566 is a reply to message #610564] Fri, 21 March 2014 05:37 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
And the plan using RULE hint.

Thanks.

l.e.: I'm using PL/SQL Developer from AllroundAutomations, as IDE.

Re: statement with big cost [message #610567 is a reply to message #610566] Fri, 21 March 2014 05:46 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #610581 is a reply to message #610578] Fri, 21 March 2014 09:05 Go to previous messageGo to next message
Roger22
Messages: 98
Registered: April 2009
Location: Brasov, ROMANIA
Member
Yes, the query executes instantly. I think that the CBO's estimate is wrong.
Re: statement with big cost [message #610583 is a reply to message #610581] Fri, 21 March 2014 09:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I think you are trying to solve a problem which only exists between your ears & we can't do anything about that.
Re: statement with big cost [message #610584 is a reply to message #610581] Fri, 21 March 2014 09:18 Go to previous message
cookiemonster
Messages: 13917
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

Previous Topic: session uga memory
Next Topic: Use of Hints in Oracle 11g to get desired Execution Plan
Goto Forum:
  


Current Time: Thu Mar 28 11:03:54 CDT 2024