SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Slow performance with XMLTable. Are there alternatives?

Tobias ArnholdJun 11 2012 — edited Jun 12 2012

Hi,

I use the following code to select xml data from a xml object.

SELECT DATEN.*
  FROM (select FNC_RETURN_CLOB()) as l_xml from dual) PO,
       XMLTable('//ROWSET/ROW' PASSING PO.L_XML
       COLUMNS  "KdNr"    VARCHAR2(10) PATH 'KdNr',
                "PMTyp" VARCHAR2(50) PATH 'PMTyp',
                "Typname" VARCHAR2(500) PATH 'Typname',
                "Ident" VARCHAR2(20) PATH 'Ident',
                "Barcode" VARCHAR2(20) PATH 'Barcode',
                "GUID" VARCHAR2(50) PATH 'GUID',
                "DatLP" VARCHAR2(20) PATH 'DatLP') AS DATEN

The function "FNC_RETURN_CLOB" connects towards a web service and returns the data as xml object. The XMLTable function generates a SQL table from it. The process to select around 4000 rows needs around 12-15 seconds where the transformation of the xml object to sql needs 5-7 seconds. I wonder why it is so slow? Are there alternative ways available?

The function looks similar to this:

create or replace
FUNCTION FNC_RETURN_CLOB RETURN XMLTYPE AS 
 l_clob CLOB;
 l_xml XMLTYPE;
BEGIN

  l_clob := apex_web_service.make_rest_request(
  p_url => 'http://hps.aru-web.goes.de/cgi-bin/apexsda1d.exe?t=1234&p1=0815',
  p_http_method => 'POST'
  );

  l_xml := XMLTYPE(l_clob);

  RETURN l_xml;
END FNC_RETURN_CLOB;

The returns xml code looks like this:

&lt;?xml version="1.0" encoding="iso-8859-1" standalone="no"?>
  &lt;ROWSET>
    &lt;ROW>
      &lt;KdNr>411047&lt;/KdNr>
      &lt;PMTyp>2322341&lt;/PMTyp>
      &lt;Typname>162 Messgeraet&lt;/Typname>
      &lt;Ident>0001&lt;/Ident>
      &lt;Barcode>20020070&lt;/Barcode>
      &lt;GUID>31127F5D53FB42D7A10D06F16612319</GUID>
      &lt;DatLP>29.06.2008&lt;/DatLP>
    &lt;/ROW>
  &lt;/ROWSET>

Database version: 11.1.0.7.0 64bit Production
Explain plan with SQL Developer:

 OPERATION  	 OBJECT_NAME  	 OPTIONS  	 COST 
	 SELECT STATEMENT 	    	    	 31     	
	 NESTED LOOPS 	    	    	 31     	    	
	 FAST DUAL 	    	    	 2     	    		 COLLECTION ITERATOR 	 XMLSEQUENCEFROMXMLTYPE  	 PICKLER FETCH

Thanks ahead,

Tobias

Edited by: Tobias Arnhold on Jun 11, 2012 12:50 PM

This post has been answered by user503699 on Jun 11 2012

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Jul 10 2012

Added on Jun 11 2012

#xml

19 comments

7,461 views