XMLPARSER PGA usage question

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Thu, 12 Feb 2015 10:37:07 -0600
Message-ID: <CAP79kiRsayLc3UdDvguqU-V1_1H54KJ3VzyArixF2gZMVmT7vQ_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 12 2015 - 17:37:07 CET