12 Replies Latest reply: Dec 11, 2012 5:06 AM by odie_63 RSS

    Extractvalue performance

    Zabo
      Hello,

      I'm using 11gR2.

      Here is an example of what I'm doing.

      I have data stored in XML and I extract values into a PL/SQL user type structure (here Order_Typ ).

      My_Xml can have 500 items.

      I call Extract_Order_Typ function 50.000 times among my process.

      Under enterprise manager, I see that EXTRACTVALUE is taking 25% of the CPU used athough It is not the main thing that I'm doing.

      What should I do to optimize Extract_Order_Typ function ?

      Thanks


      CREATE OR REPLACE package Xml_2_Typ
      is

      My_Xml xmltype:=
      xmltype('<root>
      <gross_value>325</gross_value>
      <net_value>370</net_value>
      <tax_value>45</tax_value>
      <item_list>
      <item>
      <description>books</description>
      <price>10</price>
      <tax_percent>0.1</tax_percent>
      <quantity>20</quantity>
      <gross_value>200</gross_value>
      <net_value>220</net_value>
      <tax_value>20</tax_value>
      </item>
      <item>
      <description>pencils</description>
      <price>1.25</price>
      <tax_percent>0.2</tax_percent>
      <quantity>100</quantity>
      <gross_value>125</gross_value>
      <net_value>150</net_value>
      <tax_value>25</tax_value>
      </item>
      </item_list>
      </root>');

      type Item_Typ is record
      (description varchar2(100),
      price number(10,2),
      tax_percent number(8,7),
      quantity number(10),
      gross_value number(10,2),
      net_value number(10,2),
      tax_value number(10,2));

      Type Item_List_Typ is table of Item_Typ;

      Type Order_Typ is record
      (gross_value number(10,2),
      net_value number(10,2),
      tax_value number(10,2),
      item_list Item_List_Typ);

      function Extract_Order_Typ(I_XML IN XMLTYPE) return Order_Typ;

      procedure Test_It;

      end;
      /

      CREATE OR REPLACE package body Xml_2_Typ
      is
      function Extract_Item_Typ(I_XML IN XMLTYPE)
      return Item_Typ
      is
      v_rst Item_Typ;
      v_xml xmltype;
      begin

      select extractvalue(I_XML,'/*/description'),
      extractvalue(I_XML,'/*/prices'),
      extractvalue(I_XML,'/*/tax_percent'),
      extractvalue(I_XML,'/*/quantity'),
      extractvalue(I_XML,'/*/gross_value'),
      extractvalue(I_XML,'/*/net_value'),
      extractvalue(I_XML,'/*/tax_value')
      into v_rst.description,
      v_rst.price,
      v_rst.tax_percent,
      v_rst.quantity,
      v_rst.gross_value,
      v_rst.net_value,
      v_rst.tax_value
      from dual;

      return v_rst;
      end;

      function Extract_Item_List_Typ(I_XML IN XMLTYPE)
      return Item_List_Typ
      is
      v_rst Item_List_Typ := Item_List_Typ();
      type xml_tab_typ is table of xmltype;
      v_data xml_tab_typ := xml_tab_typ();
      begin

      select value(a) as val
      bulk collect
      into v_data
      from table (xmlsequence(extract(I_XML,'/*/item'))) a;

      if v_data is not null and v_data.count>0 then
      for i in v_data.first..v_data.last loop
      v_rst.extend;
      v_rst(v_rst.last) := Extract_Item_Typ(v_data(i));
      end loop;
      end if;

      return v_rst;
      end;

      function Extract_Order_Typ(I_XML IN XMLTYPE)
      return Order_Typ
      is
      v_rst Order_Typ;
      v_xml xmltype;
      begin
      select extractvalue(I_XML,'/*/gross_value'),
      extractvalue(I_XML,'/*/net_value'),
      extractvalue(I_XML,'/*/tax_value')
      into v_rst.gross_value,
      v_rst.net_value,
      v_rst.tax_value
      from dual;

      select extract(I_XML,'/*/item_list') into v_xml from dual;


      v_rst.item_list := Extract_Item_List_Typ(v_xml);

      return v_rst;
      end;

      procedure Test_It
      is
      v_rst Order_Typ;
      begin
      v_rst := Extract_Order_Typ(My_Xml);
      end;

      end;
      /
        • 1. Re: Extractvalue performance
          BluShadow
          ExtractValue is being deprecated by Oracle.
          You should use XMLTABLE instead.
          • 2. Re: Extractvalue performance
            Zabo
            Can you give me more information ? or give me an example ?
            • 3. Re: Extractvalue performance
              BluShadow
              Zabo wrote:
              Can you give me more information ? or give me an example ?
              Example based on your data...
              SQL> ed
              Wrote file afiedt.buf
              
                1  with t as (select xmltype('<root>
                2    <gross_value>325</gross_value>
                3    <net_value>370</net_value>
                4    <tax_value>45</tax_value>
                5    <item_list>
                6      <item>
                7        <description>books</description>
                8        <price>10</price>
                9        <tax_percent>0.1</tax_percent>
               10        <quantity>20</quantity>
               11        <gross_value>200</gross_value>
               12        <net_value>220</net_value>
               13        <tax_value>20</tax_value>
               14      </item>
               15      <item>
               16        <description>pencils</description>
               17        <price>1.25</price>
               18        <tax_percent>0.2</tax_percent>
               19        <quantity>100</quantity>
               20        <gross_value>125</gross_value>
               21        <net_value>150</net_value>
               22        <tax_value>25</tax_value>
               23      </item>
               24    </item_list>
               25  </root>') as xml from dual)
               26  --
               27  -- end of test data
               28  --
               29  select x.gross_value, x.net_value, x.tax_value
               30        ,y.*
               31  from   t
               32        ,xmltable('/root'
               33                  passing t.xml
               34                  columns gross_value number  path './gross_value'
               35                         ,net_value   number  path './net_value'
               36                         ,tax_value   number  path './tax_value'
               37                         ,items       xmltype path './item_list'
               38                 ) x
               39        ,xmltable('/item_list/item'
               40                  passing x.items
               41                  columns description      varchar2(10) path './description'
               42                         ,price            number       path './price'
               43                         ,tax_percent      number       path './tax_percent'
               44                         ,quantity         number       path './quantity'
               45                         ,item_gross_value number       path './gross_value'
               46                         ,item_net_value   number       path './net_value'
               47                         ,item_tax_value   number       path './tax_value'
               48*                ) y
              SQL> /
              
              GROSS_VALUE  NET_VALUE  TAX_VALUE DESCRIPTIO      PRICE TAX_PERCENT   QUANTITY ITEM_GROSS_VALUE ITEM_NET_VALUE ITEM_TAX_VALUE
              ----------- ---------- ---------- ---------- ---------- ----------- ---------- ---------------- -------------- --------------
                      325        370         45 books              10          .1         20              200            220             20
                      325        370         45 pencils          1.25          .2        100              125            150             25
              
              SQL>
              • 4. Re: Extractvalue performance
                Stew Ashton
                [Edit: removed, BluShadow beat me to it.]

                Edited by: Stew Ashton on Dec 7, 2012 2:08 PM
                • 5. Re: Extractvalue performance
                  BluShadow
                  Stew Ashton wrote:
                  [Edit: removed, BluShadow beat me to it.]
                  Only just by the looks of it. :D
                  • 6. Re: Extractvalue performance
                    Zabo
                    Ok Thanks

                    I will try to go in that direction
                    • 7. Re: Extractvalue performance
                      Zabo
                      I tried this new functionnality : XMLTABLE but I got bad performance.

                      With EXTRACTVALUE, my process lasts 2hours
                      With XMLTABLE, my process lasts 2,5 hours.

                      Any help ?
                      • 8. Re: Extractvalue performance
                        odie_63
                        The key to performance with XML DB is the storage method.

                        Since you're on 11.2, I suggest you use an intermediate XMLType table with Binary XML storage :
                        CREATE TABLE tmp_xml OF XMLType
                        XMLType STORE AS SECUREFILE BINARY XML;
                        Load your document in that table and perform all queries from there.
                        With only a few hundreds of logical records in the input XML document, the query should be executed in only a few seconds.


                        Example with 5060 "records" :
                        SQL> set timing on
                        SQL> set lines 200
                        SQL> set autotrace traceonly
                        SQL> select x.gross_value, x.net_value, x.tax_value
                          2       , y.*
                          3  from tmp_xml t
                          4     , xmltable('/root'
                          5         passing t.object_value
                          6         columns gross_value number  path 'gross_value'
                          7               , net_value   number  path 'net_value'
                          8               , tax_value   number  path 'tax_value'
                          9               , items       xmltype path 'item_list'
                         10       ) x
                         11     , xmltable('/item_list/item'
                         12         passing x.items
                         13         columns description      varchar2(10) path 'description'
                         14               , price            number       path 'price'
                         15               , tax_percent      number       path 'tax_percent'
                         16               , quantity         number       path 'quantity'
                         17               , item_gross_value number       path 'gross_value'
                         18               , item_net_value   number       path 'net_value'
                         19               , item_tax_value   number       path 'tax_value'
                         20       ) y
                         21  ;
                        
                        5060 rows selected.
                        
                        Elapsed: 00:00:06.32
                        
                        Execution Plan
                        ----------------------------------------------------------
                        Plan hash value: 2982218919
                        
                        -------------------------------------------------------------------------------
                        | Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
                        -------------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT    |         |    66M|   125G|   222K  (1)| 00:44:26 |
                        |   1 |  NESTED LOOPS       |         |    66M|   125G|   222K  (1)| 00:44:26 |
                        |   2 |   NESTED LOOPS      |         |  8168 |    15M|    32   (0)| 00:00:01 |
                        |   3 |    TABLE ACCESS FULL| TMP_XML |     1 |  2002 |     3   (0)| 00:00:01 |
                        |   4 |    XPATH EVALUATION |         |       |       |            |          |
                        |   5 |   XPATH EVALUATION  |         |       |       |            |          |
                        -------------------------------------------------------------------------------
                        
                        Note
                        -----
                           - dynamic sampling used for this statement (level=2)
                        
                        
                        Statistics
                        ----------------------------------------------------------
                                  0  recursive calls
                                  0  db block gets
                               5078  consistent gets
                              35616  physical reads
                                  0  redo size
                             210829  bytes sent via SQL*Net to client
                               4126  bytes received via SQL*Net from client
                                339  SQL*Net roundtrips to/from client
                                  0  sorts (memory)
                                  0  sorts (disk)
                               5060  rows processed
                        Edited by: odie_63 on 10 déc. 2012 13:56 - added example
                        • 9. Re: Extractvalue performance
                          Zabo
                          In my process, I first loaded my XML in an XMLTYPE variable and then I parsed it.

                          Do you think I would get better performance loading directly from table to my datatype variable ?
                          • 10. Re: Extractvalue performance
                            odie_63
                            You will get better performance using a persistent storage (Binary XMLType table - could be a GTT if you want).

                            If you use a transient XMLType instance (PL/SQL variable), Oracle won't be able to use the optimized streaming evaluation.
                            • 11. Re: Extractvalue performance
                              Zabo
                              Thanks for your help.

                              Now my process lasts 1,5hour : with GTT and XMLTABLE
                              • 12. Re: Extractvalue performance
                                odie_63
                                1.5 hour is still tremendously and abnormally slow if - as you said - there are only 500 items to parse.

                                What kind of other actions are performed apart from parsing the data?