Re: faster way to create XML export
- From: Marco Gralike <Marco.Gralike@xxxxxxx>
- To: Jeremy Schneider <jeremy.schneider@xxxxxxxxxxxxxx>
- Date: Tue, 2 Aug 2011 23:41:38 +0200
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@xxxxxxxxxxxxxx<mailto:jeremy.schneider@xxxxxxxxxxxxxx>>
Date: Tue, 2 Aug 2011 22:08:11 +0200
To: Marco Gralike <marco.gralike@xxxxxxx<mailto:marco.gralike@xxxxxxx>>
Cc: Oracle-L <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
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@xxxxxxx<mailto:Marco.Gralike@xxxxxxx>> 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@xxxxxxxxxxxxxx<mailto:jeremy.schneider@xxxxxxxxxxxxxx>>
Reply-To:
"jeremy.schneider@xxxxxxxxxxxxxx<mailto:jeremy.schneider@xxxxxxxxxxxxxx>"
<jeremy.schneider@xxxxxxxxxxxxxx<mailto:jeremy.schneider@xxxxxxxxxxxxxx>>
Date: Tue, 2 Aug 2011 19:07:16 +0200
To: Oracle-L <oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>>
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
Other related posts:
