Re: XMLPARSER PGA usage question

  • From: Stefan Knecht <knecht.stefan@xxxxxxxxx>
  • To: christopherdtaylor1994@xxxxxxxxx
  • Date: Mon, 16 Feb 2015 23:07:14 +0700

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
>

Other related posts: