10 Replies Latest reply: Apr 14, 2012 4:11 AM by odie_63 RSS

    ORA-04030: out of process memory when trying to allocate 1048 bytes

    Gaff
      Hi:

      I'm posting this in the SQL forum even though the bulk of the procedure is using the DBMS_XML related packages.

      I get the error mentioned in the title when trying to transform a large XML source iteratively. What I'm doing is getting a small (a few K) XML fragment in a clob nd repeatedly passing it to a procedure to transform that XML into another format of XML. I'm writing the results of each "chunk" out to a table as a append it to a column that is being updated (pretty standard).

      My problem is that I can do so many iterations before running out of memory so I'm guessing I have a leak somewhere but I don't know where. I can get about 2,000 iterations (resulting in a 170M clob) but I would think I should be able to get multiples of that if I don't have a leak. I even run out of memory if I don't append the results to the updated column, so the leak has to do with the CLOBs I'm working with I guess. I'd appreciate anyone pointing out what I am forgetting to free here.

      I've seen references about increasing the PGA_AGGREGATE_TARGET to fix this problem. I've done that but it didn't help. In any case, I shouldn't hit this problem at all if I'm releasing memory properly.

      I've included the code below.

      Thanks
      CREATE OR REPLACE PACKAGE GAFF.FOO_PCK AS
      
      PROCEDURE TransformXMLClob(fragment IN CLOB, xsldoc IN CLOB, transformedFragment IN OUT NOCOPY CLOB) ;
      PROCEDURE TEST;
      END FOO_PCK;
      The package body
      CREATE OR REPLACE PACKAGE BODY GAFF.FOO_PCK AS
      
      
      -- fragment - Clob containing an XML fragment (it isn't an XML document)
      -- xsldoc   - Clob containing the XSL stylesheet to use in the transformation
      -- transformedFragment - Clob that contains the transformed XML fragment.
      
      PROCEDURE transformXMLClob(fragment IN CLOB, xsldoc IN CLOB,
                                  transformedFragment IN OUT NOCOPY CLOB) IS 
       
         indoc        CLOB := empty_clob;    -- XMLFragament to transform, CLOB from
         
         myParser     DBMS_XMLPARSER.Parser;
         indomdoc     DBMS_XMLDOM.domdocument;
         xsltdomdoc   DBMS_XMLDOM.domdocument;
         xsl          DBMS_XSLPROCESSOR.stylesheet;
         outdomdocf   DBMS_XMLDOM.domdocumentfragment;
         outnode      DBMS_XMLDOM.domnode;
         proc         DBMS_XSLPROCESSOR.processor;
         buf          CLOB := empty_clob;
         i            NUMBER;
         
         rootBeginTag CLOB := '<?xml version="1.0" encoding="UTF-8"?><root>';
         rootEndTag   CLOB := '</root>';
      
      BEGIN
      
      
      DBMS_LOB.createTemporary (indoc, TRUE);
      
          if( DBMS_LOB.isopen(indoc) != 1) then
             dbms_lob.open( indoc, dbms_lob.lob_readwrite );
          end if ;
      
         indoc := rootBeginTag || fragment || rootEndTag;
      
      
         DBMS_LOB.createTemporary (buf, TRUE);
      
         myParser := DBMS_XMLPARSER.newParser;
         DBMS_XMLPARSER.parseClob (myParser, indoc);
         indomdoc := DBMS_XMLPARSER.getDocument (myParser);
      
         -- Don't transform here.  Feed one node at a time to parser later.
         DBMS_XMLPARSER.parseClob (myParser, xsldoc);
         xsltdomdoc := DBMS_XMLPARSER.getDocument (myParser);
         xsl := DBMS_XSLPROCESSOR.newstylesheet (xsltdomdoc, '');
         proc := DBMS_XSLPROCESSOR.newProcessor;
      
         --apply stylesheet to DOM document
      
         outdomdocf := DBMS_XSLPROCESSOR.processxsl (proc, xsl, indomdoc);
         outnode := DBMS_XMLDOM.makenode (outdomdocf);
      
         -- PL/SQL DOM API for XMLType can be used here
      
         DBMS_XMLDOM.writetoClob (outnode, buf);
         --buf := '<?xml version="1.0" encoding="UTF-8"?>' || buf;
         
         transformedFragment := buf;
      
         --dbms_output.put_line(substr(buf,1,3000));
      
         DBMS_XMLPARSER.freeParser (myParser);
         DBMS_XSLPROCESSOR.freeProcessor (proc);
      
         if( DBMS_LOB.isopen(indoc) = 1) then
             dbms_lob.close( indoc);
          end if ; 
         DBMS_LOB.FreeTemporary (indoc);
         
       
         
         if( DBMS_LOB.isopen(buf) = 1) then
             dbms_lob.close( buf);
          end if ;
         DBMS_LOB.FreeTemporary (buf); 
         
         
         if( DBMS_LOB.isopen(rootBeginTag) = 1) then
             dbms_lob.close(rootBeginTag);
          end if ;
         DBMS_LOB.FreeTemporary (rootBeginTag); 
      
         
         if( DBMS_LOB.isopen(rootEndTag) = 1) then
             dbms_lob.close(rootEndTag);
          end if ;
         DBMS_LOB.FreeTemporary (rootEndTag);       
      
      
      END transformXMLClob;
      
      
      PROCEDURE TEST IS
         xsl             CLOB ; -- := empty_Clob();
         indoc1          CLOB ; -- := empty_Clob();
         indoc2          CLOB ; -- := empty_Clob();
         transformedFragment CLOB;
         transformedDocument CLOB;
         tmpClob         CLOB;
         
         i               INTEGER;
         fragmentStart   INTEGER;
         fragmentEnd     INTEGER;
      
         numNodes        INTEGER;
         checkEnd        varchar2(1000);
         
      BEGIN
         -- Get the stylesheet.
         SELECT   col1 INTO xsl FROM xsl_tab2;
         
         SELECT   file_content
           INTO   indoc1
           FROM   xml_data_template
          WHERE   my_key = 15364;
          
         fragmentStart := instr(indoc1,'<root ');
         fragmentEnd := instr(indoc1,'</root>');
      
      
         indoc2 := substr(indoc1,fragmentStart,(length(indoc1) - 
                          (fragmentStart + length('</root>'))) );
                          
       
         insert into xml_data_template
                ( my_key,
                  file_path,
                  file_type,
                  file_content )
         values ( 1000,
                     'big/clob/test',
                     'EXPORT',
                     EMPTY_CLOB() );
         commit;
      
         SELECT file_content 
         INTO transformedDocument 
         FROM xml_data_template
         WHERE my_key = 1000 FOR UPDATE;
      
         DBMS_LOB.OPEN (transformedDocument,DBMS_LOB.LOB_READWRITE);
                    
         /* SCV2 docs are enclosed in a <RecordSet/> element.  */
         
         DBMS_LOB.append(transformedDocument,'<?xml version="1.0" encoding="UTF-8"?>' );
         
         
         for i in 1..5000 LOOP
         
           FOO_PCK.TransformXMLClob(indoc2, xsl, transformedFragment);
              DBMS_LOB.append(transformedDocument,transformedFragment);
         
           
          if( DBMS_LOB.isopen(transformedFragment) = 1) then
             dbms_lob.close( transformedFragment);
          end if ;
          
         DBMS_LOB.FreeTemporary (transformedFragment); 
      
         --transformedFragment := empty_clob;
         end LOOP;
         
      
         tmpClob := '</RecordSet>';
         --transformedFragment := tmpClob;
         
         --dbms_lob.append(transformedDocument, tmpClob);
                 
          if (DBMS_LOB.ISOPEN ( transformedDocument ) = 1 ) then
            DBMS_LOB.CLOSE ( transformedDocument );
          end if; 
        
          if (DBMS_LOB.ISOPEN ( tmpClob ) = 1 ) then
            DBMS_LOB.CLOSE ( tmpClob );
          end if; 
      
          if (DBMS_LOB.ISOPEN ( indoc1 ) = 1 ) then
            DBMS_LOB.CLOSE ( indoc1 );
          end if; 
      
          if (DBMS_LOB.ISOPEN ( indoc2 ) = 1 ) then
            DBMS_LOB.CLOSE ( indoc2 );
          end if; 
          
          if (DBMS_LOB.ISOPEN ( xsl ) = 1 ) then
            DBMS_LOB.CLOSE ( xsl );
          end if; 
                    
         COMMIT;
         
         tmpClob := empty_clob;
         
         if (DBMS_LOB.ISOPEN ( transformedDocument ) = 1 ) then
            DBMS_LOB.CLOSE ( transformedDocument );
         end if;
         
         
         EXCEPTION
                 WHEN OTHERS THEN
                 dbms_output.put_line('EXCEPTION!' || SQLERRM);
                  if (DBMS_LOB.ISOPEN ( transformedDocument ) = 1 ) then
                    DBMS_LOB.CLOSE ( transformedDocument );
                  end if; 
                 commit;
         
      END TEST;
      
      END FOO_PCK;
      /
        • 1. Re: ORA-04030: out of process memory when trying to allocate 1048 bytes
          rp0428
          >
          I get the error mentioned in the title when trying to transform a large XML source iteratively.
          >
          You aren't showing any execution or error happening. All you did was post a lot of code and say 'I have a problem'.

          Oracle provides very specific information about where errors occur.

          Instrument your code with some DBMS_OUTPUT.PUT_LINE statements to show what code is executing, what code has problems and the sizes of the lobs that are being used and created so you can see them grow with each iteration.
          • 2. Re: ORA-04030: out of process memory when trying to allocate 1048 bytes
            odie_63
            Hi,

            That's a lot of stuff just to perform an XSL transformation.

            I'd appreciate anyone pointing out what I am forgetting to free here.
            I don't see any call to DBMS_XSLPROCESSOR.freeStylesheet.

            Anyway, there's also a lot of overhead introduced by passing around CLOB fragment and building DOM out of it.
            You should also create the stylesheet and processor objects only once, outside of the loop, and reuse the handles within each iteration.


            BTW, did you try the few methods suggested recently in the XML DB forum?

            You might be interested in reading this too : http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb03usg.htm#BABDIDHA
            (specifically paragraphs about lazy DOM and the advantage of performing the transformation directly on a schema-based XMLType column)
            • 3. Re: ORA-04030: out of process memory when trying to allocate 1048 bytes
              Gaff
              I've been watching that in the debugger and don't see anything growing unreasonably. I posted the code because my guess is what is happening is that somewhere I have a "leak" in that a clob is being assigned to another clob invoking a copy that I don't intend to do. So my guess is that the "leak" is that I am losing handles to clobs and can therefore never free them. I provided the code so another set of eyes might identify where that is happening.

              This is what I get for an error.

              ERROR at line 1:
              ORA-04030: out of process memory when trying to allocate 2520 bytes (session
              heap,koh-kghu session heap)
              ORA-04030: out of process memory when trying to allocate 1032 bytes (qmxlu
              subheap,qmemNextBuf:alloc)

              Thanks.
              • 4. Re: ORA-04030: out of process memory when trying to allocate 1048 bytes
                Gaff
                Thanks Odie.

                You're right. It is a lot of stuff! Maybe there's a better way, but here's the situation. I currently have a legacy procedure that is writing out "XML" by doing a query and it builds up the XML string one line at a time with dbms_output.put_line :) I DID NOT WRITE THIS! It writes the "XML" export out that is currently used and it's kept in a CLOB column. The XML Schema for that export has now changed so rather than go into that code with all the dbms_output commands I'm trying to just transform what we currently produce to the new format with XSL.

                The current export is in a CLOB. I suppose I could get it into an XMLType column and try to work with that. I had read about the "virtual DOM" earlier but didn't know how to use it. This approach looks like it uses that. I'll look into this method.

                What is the path relative to in this command?
                SELECT
                  XMLtransform(
                    OBJECT_VALUE, 
                    XDBURIType('/source/schemas/poSource/xsl/purchaseOrder.xsl').getXML()).extract('/*')
                  FROM purchaseorder
                  WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]')=1;
                 
                XMLTRANSFORM(OBJECT_VALUE,  
                             XDBURITYPE('/SOURCE/SCHEMAS/POSOURCE/XSL/PURCHASEORDER.XSL').GETXML())
                Thanks for the link.
                • 5. Re: ORA-04030: out of process memory when trying to allocate 1048 bytes
                  odie_63
                  What is the path relative to in this command?
                  You mean in XDBUriType?
                  It's an absolute path in the XML DB repository.

                  But you can pass the stylesheet from wherever you want : variable, database column, subquery, repository resource etc.
                  The important point is to work on a stored XMLType instance.
                  • 6. Re: ORA-04030: out of process memory when trying to allocate 1048 bytes
                    Gaff
                    Thanks.


                    The other concern I had was the XML Schema. Do I need to register the schema (the one for the current XML, the new XML? Both?). I don't have one for the old XML (but I think there is an Oracle call to create one, isn't there?). The one for the new XML is a bit complex and includes two other XML schema files. I was a bit leery of getting that to work in Oracle. Is it required to register the XML Schema to do the transform?

                    Thanks
                    • 7. Re: ORA-04030: out of process memory when trying to allocate 1048 bytes
                      odie_63
                      You would need the schema for the current XML.
                      I don't have one for the old XML (but I think there is an Oracle call to create one, isn't there?).
                      That's unfortunate.
                      The only built-in available is DBMS_XMLSCHEMA.generateSchema, but it only works on preexisting SQL object types representing the XML structure.
                      However a lot of 3rd-party tools can do it, even online.
                      Is it required to register the XML Schema to do the transform?
                      No, but better optimization can take place when the transformation operates on a schema-based instance.
                      • 8. Re: ORA-04030: out of process memory when trying to allocate 1048 bytes
                        Gaff
                        I just tried that method with a few of the current XML Clobs and it worked! I didn't need to register any of the schemas or anything. I'm going to generate a large data file now and see what it does. I think the point of the virtual DOM is that it doesn't need to parse anything and that's what the schema would be used for I would think. This looks like it basically gives me the ability to (stream?) in a large clob and get it transformed almost like a SAX parser, which is good.

                        I'm sure I would need the schema if I wanted to validate the new XML though.

                        Thank you very much for your help!

                        Edited by: Gaff on Apr 12, 2012 6:00 PM
                        • 9. Re: ORA-04030: out of process memory when trying to allocate 1048 bytes
                          Gaff
                          Well, I checked this method out with a larger (216M) file and I got "ORA-31186: Document contains too many nodes". I recall looking at Transform a week or so ago and couldn't remember why I got away from it and started writing my own package. I guess this was the reason.

                          I was thinking this would use the Vritural DOM and not hit this problem, so I'm confused as to why this error is occurring. I have to say, 216M doesn't seem very large, this is rather disappointing.

                          So how does one transform a "large" XML file/clob in PL/SQL and not hit this problem? This is ridiculous.

                          -------
                          Perhaps it isn't capable of using the Virtual DOM because I have not registered a schema?

                          To that end, it looks like Oracle can generate a schema for a given table Generate XML Schema from oracle tables thread] So could I possibly get the contents of my XMLTYPE data back as a table (XMLTABLE()) and use that as the "table" to generate an XML Schema?



                          Gaff

                          Edited by: Gaff on Apr 13, 2012 11:55 AM
                          • 10. Re: ORA-04030: out of process memory when trying to allocate 1048 bytes
                            odie_63
                            To that end, it looks like Oracle can generate a schema for a given table Generate XML Schema from oracle tables thread] So could I possibly get the contents of my XMLTYPE data back as a table (XMLTABLE()) and use that as the "table" to generate an XML Schema?
                            Not really.
                            The query given by Mark Drake uses USER_TAB_COLS dictionary view to retrieve the table metadata.
                            I won't help you since it generates a canonical XML schema (basic ROWSET/ROW structure).

                            If the structure is not complex, you can build the schema "manually", it's not that hard.

                            I'd like to help and test some options but I would need a test case :
                            - a sample input XML (doesn't have to be the big one but a representative sample I could extrapolate)
                            - the XSLT stylesheet