Re: XMLPARSER PGA usage question

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Mon, 16 Feb 2015 23:07:14 +0700
Message-ID: <CAP50yQ8syrbPgnRnZyxseeGU48Bgn3VN6q3hEDyq9Pa=0KBukg_at_mail.gmail.com>

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_at_gmail.com> 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
>

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

Received on Mon Feb 16 2015 - 17:07:14 CET