FAQ
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

Search Discussions

  • Marco Gralike at Aug 2, 2011 at 6:43 pm
    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> DECLARE

    2 rc sys_refcursor;
    3 BEGIN

    4 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 >
    Reply-To: "jeremy.schneider_at_ardentperf.com" >
    Date: Tue, 2 Aug 2011 19:07:16 +0200
    To: Oracle-L >
    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
  • Jeremy Schneider at Aug 2, 2011 at 8:08 pm
    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 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
    Reply-To: "jeremy.schneider_at_ardentperf.com" <
    jeremy.schneider_at_ardentperf.com>
    Date: Tue, 2 Aug 2011 19:07:16 +0200
    To: Oracle-L
    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-l
  • Marco Gralike at Aug 2, 2011 at 9:41 pm
    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 >
    Date: Tue, 2 Aug 2011 22:08:11 +0200
    To: Marco Gralike >
    Cc: Oracle-L >
    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 > 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 >
    Reply-To: "jeremy.schneider_at_ardentperf.com" >
    Date: Tue, 2 Aug 2011 19:07:16 +0200
    To: Oracle-L >
    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

    Jeremy Schneider
    Chicago

    --
    http://www.ardentperf.com
    +1 312-725-9249

    Jeremy Schneider
    Chicago

    --
    http://www.freelists.org/webpage/oracle-l
  • Jeremy Schneider at Aug 2, 2011 at 11:19 pm
    Well we're not making any 1GB files yet, but I can't really imagine what
    anyone does with a 400MB file either. Anyway - we were doing some testing
    with a much smaller "test" dataset (8MB file), and we tried Marco's idea.
    Here are the results:

    Original Code: 96 sec
    Oracle Serializer: 40 sec
    Marco's Code: 3 sec

    :)

    Sweet! However, it seems that there's an implicit conversion from XMLType
    to CLOB and it's not immediately obvious how to control the XML that's
    generated. I guess it's time for me to learn some more about XMLDB. :-/
    Here's what we're trying to figure out how to do with XMLType:

    xmlQuery.keepCursorState(true);
    xmlQuery.setMaxRows(pRowLimit_variable); // Note: we continue with cursor
    and split output to multiple files
    xmlQuery.setRowTag(pRowHeader_variable);
    xmlQuery.setRowsetTag(pRowHeader_variable+"_MYSUFFIX");
    xmlQuery.setDateFormat("yyyy-MM-dd'T'HH:mm:ss");
    xmlQuery.setStylesheetHeader(pXSLtURI_variable);

    Any tips would be appreciated...

    -J

    PS - this blog post by Marco is excellent:
    http://www.liberidu.com/blog/?p=369
    On Tue, Aug 2, 2011 at 4:41 PM, Marco Gralike wrote:

    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;-)
    --
    http://www.ardentperf.com
    +1 312-725-9249

    Jeremy Schneider
    Chicago

    --
    http://www.freelists.org/webpage/oracle-l
  • Jeremy Schneider at Aug 2, 2011 at 11:34 pm
    Oh yeah, we also tested with the DBMS_XMLQuery package - it seemed to take
    about as long as the Oracle Serializer java code.

    On Tue, Aug 2, 2011 at 6:19 PM, Jeremy Schneider <
    jeremy.schneider_at_ardentperf.com> wrote:
    Well we're not making any 1GB files yet, but I can't really imagine what
    anyone does with a 400MB file either. Anyway - we were doing some testing
    with a much smaller "test" dataset (8MB file), and we tried Marco's idea.
    Here are the results:

    Original Code: 96 sec
    Oracle Serializer: 40 sec
    Marco's Code: 3 sec

    :)

    Sweet! However, it seems that there's an implicit conversion from XMLType
    to CLOB and it's not immediately obvious how to control the XML that's
    generated. I guess it's time for me to learn some more about XMLDB. :-/
    Here's what we're trying to figure out how to do with XMLType:

    - xmlQuery.keepCursorState(true);
    - xmlQuery.setMaxRows(pRowLimit_variable); // Note: we continue with
    cursor and split output to multiple files
    - xmlQuery.setRowTag(pRowHeader_variable);
    - xmlQuery.setRowsetTag(pRowHeader_variable+"_MYSUFFIX");
    - xmlQuery.setDateFormat("yyyy-MM-dd'T'HH:mm:ss");
    - xmlQuery.setStylesheetHeader(pXSLtURI_variable);

    Any tips would be appreciated...

    -J


    PS - this blog post by Marco is excellent:
    http://www.liberidu.com/blog/?p=369

    On Tue, Aug 2, 2011 at 4:41 PM, Marco Gralike wrote:

    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;-)
    --
    http://www.ardentperf.com
    +1 312-725-9249

    Jeremy Schneider
    Chicago

    --
    http://www.freelists.org/webpage/oracle-l
  • Jeremy Schneider at Aug 3, 2011 at 12:43 am
    We just finished a test run with DBMS_XMLGEN on the same dataset -- it
    finished in 2 sec. Still used dbms_xslprocessor.clob2file for filewriting.
    Looks like this will do most everything I mentioned - just working on the
    last step now, getting the XSLT headers. (Seems to be a function for this
    but it's not well documented and didn't seem to work on our first try...)

    Also, I noticed something in the oracle docs here:
    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14252/adx_j_xsu.htm#sthref546

    "Note: For increased performance, consider using DBMS_XMLGen and
    DBMS_XMLStore as alternatives to DBMS_XMLQuery and DBMS_XMLSave. The former
    packages are written in C and are built in to the database kernel. You can
    also use SQL/XML functions such as XML_Element for XML access in the
    database."

    No kidding - way faster! Wish I'd seen this before! :)

    -Jeremy

    On Tue, Aug 2, 2011 at 6:34 PM, Jeremy Schneider <
    jeremy.schneider_at_ardentperf.com> wrote:
    Oh yeah, we also tested with the DBMS_XMLQuery package - it seemed to take
    about as long as the Oracle Serializer java code.


    On Tue, Aug 2, 2011 at 6:19 PM, Jeremy Schneider <
    jeremy.schneider_at_ardentperf.com> wrote:
    Well we're not making any 1GB files yet, but I can't really imagine what
    anyone does with a 400MB file either. Anyway - we were doing some testing
    with a much smaller "test" dataset (8MB file), and we tried Marco's idea.
    Here are the results:

    Original Code: 96 sec
    Oracle Serializer: 40 sec
    Marco's Code: 3 sec

    :)

    Sweet! However, it seems that there's an implicit conversion from XMLType
    to CLOB and it's not immediately obvious how to control the XML that's
    generated. I guess it's time for me to learn some more about XMLDB. :-/
    Here's what we're trying to figure out how to do with XMLType:

    - xmlQuery.keepCursorState(true);
    - xmlQuery.setMaxRows(pRowLimit_variable); // Note: we continue with
    cursor and split output to multiple files
    - xmlQuery.setRowTag(pRowHeader_variable);
    - xmlQuery.setRowsetTag(pRowHeader_variable+"_MYSUFFIX");
    - xmlQuery.setDateFormat("yyyy-MM-dd'T'HH:mm:ss");
    - xmlQuery.setStylesheetHeader(pXSLtURI_variable);

    Any tips would be appreciated...

    -J


    PS - this blog post by Marco is excellent:
    http://www.liberidu.com/blog/?p=369

    On Tue, Aug 2, 2011 at 4:41 PM, Marco Gralike wrote:

    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;-)

    --
    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-l
  • Marco Gralike at Aug 3, 2011 at 1:10 am
    Ah, I see you have found it :-) (the C bit contra Java wrapper bit)

    From: Jeremy Schneider >
    Date: Wed, 3 Aug 2011 02:43:36 +0200
    To: Marco Gralike >
    Cc: Oracle-L >
    Subject: Re: faster way to create XML export

    We just finished a test run with DBMS_XMLGEN on the same dataset -- it finished in 2 sec. Still used dbms_xslprocessor.clob2file for filewriting. Looks like this will do most everything I mentioned - just working on the last step now, getting the XSLT headers. (Seems to be a function for this but it's not well documented and didn't seem to work on our first try...)

    Also, I noticed something in the oracle docs here:
    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14252/adx_j_xsu.htm#sthref546

    "Note: For increased performance, consider using DBMS_XMLGen and DBMS_XMLStore as alternatives to DBMS_XMLQuery and DBMS_XMLSave. The former packages are written in C and are built in to the database kernel. You can also use SQL/XML functions such as XML_Element for XML access in the database."

    No kidding - way faster! Wish I'd seen this before! :)

    -Jeremy

    On Tue, Aug 2, 2011 at 6:34 PM, Jeremy Schneider > wrote:
    Oh yeah, we also tested with the DBMS_XMLQuery package - it seemed to take about as long as the Oracle Serializer java code.

    On Tue, Aug 2, 2011 at 6:19 PM, Jeremy Schneider > wrote:
    Well we're not making any 1GB files yet, but I can't really imagine what anyone does with a 400MB file either. Anyway - we were doing some testing with a much smaller "test" dataset (8MB file), and we tried Marco's idea. Here are the results:

    Original Code: 96 sec
    Oracle Serializer: 40 sec
    Marco's Code: 3 sec

    :)

    Sweet! However, it seems that there's an implicit conversion from XMLType to CLOB and it's not immediately obvious how to control the XML that's generated. I guess it's time for me to learn some more about XMLDB. :-/ Here's what we're trying to figure out how to do with XMLType:

    xmlQuery.keepCursorState(true);
    xmlQuery.setMaxRows(pRowLimit_variable); // Note: we continue with cursor and split output to multiple files
    xmlQuery.setRowTag(pRowHeader_variable);
    xmlQuery.setRowsetTag(pRowHeader_variable+"_MYSUFFIX");
    xmlQuery.setDateFormat("yyyy-MM-dd'T'HH:mm:ss");
    xmlQuery.setStylesheetHeader(pXSLtURI_variable);

    Any tips would be appreciated...

    -J

    PS - this blog post by Marco is excellent:
    http://www.liberidu.com/blog/?p=369

    On Tue, Aug 2, 2011 at 4:41 PM, Marco Gralike > wrote:
    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;-)

    --
    http://www.ardentperf.com<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-l
  • Jeremy Schneider at Aug 3, 2011 at 1:27 am
    Any idea how to add the XSLT header with XMLGEN, similar to java
    xmlQuery.setStylesheetHeader()? We can't figure it out.

    We need output like this at the top of the file:
    <?xml-stylesheet href="<a href="http://some_xml.xslt">http://some_xml.xslt" type="text/xsl"?>

    -J
    On Tue, Aug 2, 2011 at 8:10 PM, Marco Gralike wrote:

    Ah, I see you have found it :-) (the C bit contra Java wrapper bit)

    From: Jeremy Schneider
    Date: Wed, 3 Aug 2011 02:43:36 +0200

    To: Marco Gralike
    Cc: Oracle-L
    Subject: Re: faster way to create XML export

    We just finished a test run with DBMS_XMLGEN on the same dataset -- it
    finished in 2 sec. Still used dbms_xslprocessor.clob2file for filewriting.
    Looks like this will do most everything I mentioned - just working on the
    last step now, getting the XSLT headers. (Seems to be a function for this
    but it's not well documented and didn't seem to work on our first try...)

    Also, I noticed something in the oracle docs here:

    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14252/adx_j_xsu.htm#sthref546

    "Note: For increased performance, consider using DBMS_XMLGen and
    DBMS_XMLStore as alternatives to DBMS_XMLQuery and DBMS_XMLSave. The former
    packages are written in C and are built in to the database kernel. You can
    also use SQL/XML functions such as XML_Element for XML access in the
    database."

    No kidding - way faster! Wish I'd seen this before! :)

    -Jeremy

    On Tue, Aug 2, 2011 at 6:34 PM, Jeremy Schneider <
    jeremy.schneider_at_ardentperf.com> wrote:
    Oh yeah, we also tested with the DBMS_XMLQuery package - it seemed to take
    about as long as the Oracle Serializer java code.


    On Tue, Aug 2, 2011 at 6:19 PM, Jeremy Schneider <
    jeremy.schneider_at_ardentperf.com> wrote:
    Well we're not making any 1GB files yet, but I can't really imagine what
    anyone does with a 400MB file either. Anyway - we were doing some testing
    with a much smaller "test" dataset (8MB file), and we tried Marco's idea.
    Here are the results:

    Original Code: 96 sec
    Oracle Serializer: 40 sec
    Marco's Code: 3 sec

    :)

    Sweet! However, it seems that there's an implicit conversion from
    XMLType to CLOB and it's not immediately obvious how to control the XML
    that's generated. I guess it's time for me to learn some more about XMLDB.
    :-/ Here's what we're trying to figure out how to do with XMLType:

    - xmlQuery.keepCursorState(true);
    - xmlQuery.setMaxRows(pRowLimit_variable); // Note: we continue with
    cursor and split output to multiple files
    - xmlQuery.setRowTag(pRowHeader_variable);
    - xmlQuery.setRowsetTag(pRowHeader_variable+"_MYSUFFIX");
    - xmlQuery.setDateFormat("yyyy-MM-dd'T'HH:mm:ss");
    - xmlQuery.setStylesheetHeader(pXSLtURI_variable);

    Any tips would be appreciated...

    -J


    PS - this blog post by Marco is excellent:
    http://www.liberidu.com/blog/?p=369

    On Tue, Aug 2, 2011 at 4:41 PM, Marco Gralike wrote:

    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;-)
    --
    http://www.ardentperf.com
    +1 312-725-9249

    Jeremy Schneider
    Chicago

    --
    http://www.freelists.org/webpage/oracle-l
  • Jeremy Schneider at Aug 3, 2011 at 3:20 am
    FYI, stumbled accross metalink note 1299242.1 which made one suggestion -
    just using the LOB interface to manipulate the top few lines in the LOB.
    (In 11gR1 you can use a stylesheet transformation - but this isn't available
    in 10gR2.) Not as clean as I'd hoped for, but it should work. If anyone
    has any better ideas, please share!

    -J

    On Tue, Aug 2, 2011 at 8:27 PM, Jeremy Schneider <
    jeremy.schneider_at_ardentperf.com> wrote:
    Any idea how to add the XSLT header with XMLGEN, similar to java
    xmlQuery.setStylesheetHeader()? We can't figure it out.

    We need output like this at the top of the file:
    <?xml-stylesheet href="<a href="http://some_xml.xslt">http://some_xml.xslt" type="text/xsl"?>

    -J
    On Tue, Aug 2, 2011 at 8:10 PM, Marco Gralike wrote:

    Ah, I see you have found it :-) (the C bit contra Java wrapper bit)

    From: Jeremy Schneider
    Date: Wed, 3 Aug 2011 02:43:36 +0200

    To: Marco Gralike
    Cc: Oracle-L
    Subject: Re: faster way to create XML export

    We just finished a test run with DBMS_XMLGEN on the same dataset -- it
    finished in 2 sec. Still used dbms_xslprocessor.clob2file for filewriting.
    Looks like this will do most everything I mentioned - just working on the
    last step now, getting the XSLT headers. (Seems to be a function for this
    but it's not well documented and didn't seem to work on our first try...)

    Also, I noticed something in the oracle docs here:

    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14252/adx_j_xsu.htm#sthref546

    "Note: For increased performance, consider using DBMS_XMLGen and
    DBMS_XMLStore as alternatives to DBMS_XMLQuery and DBMS_XMLSave. The former
    packages are written in C and are built in to the database kernel. You can
    also use SQL/XML functions such as XML_Element for XML access in the
    database."

    No kidding - way faster! Wish I'd seen this before! :)

    -Jeremy

    On Tue, Aug 2, 2011 at 6:34 PM, Jeremy Schneider <
    jeremy.schneider_at_ardentperf.com> wrote:
    Oh yeah, we also tested with the DBMS_XMLQuery package - it seemed to
    take about as long as the Oracle Serializer java code.


    On Tue, Aug 2, 2011 at 6:19 PM, Jeremy Schneider <
    jeremy.schneider_at_ardentperf.com> wrote:
    Well we're not making any 1GB files yet, but I can't really imagine what
    anyone does with a 400MB file either. Anyway - we were doing some testing
    with a much smaller "test" dataset (8MB file), and we tried Marco's idea.
    Here are the results:

    Original Code: 96 sec
    Oracle Serializer: 40 sec
    Marco's Code: 3 sec

    :)

    Sweet! However, it seems that there's an implicit conversion from
    XMLType to CLOB and it's not immediately obvious how to control the XML
    that's generated. I guess it's time for me to learn some more about XMLDB.
    :-/ Here's what we're trying to figure out how to do with XMLType:

    - xmlQuery.keepCursorState(true);
    - xmlQuery.setMaxRows(pRowLimit_variable); // Note: we continue with
    cursor and split output to multiple files
    - xmlQuery.setRowTag(pRowHeader_variable);
    - xmlQuery.setRowsetTag(pRowHeader_variable+"_MYSUFFIX");
    - xmlQuery.setDateFormat("yyyy-MM-dd'T'HH:mm:ss");
    - xmlQuery.setStylesheetHeader(pXSLtURI_variable);

    Any tips would be appreciated...

    -J


    PS - this blog post by Marco is excellent:
    http://www.liberidu.com/blog/?p=369

    On Tue, Aug 2, 2011 at 4:41 PM, Marco Gralike wrote:

    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;-)
    --
    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-l
  • Marco Gralike at Aug 3, 2011 at 7:13 am
    http://forums.oracle.com/forums/thread.jspa?threadID=926858 (seems bugging, not maintained since 2004)

    One of the reasons i use the advised stuff: xmlelement, xmlforest, xmlagg, xmlpi, xmlconcat, etc

    Post the question on the forum�

    From: Jeremy Schneider >
    Date: Wed, 3 Aug 2011 03:27:42 +0200
    To: Marco Gralike >
    Cc: Oracle-L >
    Subject: Re: faster way to create XML export

    Any idea how to add the XSLT header with XMLGEN, similar to java xmlQuery.setStylesheetHeader()? We can't figure it out.

    We need output like this at the top of the file:
    <?xml-stylesheet href="<a href="http://some_xml.xslt">http://some_xml.xslt" type="text/xsl"?>

    -J

    On Tue, Aug 2, 2011 at 8:10 PM, Marco Gralike > wrote:
    Ah, I see you have found it :-) (the C bit contra Java wrapper bit)

    From: Jeremy Schneider >
    Date: Wed, 3 Aug 2011 02:43:36 +0200

    To: Marco Gralike >
    Cc: Oracle-L >
    Subject: Re: faster way to create XML export

    We just finished a test run with DBMS_XMLGEN on the same dataset -- it finished in 2 sec. Still used dbms_xslprocessor.clob2file for filewriting. Looks like this will do most everything I mentioned - just working on the last step now, getting the XSLT headers. (Seems to be a function for this but it's not well documented and didn't seem to work on our first try...)

    Also, I noticed something in the oracle docs here:
    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14252/adx_j_xsu.htm#sthref546

    "Note: For increased performance, consider using DBMS_XMLGen and DBMS_XMLStore as alternatives to DBMS_XMLQuery and DBMS_XMLSave. The former packages are written in C and are built in to the database kernel. You can also use SQL/XML functions such as XML_Element for XML access in the database."

    No kidding - way faster! Wish I'd seen this before! :)

    -Jeremy

    On Tue, Aug 2, 2011 at 6:34 PM, Jeremy Schneider > wrote:
    Oh yeah, we also tested with the DBMS_XMLQuery package - it seemed to take about as long as the Oracle Serializer java code.

    On Tue, Aug 2, 2011 at 6:19 PM, Jeremy Schneider > wrote:
    Well we're not making any 1GB files yet, but I can't really imagine what anyone does with a 400MB file either. Anyway - we were doing some testing with a much smaller "test" dataset (8MB file), and we tried Marco's idea. Here are the results:

    Original Code: 96 sec
    Oracle Serializer: 40 sec
    Marco's Code: 3 sec

    :)

    Sweet! However, it seems that there's an implicit conversion from XMLType to CLOB and it's not immediately obvious how to control the XML that's generated. I guess it's time for me to learn some more about XMLDB. :-/ Here's what we're trying to figure out how to do with XMLType:

    xmlQuery.keepCursorState(true);
    xmlQuery.setMaxRows(pRowLimit_variable); // Note: we continue with cursor and split output to multiple files
    xmlQuery.setRowTag(pRowHeader_variable);
    xmlQuery.setRowsetTag(pRowHeader_variable+"_MYSUFFIX");
    xmlQuery.setDateFormat("yyyy-MM-dd'T'HH:mm:ss");
    xmlQuery.setStylesheetHeader(pXSLtURI_variable);

    Any tips would be appreciated...

    -J

    PS - this blog post by Marco is excellent:
    http://www.liberidu.com/blog/?p=369

    On Tue, Aug 2, 2011 at 4:41 PM, Marco Gralike > wrote:
    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;-)
  • Marco Gralike at Aug 3, 2011 at 1:08 am
    Because its the same. Its a PL/SQL Java wrapper (same XDK)

    From: Jeremy Schneider >
    Date: Wed, 3 Aug 2011 01:34:24 +0200
    To: Marco Gralike >
    Cc: Oracle-L >
    Subject: Re: faster way to create XML export

    Oh yeah, we also tested with the DBMS_XMLQuery package - it seemed to take about as long as the Oracle Serializer java code.

    On Tue, Aug 2, 2011 at 6:19 PM, Jeremy Schneider > wrote:
    Well we're not making any 1GB files yet, but I can't really imagine what anyone does with a 400MB file either. Anyway - we were doing some testing with a much smaller "test" dataset (8MB file), and we tried Marco's idea. Here are the results:

    Original Code: 96 sec
    Oracle Serializer: 40 sec
    Marco's Code: 3 sec

    :)

    Sweet! However, it seems that there's an implicit conversion from XMLType to CLOB and it's not immediately obvious how to control the XML that's generated. I guess it's time for me to learn some more about XMLDB. :-/ Here's what we're trying to figure out how to do with XMLType:

    xmlQuery.keepCursorState(true);
    xmlQuery.setMaxRows(pRowLimit_variable); // Note: we continue with cursor and split output to multiple files
    xmlQuery.setRowTag(pRowHeader_variable);
    xmlQuery.setRowsetTag(pRowHeader_variable+"_MYSUFFIX");
    xmlQuery.setDateFormat("yyyy-MM-dd'T'HH:mm:ss");
    xmlQuery.setStylesheetHeader(pXSLtURI_variable);

    Any tips would be appreciated...

    -J

    PS - this blog post by Marco is excellent:
    http://www.liberidu.com/blog/?p=369

    On Tue, Aug 2, 2011 at 4:41 PM, Marco Gralike > wrote:
    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;-)

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 2, '11 at 5:07p
activeAug 3, '11 at 7:13a
posts12
users2
websiteoracle.com

2 users in discussion

Jeremy Schneider: 7 posts Marco Gralike: 5 posts

People

Translate

site design / logo © 2022 Grokbase