This discussion is archived
10 Replies Latest reply: Apr 15, 2013 12:06 PM by 811419 RSS

Examples of large SELECT XmlElement - is Oracle support robust?

811419 Newbie
Currently Being Moderated
Hello,

Can anyone offer examples of using large SELECT XmlElement/XmlAgg/XmlForest/XmlConcat queries in heavy-use applications? We have an application that generates complex XML messages with hundreds of elements, using data from 60+ tables, but we're not convinced that Oracle provides a robust engine for this kind of application, even if I break it down into smaller pieces using XMLTYPE temporary variables.

[We're using Oracle 10.2.0.5 Patch 20 x64.]

If anyone thinks otherwise, I would be eager to hear your reasoning or experience.

Thanks.

Update: I heard from one of the authors of the white paper at http://www.oracle.com/technetwork/database-features/xmldb/icde2005-industrial-strength-sqlxml-129281.pdf; Anand Manikutty was kind enough to respond to my inquiry:
"... SQL/XML is an extremely robust technology which has been deployed by a large number of customers. To add to what Vikas Arora et al.'s paper states, this technology makes Oracle a truly industrial strength technology for XML processing. It is flexible, reliable and high performant. I have personally used it myself in developing web-based applications, and it worked extremely well in the admittedly limited context that I used it under."

Edited by: Kit.net on Apr 10, 2013 12:45 PM
  • 1. Re: Examples of large SELECT XmlElement - is Oracle support robust?
    odie_63 Guru
    Currently Being Moderated
    Hi,
    Kit.net wrote:
    but we're not convinced that Oracle provides a robust engine for this kind of application
    Can you first share your experience on the subject ? Right now, I don't see any facts.

    Are you having difficulties with building the queries?
    Are you looking for performance, maintainability, both?
    Can anyone offer examples of using large SELECT XmlElement/XmlAgg/XmlForest/XmlConcat queries in use?
    Why do you need specific examples?
    SQL/XML functions can be used to build any structure you require, with no additional layer to manipulate the data.
    Would you prefer using a tedious DOM approach, building the document element by element, row by row ?

    XSLT may be another option. SQL/XML functions would be only used to generate a "raw" XML doc with the necessary data, then the stylesheet would take care of building the final document.

    Again, share your use case.
  • 2. Re: Examples of large SELECT XmlElement - is Oracle support robust?
    811419 Newbie
    Currently Being Moderated
    We have a series of web services that return XML documents from data stored in Oracle. I implemented one with a big SELECT XmlElement(...), but we started seeing core dumps, so we have an SR (service request) open with support.oracle.com. I found SQL/XML the most elegant (fewest lines of code, easy to read and maintain) and efficient (100x faster) way to develop, as you point out, but the response we're getting from Oracle is underwhelming.

    I'm trying to decide whether it's worth trying to pursue, or give up and generate the XML using client code. If there are other industrial-strength applications using SQL/XML, that might be worth knowing at this point.
  • 3. Re: Examples of large SELECT XmlElement - is Oracle support robust?
    odie_63 Guru
    Currently Being Moderated
    Kit.net wrote:
    If there are other industrial-strength applications using SQL/XML, that might be worth knowing at this point.
    How much data are you dealing with ? Gigabyte(s)?

    I experienced some issues too with aggregation context close to a few hundreds of MBs.
  • 4. Re: Examples of large SELECT XmlElement - is Oracle support robust?
    975554 Newbie
    Currently Being Moderated
    I am using SQL/XML to generate XML and I find it easy to create and maintain the code. I have tried client tools such as Altova but it was very!!!!! slow when I was creating XML for a large dataset. I am creating XML into a CLOB and then using dbms_xlsprocessor to create the OS file. In terms of how big the OS file can be I am finding out that the limit is somewhere around 700mb. I also have been working with Oracle on a SR for over 4 months concerning a memory error when the size of the XML it too big. Oracle created Bug 16219864 for it. For by latest issue see: "xmlagg is executing slow on unicode database " in Oracle Discussion Forums/ XML /General XML.
  • 5. Re: Examples of large SELECT XmlElement - is Oracle support robust?
    811419 Newbie
    Currently Being Moderated
    The maximum possible size of data was 2,476,831 bytes (selecting 25 records at a time); but the core dumps recurred after stepping to a single record.

    Once the database is in a state where it will core dump, it seems to core dump repeatedly on certain sets of data, but this is mysterious to me, because it runs through all the data multiple times without a problem before it gets into this state.
  • 6. Re: Examples of large SELECT XmlElement - is Oracle support robust?
    odie_63 Guru
    Currently Being Moderated
    The maximum possible size of data was 2,476,831 bytes (selecting 25 records at a time); but the core dumps recurred after stepping to a single record.
    This is strange indeed. You're far from the limit I observed, 2MB is still a small amount of data.

    I'm curious to know what Oracle Support has to say.

    Another issue I used to have (and producing a core dump) occurred when using an ORDER BY clause in a SELECT supposed to return a single row and column (via XMLAgg).
    Pretty innocuous at first sight but that query crashed the instance all the time, regardless of the volumetry. Removing the unnecessary ORDER BY clause solved the problem. Version was 10.2.0.5 back then.
  • 7. Re: Examples of large SELECT XmlElement - is Oracle support robust?
    811419 Newbie
    Currently Being Moderated
    I appreciate the helpful replies.
    I have 9 calls to XmlAgg() that use ORDER BY.
  • 8. Re: Examples of large SELECT XmlElement - is Oracle support robust?
    975554 Newbie
    Currently Being Moderated
    What version of the database are you on? I am on 11.2.0.3.
    I too have XML queries having large number of xmlagg functions (the most is 30). My problem is not size(as long as I keep the OS file below 700mb) it is speed on a unicode database. I also have used views to limit the number of joins and the size of the where clause which makes the XML query much easier to read.
  • 9. Re: Examples of large SELECT XmlElement - is Oracle support robust?
    811419 Newbie
    Currently Being Moderated
    Hi, thanks for your posts.
    We're on Oracle 10.2.0.5 Patch 20 x64.
  • 10. Re: Examples of large SELECT XmlElement - is Oracle support robust?
    811419 Newbie
    Currently Being Moderated
    Oracle continues to stand behind their SQL/XML technology, but I did not hear from many who are actually using it for large projects.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points