Oracle performance problems with large batch of XSL operations
- by FrustratedWithFormsDesigner
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)
