Re: faster way to create XML export
Date: Tue, 2 Aug 2011 15:08:11 -0500
Message-ID: <CA+fnDAa1hN_7p14L-obcjkXkXRHpE7E8ZmdrXPEkbSTzFu-gEQ_at_mail.gmail.com>
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> 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>
> Reply-To: "jeremy.schneider_at_ardentperf.com" <
> jeremy.schneider_at_ardentperf.com>
> Date: Tue, 2 Aug 2011 19:07:16 +0200
> To: Oracle-L <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
> +1 312-725-9249
>
> Jeremy Schneider
> Chicago
>
-- http://www.ardentperf.com +1 312-725-9249 Jeremy Schneider Chicago -- http://www.freelists.org/webpage/oracle-lReceived on Tue Aug 02 2011 - 15:08:11 CDT