-1

We have got a client that wants a large amount of data in XML format. The Xml is roughly like so :

Employee 
 |- Emp Criminal History
 |- Emp Address
    |- Address history  
 |- Emp Partner 
    |- Partner history    

This is an overly simplified structure and the actual structure is a few more levels nested. The usual size of the files with xml for a year is about 100 MB

With the archaic system we have in place, the tomcat (web server) and database (oracle) live on different servers. The procedure in oracle takes a from/to date date and spits out a clob which is formed by using Oracle's API to create xml. Eg :

XMLSERIALIZE (
    XMLELEMENT( 
      XMLCONCAT(
       XMLAGG(

To write this data out to disk on the server oracle is on, takes a large amount of time. With the architecture I am bound by, I need a java class to make a call to this proc and then from the clob this proc returns, parse each line so I can simply write it to an xml file. I ran a report for 6 months and that contained about 5 million lines of xml which took about 7 hours to run (just to write data to disk on the oracle server). I did run it overnight via a java class and this morning I had a

java.lang.OutOfMemoryError: Java heap space

I am obviously missing something.

What are the things I could follow to get my run time to reasonable levels given the queries are optimally performing ? I have no idea on the benchmarks for Oracle API - I am assuming since they have been around for a while, the xml api isn't an issue.

Java: 1.5. Oracle: 11.2

  • "With the architecture I am bound by, I need a java class to make a call to this proc" - how "bound" means "bound"? Are you allowed to bypass the existing procedure, or write a new, optimized one? You already have a bottleneck here, if you are not allowed to optimize it, I fear we cannot help you. Moreover, we cannot help you to fix your out-of-memory issues with your Java class (debugging help is off-topic for this site, and even it it would be on-topic, one cannot debug something without knowing the code). – Doc Brown Nov 21 '18 at 7:31
  • 1
    With performance issues the devil is always in the detail and it's hard to provide enough detail in a SO question to allow for meaningful answers. However, it should be possible to process a 100Mb file sequentially in 10 seconds or so, not the 7 hours you are seeing. The memory problem suggests you are trying to load the whole data set into memory, which is not out of the question for 100Mb if you do it efficiently, but I can't see why it's necessary for this task. – Michael Kay Nov 21 '18 at 8:26
  • @DocBrown I wrote the procedure and the class myself. By bound I meant, I cannot make my web app point to a folder on the oracle server to allow for the final file's download -and it has to be in this kind of a setup. The OOM issues are a symptom of a deeper problem, the cause for which I dont know. I can increase the heap size - but that wouldn't be the correct solution. – happybuddha Nov 21 '18 at 9:36
  • @MichaelKay I think what I am after is advice, if some one else can obviously see what I am doing wrong here. I could not find any performance benchmark data on the oracle XML apis. So I was hoping someone would imagine this solution of mine to still be on a whiteboard and point out things that could help with performance? – happybuddha Nov 21 '18 at 9:38
  • 1
    @DocBrown Nonsense, I frequently process 100Mb of XML in a few seconds. If it takes 7 hours then you are doing something badly wrong. – Michael Kay Nov 22 '18 at 12:09

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.