Oracle performance problems with large batch of XSL operations

Question

I have a system that is performing many XSL transformations on XMLType objects. The problem is that the system gradually slows down over time, and sometimes crashes when it runs out of memory. It seems that the slow down (and possibly memory crash) is around the dbms_xslprocessor.processXSL function call, which gradually takes longer and longer to complete.

The code looks like this:

v_doc dbms_xmldom.DOMDocument;
v_transformer dbms_xmldom.DOMDocument;
v_XSLprocessor dbms_xslprocessor.Processor;
v_stylesheet dbms_xslprocessor.Stylesheet;
v_clob clob;
...
transformer := PKG_STUFF.getXSL();
v_transformer := dbms_xmldom.newDOMDocument(transformer);
v_XSLprocessor := Dbms_Xslprocessor.newProcessor;
v_stylesheet := dbms_xslprocessor.newStylesheet(v_transformer, '');
...
for source_data in (select id in source_tbl) loop
begin
    v_doc := PKG_CONVERT.convert(in_id => source_data.id);
    --start time of operation
    v_begin_op_time := dbms_utility.get_time;
    --reset the CLOB                    
    v_clob := ' ';
    --Apply XSL Transform
    dbms_xslprocessor.processXSL(p => v_XSLprocessor, ss => v_stylesheet, xmldoc => v_Doc, cl => v_clob);
    v_doc := dbms_xmldom.newDOMDocument(XMLType(v_clob));
    --end time
    v_end_op_time := dbms_utility.get_time; 
    --calculate duration
    v_time_taken  :=  (((v_end_op_time - v_begin_op_time)));
    --log the duration
    PKG_LOG.log_message('Time taken to transform XML: '||v_time_taken);
    ...
    ...
    DBMS_XMLDOM.freeDocument(v_Doc);
    DBMS_LOB.freetemporary(lob_loc => v_clob);
end loop;

The time taken to transform the XML is slowly creeping up (I suppose it might also be the call to dbms_xmldom.newDOMDocument, but I had thought that to be fairly straightforward). I have no idea why.... :(

(Oracle 10g)

UPDATE:

Further testing by simply commenting out the call to processXSL seems to speed things up significantly. Still waiting for larger dataset test to confirm this, but it seems that processXSL is what's really bogging things down. Has anyone else had problems with it before?

We're not transforming many XML documents, only a few thousand right now. The XSL is not exactly simple, but I'm only creating one Processor, one Transformer, and one Stylesheet, and then reusing them over and over and over...

UPDATE 2:

Even without transformations, the memory usage keeps growing. Not nearly bad enough to crash, but it still shouldn't be doing this. Very puzzling.

UPDATE 3:

So... our DBA found references somewhere to the fact that there were known memory leaks in dbms_xmldom and possibly in dbms_xslprocessor. The problems were fixed in newer versions of Oracle, but we do not have those versions, and since there is no immediate plan to upgrade, we have to code around this.

Answer 1

My understanding is that memory use for DOM operations like the XSL processing tends to go as the 4th power of the number of tokens.

We've also seen the XML processing fail because of out of memory errors -- in XSL processing, creating DOM documents, etc.

So I recommend having your DBA's watch memory usage while you're doing the processing and checking how it scales.

In our case, our XSL processing only needed to operate on a relatively small portion of the document at a time. For example, we were extracting data on 100,000 facilities, and the XSL transformed data on a single facility. By processing in groups of 1000 or 5000 and then stitching the final document together, we were able to greatly speed things up and reduce memory usage. Since we were extracting the data from relational tables using dbms_xmlgen.getXMLType() to create the XML document, we were able to use dbms_xmlgen.setMaxRows to set the chunk size.

Some of the XSL processors available in other languages serialize things and trade time and space for memory use. But, as far as I know, the database version wants to bring everything in and process it as a whole.

Answer 2

As I mentioned, it looks like there were known memory problems in the packages we were using, and the version of Oracle I'm using does not have the fixes applied. I found I was able to code around them.

Originally, I was using dbms_xmldom to build up an XML document out of pieces. I replaced this code with SELECT (into ... from dual) statements and the XML functions: xmlelement, xmlconcat, xmlagg, xmlforest.

To perform the transformation, the XMLType API has a transform function which I used instead of the dbms_xslprocessor package. It runs slower, but seems much more consistent (in speed and memory usage) and does not crash the system when it runs out of memory. :)