This discussion is archived
7 Replies Latest reply: Nov 18, 2012 11:02 AM by Marco Gralike RSS

performance issue with EXTRACTVALUE function.

RamaKrishna.CH Explorer
Currently Being Moderated
Hi all,
I am getting the XML string length approximately 20 billion .I have to extract some values from that XML initially. But when i tried like below, it takes approximately 10 mins. So can any one help me to extract more faster.
My DB version:
  BANNER                                                         
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bi 
PL/SQL Release 10.1.0.5.0 - Production                           
CORE     10.1.0.5.0     Production                                         
TNS for HPUX: Version 10.1.0.5.0 - Production                    
NLSRTL Version 10.1.0.5.0 - Production
WITH t as ( select EXTRACT(XMLTYPE('<InputParameters><ClientName>XXX</ClientName><HpUserId>2500</HpUserId><UserName>ABC</UserName><UserPass></UserPass>
<ParamSet>
<alerttype>1000</alerttype>
<emailtext><![CDATA[test]]></emailtext>
<smstext><![CDATA[]]></smstext>
<pushtext><![CDATA[]]></pushtext>
<voicetext><![CDATA[]]></voicetext>
<emailmsgsub><![CDATA[test]]></emailmsgsub>
<smsmsgsub><![CDATA[]]></smsmsgsub>
<pushmsgsub><![CDATA[]]></pushmsgsub>
<voicemsgsub><![CDATA[]]></voicemsgsub>
<flag><![CDATA[N]]></flag>
<listitems>
<listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
<listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
<listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
<listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
<listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
<listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
<listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
<listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
<listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
<listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
<listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
<listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
<listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
<listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
<listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
<listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
</listitems>
</ParamSet></InputParameters>'),'//msgdata') input from dual)

select   EXTRACTVALUE(input,'//emailmsgsub')
         ,EXTRACTVALUE(input,'//smsmsgsub')
         ,EXTRACTVALUE(input,'//pushmsgsub')
         ,EXTRACTVALUE(input,'//voicemsgsub')
         ,EXTRACTVALUE(input,'//alerttype')
         ,EXTRACTVALUE(input,'//flag') from t;
Thanks,Ram.
  • 1. Re: performance issue with EXTRACTVALUE function.
    Stew Ashton Expert
    Currently Being Moderated
    As a general rule, you want to indicate an explicit path when you can. In other words, avoid using '//'.

    In this case:
    select EXTRACTVALUE(input,'InputParameters/ParamSet/emailmsgsub')
      ,EXTRACTVALUE(input,'InputParameters/ParamSet/smsmsgsub')
      ,EXTRACTVALUE(input,'InputParameters/ParamSet/pushmsgsub')
      ,EXTRACTVALUE(input,'InputParameters/ParamSet/voicemsgsub')
      ,EXTRACTVALUE(INPUT,'InputParameters/ParamSet/alerttype')
      ,EXTRACTVALUE(input,'InputParameters/ParamSet/flag') from t;
    Depending on how your XML is actually stored, it could be slow no matter what.

    From what I read, XML DB has improved quite a bit since your version. The more you use XML, the more you need to consider upgrading to the current version of the database.
  • 2. Re: performance issue with EXTRACTVALUE function.
    odie_63 Guru
    Currently Being Moderated
    I am getting the XML string length approximately 20 billion .
    20 billion what? Characters, bytes?
    If you really have a 20GB long XML to process, it ought to take some time anyway...

    Where does that string reside in reality? CLOB column/variable?

    The first thing you must do is store the XML in a persistent XMLType column, then query from there :
    CREATE TABLE tmp_xml (id number, xmldoc xmltype);
    
    INSERT INTO tmp_xml VALUES (1, xmltype(<your xml string/column>));
    
    select extractvalue(xmldoc, '/InputParameters/ParamSet/emailmsgsub')
         , extractvalue(xmldoc, '/InputParameters/ParamSet/smsmsgsub')
         , extractvalue(xmldoc, '/InputParameters/ParamSet/pushmsgsub')
         , extractvalue(xmldoc, '/InputParameters/ParamSet/voicemsgsub')
         , extractvalue(xmldoc, '/InputParameters/ParamSet/alerttype')
         , extractvalue(xmldoc, '/InputParameters/ParamSet/flag') 
    from tmp_xml
    ;
    BTW, the sample query you gave doesn't work, there's no "msgdata" element to extract.

    If you don't see an improvement with the suggestion above, then you'll need to implement Object Relational storage, and for that you need an XML schema, do you have one?
  • 3. Re: performance issue with EXTRACTVALUE function.
    RamaKrishna.CH Explorer
    Currently Being Moderated
    1)
    +
    20 billion what? Characters, bytes?
    +
    20 billion characters.

    2)
    +
    Where does that string reside in reality? CLOB column/variable?
    +
    In CSV file in user side.Front end programmer read it and format to XML string.
    My procedure in mode parameter is CLOB.
    3)
    +
      BTW, the sample query you gave doesn't work, there's no "msgdata" element to extract.
    +
    sorry about that. please consider 4th example sample data.

    4)
    +
      If you don't see an improvement with the suggestion above
    +
    i tried like below but didn't saw any improvement.
            WITH t as ( select EXTRACT(XMLTYPE('<InputParameters><ClientName>XXX</ClientName><HpUserId>2500</HpUserId><UserName>ABC</UserName><UserPass></UserPass>
    <ParamSet>
    <msgdata><alerttype>1000</alerttype>
    <emailtext><![CDATA[test]]></emailtext>
    <smstext><![CDATA[]]></smstext>
    <pushtext><![CDATA[]]></pushtext>
    <voicetext><![CDATA[]]></voicetext>
    <emailmsgsub><![CDATA[test]]></emailmsgsub>
    <smsmsgsub><![CDATA[]]></smsmsgsub>
    <pushmsgsub><![CDATA[]]></pushmsgsub>
    <voicemsgsub><![CDATA[]]></voicemsgsub>
    <flag><![CDATA[N]]></flag>
    </msgdata>
    <listitems>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    </listitems>
    </ParamSet></InputParameters>'),'//msgdata') input from dual)
     
    select   EXTRACTVALUE(input,'/msgdata/emailmsgsub')
             ,EXTRACTVALUE(input,'/msgdata/smsmsgsub')
             ,EXTRACTVALUE(input,'/msgdata/pushmsgsub')
             ,EXTRACTVALUE(input,'/msgdata/voicemsgsub')
             ,EXTRACTVALUE(input,'/msgdata/alerttype')
             ,EXTRACTVALUE(input,'/msgdata/flag') from t;
        
    5)
    +
      then you'll need to implement Object Relational storage, and for that you need an XML schema, do you have one?
    +
    No,I our DB no schema like that.

    thanks,ram.

    Edited by: Rama Krishna.CH on Nov 17, 2012 4:29 PM

    Edited by: Rama Krishna.CH on Nov 17, 2012 4:35 PM
  • 4. Re: performance issue with EXTRACTVALUE function.
    RamaKrishna.CH Explorer
    Currently Being Moderated
    Hi,
    my problem solve with writing like below.

    DECLARE
    input  XMLTYPE;
    l_emailtext            CLOB;
      l_smstext              CLOB;
         l_pushtext             CLOB;
      l_voicetext            CLOB;
      l_msgtext              CLOB;
      l_emailmsgsub          VARCHAR2(250);
      l_smsmsgsub            VARCHAR2(250);
      l_pushmsgsub           VARCHAR2(250);
      l_voicemsgsub          VARCHAR2(250);
      l_emmsgid              NUMBER(12); 
      l_flag  char(1);
      l_alerttype char(4);
    begin
    select EXTRACT(XMLTYPE('<InputParameters><ClientName>XXX</ClientName><HpUserId>2500</HpUserId><UserName>ABC</UserName><UserPass></UserPass>
    <ParamSet>
    <msgdata><alerttype>1000</alerttype>
    <emailtext><![CDATA[test]]></emailtext>
    <smstext><![CDATA[]]></smstext>
    <pushtext><![CDATA[]]></pushtext>
    <voicetext><![CDATA[]]></voicetext>
    <emailmsgsub><![CDATA[test]]></emailmsgsub>
    <smsmsgsub><![CDATA[]]></smsmsgsub>
    <pushmsgsub><![CDATA[]]></pushmsgsub>
    <voicemsgsub><![CDATA[]]></voicemsgsub>
    <flag><![CDATA[N]]></flag>
    </msgdata>
    <listitems>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    <listitem><homePhone>6666446666</homePhone><mobile>9966176187</mobile><emailaddr><![CDATA[harinathp@seadata.com]]></emailaddr><mbrsep>94417111</mbrsep><deviceid>txt1att.net</deviceid></listitem>
    </listitems>
    </ParamSet></InputParameters>'),'//msgdata') INTO input from dual;
    select   EXTRACTVALUE(input,'/msgdata/emailmsgsub')
             ,EXTRACTVALUE(input,'/msgdata/smsmsgsub')
             ,EXTRACTVALUE(input,'/msgdata/pushmsgsub')
             ,EXTRACTVALUE(input,'/msgdata/voicemsgsub')
             ,EXTRACTVALUE(input,'/msgdata/alerttype')
             ,EXTRACTVALUE(input,'/msgdata/flag') INTO l_emailmsgsub ,l_smsmsgsub,l_pushmsgsub,l_voicemsgsub,l_alerttype,l_flag from DUAL;
    END;
    thanks for helping me.
  • 5. Re: performance issue with EXTRACTVALUE function.
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    odie_63 wrote:
    I am getting the XML string length approximately 20 billion .
    20 billion what? Characters, bytes?
    If you really have a 20GB long XML to process, it ought to take some time anyway...
    LOL - I would have liked those response times :-) (me being back from Bulgaria)
  • 6. Re: performance issue with EXTRACTVALUE function.
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    As mentioned AVOID // !!!
  • 7. Re: performance issue with EXTRACTVALUE function.
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    You know that this database release is not supported anymore, right...?

Legend

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