Re: faster way to create XML export
Date: Tue, 2 Aug 2011 23:41:38 +0200
Message-ID: <CA5E3985.9B2E%marco.gralike_at_amis.nl>
ONE 1++GB in XML document, are you serious. Wouldn't be surprised though. I see it more and more often. ;-)
No the stuff showed below is in C and/or even part of the C kernel. It is possible to do in SQL and yes due to the fact that it is 1 XML document you will get in trouble regarding those mentioned sizes per XML document. I am guessing, although never really checked that if you switched it again for a C based SAV serializer it would be even faster or does it reside in the JVM database kernel?
I wonder which part picks up those big XML documents and tries to do something useful with it. Work to be done with those documents afterwards most be very resource intensive ;-)
From: Jeremy Schneider <jeremy.schneider_at_ardentperf.com<mailto:jeremy.schneider_at_ardentperf.com>>
Date: Tue, 2 Aug 2011 22:08:11 +0200
To: Marco Gralike <marco.gralike_at_amis.nl<mailto:marco.gralike_at_amis.nl>>
Cc: Oracle-L <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>>
Subject: Re: faster way to create XML export
Hey Marco - I was hoping that I might see a reply from you! :)
We'll definitely give this a try and let you know. One question... will this build the entire XML object in memory before dumping to file, or will it stream? Some of our files are currently up to several hundred MB, which could fit in memory -- but in the future they might grow into the GB range and spill over into temp or swap space. Seems like it might be better to stream a GB-size file... is this possible in SQL or do we have to revert to Java for that?
On another note, we already discovered one way to speed it up... turns out that we were using an older apache XMLSerializer instead of the Oracle XMLSAXSerializer. Might be because this code was written in the 9i timeframe, not sure if the Oracle serializer was available then. Anyway, just switching serializers (changed 1 line of code) almost doubled the speed.
-Jeremy
On Tue, Aug 2, 2011 at 1:43 PM, Marco Gralike <Marco.Gralike_at_amis.nl<mailto:Marco.Gralike_at_amis.nl>> wrote: http://www.liberidu.com/blog/?p=365
Based on something like:
Select xmltype(cursor(select * from all_objects)).getClobVal() from dual;
You could do something like (UTLDATA is an Oracle directory alias)
SQL> DECLARE2 rc sys_refcursor;
3 BEGIN4 OPEN rc FOR SELECT * FROM ( SELECT rownum FROM dual CONNECT BY level < 500000 );
5 dbms_xslprocessor.clob2file( xmltype( rc ).getclobval( ) , ‘UTLDATA’,'my.xml’);
6 end;
7 /
From: Jeremy Schneider <jeremy.schneider_at_ardentperf.com<mailto:jeremy.schneider_at_ardentperf.com>>
Reply-To: "jeremy.schneider_at_ardentperf.com<mailto:jeremy.schneider_at_ardentperf.com>" <jeremy.schneider_at_ardentperf.com<mailto:jeremy.schneider_at_ardentperf.com>>
Date: Tue, 2 Aug 2011 19:07:16 +0200
To: Oracle-L <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>>
Subject: faster way to create XML export
What's the fastest way to create an XML file on disk with a SQL query as input? We're on Oracle 10.2 and currently we're using the Java getXMLSAX() function with an output stream and an OracleXMLQuery object (from Oracle's Java API for XML). This is causing some performance problems... in fact it's accounting for over half the time in one particular long-running report which is important to the business. It really smells to me like something which could go a lot faster. Anyone have experience with this, and can suggest a better way?
-Jeremy
--
http://www.ardentperf.com<http://www.ardentperf.com/>
+1 312-725-9249<tel:%2B1%20312-725-9249>
Jeremy Schneider
Chicago
--
http://www.ardentperf.com
+1 312-725-9249
Jeremy Schneider
Chicago
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Aug 02 2011 - 16:41:38 CDT