Try using xmltable() in a single select, and get rid of the PL/SQL entirely. Also you may want to store your XML in binary XML in securefile LOBs. I'm not sure from your snipped above if that will fit your needs - but it's certainly worth a test. We've done a project recently where that was used to process tons of XML files (though not single files of GB-sizes, but rather GB's of small files, so you mileage may vary). Stefan On Thu, Feb 12, 2015 at 11:37 PM, Chris Taylor < christopherdtaylor1994@xxxxxxxxx> wrote: > Env: Oracle 11.2 on Linux 5 > > Disclaimer: > I don't use in the DB and know very little about it and am posting this to > get some input that I can provide to my developer. > > Background: > The developer is wanting to process an XML file (FilenameRedacted.XML) in > the below PL/SQL block as as proof of concept of something they're wanting > to do. > > Originally we hit an Oracle bug with memory mapping limit at 4GB (BUG: > 11852492 Doc: 1325100.1) so I adjusted the available maps to 16GB from 4GB. > > XML File is 1 GB. > > Observation: > After adjusting memory maps available, the process completed, but used > 5.6GB of PGA memory for a 1 GB xml file. I do not know if this memory > footprint is linear, or near linear, however. (So, I don't know if a 2 GB > xml file would double the PGA usage or nearly double it, or would be some > other number entirely). > > Question: > Looking at the PL/SQL code below, is there a way to reduce the PGA > requirement by making the PL/SQL more efficient in some way? (He used > dba-oracle as a point of reference so I'm sure there's some inefficiencies > here) > > Here's the code: > > set serveroutput on > declare > v_xmlfile clob; > v_is_ok boolean; > indoc varchar2(2000); > myparser dbms_xmlparser.parser; > indomdoc dbms_xmldom.domdocument; > innode dbms_xmldom.domnode; > buf varchar2(2000); > begin > select col2 into v_xmlfile > from tab_xml_parse > where col1 = 'FileName_Redacted.xml'; > v_is_ok := func_check_xml(v_xmlfile); > if v_is_ok then > dbms_output.put_line('The XML document is OK!'); > else > dbms_output.put_line('The XML document is wrong!!'); > end if; > > --Another example inside this block > dbms_output.put_line(a => '#######################'); > dbms_output.put_line(a => '# Another example #'); > indoc := '<car><name>bwm m3</name></car>'; > > -- Construct a parser instance > myparser := dbms_xmlparser.newparser; > > --Parse XML documents > dbms_xmlparser.parsebuffer(p => myparser,doc => indoc); > > --Obtain the DOMDocument interface > indomdoc := dbms_xmlparser.getdocument(p => myparser); > innode := dbms_xmldom.makenode(doc => indomdoc); > dbms_xmldom.writetobuffer(n => innode,buffer => buf); > dbms_output.put_line(a => buf); > dbms_xmldom.freedocument(doc => indomdoc); > dbms_xmlparser.freeparser(p => myparser); > dbms_output.put_line(a => '#######################'); > > end; > / > > Regards, > Chris Taylor >