This discussion is archived
12 Replies Latest reply: Dec 11, 2012 3:06 AM by odie_63 RSS

Extractvalue performance

Zabo Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    ExtractValue is being deprecated by Oracle.
    You should use XMLTABLE instead.
  • 2. Re: Extractvalue performance
    Zabo Newbie
    Currently Being Moderated
    Can you give me more information ? or give me an example ?
  • 3. Re: Extractvalue performance
    BluShadow Guru Moderator
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    [Edit: removed, BluShadow beat me to it.]

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

    I will try to go in that direction
  • 7. Re: Extractvalue performance
    Zabo Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks for your help.

    Now my process lasts 1,5hour : with GTT and XMLTABLE
  • 12. Re: Extractvalue performance
    odie_63 Guru
    Currently Being Moderated
    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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points