9 Replies Latest reply: Jul 10, 2013 2:06 PM by Marco Gralike RSS

    XMLTYPE.createxml - how to free memory?

    afd
      I use XMLTYPE.createxml to generate XML and my PGA memory use skyrockets. How can I free it?
      SQL> select * from v$version;
      
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE    11.2.0.1.0      Production
      TNS for Linux: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
      
      SQL> select  n.NAME, t.VALUE
        2    from v$mystat t, v$statname n
        3   where t.STATISTIC# = n.STATISTIC#
        4     and n.NAME like 'session%memory%'
        5   order by name;
      
      NAME                                                                  VALUE
      ---------------------------------------------------------------- ----------
      session pga memory                                                  2236760
      session pga memory max                                              2236760
      session uga memory                                                  1729280
      session uga memory max                                              1729280
      
      SQL> declare
        2    v_ref_cur sys_refcursor;
        3    v_xmltypebuf XMLTYPE;
        4  begin
        5    for i in 1..10000 loop
        6       open v_ref_cur for select * from dual ;
        7       v_xmltypebuf := XMLTYPE.createxml(v_ref_cur);
        8       close v_ref_cur;
        9    end loop;
       10  end;
       11  /
      
      PL/SQL procedure successfully completed.
      
      SQL> select  n.NAME, t.VALUE
        2    from v$mystat t, v$statname n
        3   where t.STATISTIC# = n.STATISTIC#
        4     and n.NAME like 'session%memory%'
        5   order by name;
      
      NAME                                                                  VALUE
      ---------------------------------------------------------------- ----------
      session pga memory                                                101458264
      session pga memory max                                            101458264
      session uga memory                                                 99764800
      session uga memory max                                             99764800
      Thank you in advance,
      Tony
        • 1. Re: XMLTYPE.createxml - how to free memory?
          Jason_(A_Non)
          What happens when you run your script back to back? I modified your script a bit to just focus on the XMLTYPE() itself. I ran the setup three times, with about 1 second between runs. You will noticed that memory usage drops between between run 1 and 2 and 2 and 3. (I added run markers after the fact).
          Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
           
          SQL> -- Run 1
          SQL> select n.NAME, t.VALUE
            2    from v$mystat t, v$statname n
            3   where t.STATISTIC# = n.STATISTIC#
            4     and n.NAME like 'session%memory%'
            5   order by name;
           
          NAME                                                                  VALUE
          ---------------------------------------------------------------- ----------
          session pga memory                                                  1767576
          session pga memory max                                              1767576
          session uga memory                                                  1362360
          session uga memory max                                              1362360
          SQL> declare
            2     v_xmltypebuf XMLTYPE;
            3  begin
            4     for i in 1..10000 loop
            5        v_xmltypebuf := XMLTYPE('<root/>');
            6     end loop;
            7  end;
            8  /
           
          PL/SQL procedure successfully completed
          SQL> select n.NAME, t.VALUE
            2    from v$mystat t, v$statname n
            3   where t.STATISTIC# = n.STATISTIC#
            4     and n.NAME like 'session%memory%'
            5   order by name;
           
          NAME                                                                  VALUE
          ---------------------------------------------------------------- ----------
          session pga memory                                                  2554008
          session pga memory max                                              2554008
          session uga memory                                                  1493396
          session uga memory max                                              1493396
           
          SQL> -- Run 2 (notice drop in memory values from query immediately above)
          SQL> select n.NAME, t.VALUE
            2    from v$mystat t, v$statname n
            3   where t.STATISTIC# = n.STATISTIC#
            4     and n.NAME like 'session%memory%'
            5   order by name;
           
          NAME                                                                  VALUE
          ---------------------------------------------------------------- ----------
          session pga memory                                                  1964184
          session pga memory max                                              2685080
          session uga memory                                                  1493396
          session uga memory max                                              1624432
          SQL> declare
            2     v_xmltypebuf XMLTYPE;
            3  begin
            4     for i in 1..10000 loop
            5        v_xmltypebuf := XMLTYPE('<root/>');
            6     end loop;
            7  end;
            8  /
           
          PL/SQL procedure successfully completed
          SQL> select n.NAME, t.VALUE
            2    from v$mystat t, v$statname n
            3   where t.STATISTIC# = n.STATISTIC#
            4     and n.NAME like 'session%memory%'
            5   order by name;
           
          NAME                                                                  VALUE
          ---------------------------------------------------------------- ----------
          session pga memory                                                  2226328
          session pga memory max                                              2685080
          session uga memory                                                  1493396
          session uga memory max                                              1624432
           
          SQL> -- Run 3 (notice drop in memory values from query immediately above)
          SQL> select n.NAME, t.VALUE
            2    from v$mystat t, v$statname n
            3   where t.STATISTIC# = n.STATISTIC#
            4     and n.NAME like 'session%memory%'
            5   order by name;
           
          NAME                                                                  VALUE
          ---------------------------------------------------------------- ----------
          session pga memory                                                  1964184
          session pga memory max                                              2685080
          session uga memory                                                  1493396
          session uga memory max                                              1624432
          SQL> declare
            2     v_xmltypebuf XMLTYPE;
            3  begin
            4     for i in 1..10000 loop
            5        v_xmltypebuf := XMLTYPE('<root/>');
            6     end loop;
            7  end;
            8  /
           
          PL/SQL procedure successfully completed
          SQL> select n.NAME, t.VALUE
            2    from v$mystat t, v$statname n
            3   where t.STATISTIC# = n.STATISTIC#
            4     and n.NAME like 'session%memory%'
            5   order by name;
           
          NAME                                                                  VALUE
          ---------------------------------------------------------------- ----------
          session pga memory                                                  2226328
          session pga memory max                                              2685080
          session uga memory                                                  1493396
          session uga memory max                                              1624432
          So Oracle does appear to be freeing up memory, just not the split second the script finishes.
          • 2. Re: XMLTYPE.createxml - how to free memory?
            Marco Gralike
            Have a go with it J. using DBMS_SESSION.FREE_UNUSED_USER_MEMORY as well combined with fragments. Can't test it myself (01:30 AM CET).

            M.
            • 3. Re: XMLTYPE.createxml - how to free memory?
              afd
              A_Non wrote:
              What happens when you run your script back to back? I modified your script a bit to just focus on the XMLTYPE() itself. I ran the setup three times, with about 1 second between runs. You will noticed that memory usage drops between between run 1 and 2 and 2 and 3. (I added run markers after the fact).
              If I try the XMLTYPE constructor XMLTYPE('<root/>'), no memory leak occurs.
              But I need to generate the XMLTYPE from a REF CURSOR. And if I repeteadly run my script with XMLTYPE.createxml(v_ref_cur), memory use grows each time and eventually I receive the following error "ORA-21780: Maximum number of object durations exceeded".

              I tried dbms_session.free_unused_server_memory but it didn't help.
              I solved my problem by switching from XMLTYPE.createxml to DBMS_XMLGEN.getxmltype; see below:
              SQL> select n.NAME, t.VALUE
                2    from v$mystat t, v$statname n
                3   where t.STATISTIC# = n.STATISTIC#
                4     and n.NAME like 'session%memory%'
                5   order by name;
              
              NAME                                                                  VALUE
              ---------------------------------------------------------------- ----------
              session pga memory                                                  2433368
              session pga memory max                                              2433368
              session uga memory                                                  1598304
              session uga memory max                                              1598304
              
              SQL> declare
                2    v_ref_cur   sys_refcursor;
                3    v_ctxHandle dbms_xmlgen.ctxType;
                4    v_xmltypebuf XMLTYPE;
                5  begin
                6    for i in 1 .. 10000 loop
                7      open v_ref_cur for
                8          select * from dual;
                9
               10      v_ctxHandle := DBMS_XMLGEN.NEWCONTEXT(queryString => v_ref_cur);
               11      v_xmltypebuf := dbms_xmlgen.getXMLType(ctx => v_ctxHandle);
               12
               13      dbms_xmlgen.closeContext(ctx => v_ctxHandle);
               14      close v_ref_cur;
               15    end loop;
               16  end;
               17  /
              
              PL/SQL procedure successfully completed.
              
              SQL> select n.NAME, t.VALUE
                2    from v$mystat t, v$statname n
                3   where t.STATISTIC# = n.STATISTIC#
                4     and n.NAME like 'session%memory%'
                5   order by name;
              
              NAME                                                                  VALUE
              ---------------------------------------------------------------- ----------
              session pga memory                                                  2629976
              session pga memory max                                              2629976
              session uga memory                                                  1991232
              session uga memory max                                              1991232
              
              SQL> declare
                2    v_ref_cur   sys_refcursor;
                3    v_ctxHandle dbms_xmlgen.ctxType;
                4    v_xmltypebuf XMLTYPE;
                5  begin
                6    for i in 1 .. 10000 loop
                7      open v_ref_cur for
                8          select * from dual;
                9
               10      v_ctxHandle := DBMS_XMLGEN.NEWCONTEXT(queryString => v_ref_cur);
               11      v_xmltypebuf := dbms_xmlgen.getXMLType(ctx => v_ctxHandle);
               12
               13      dbms_xmlgen.closeContext(ctx => v_ctxHandle);
               14      close v_ref_cur;
               15    end loop;
               16  end;
               17  /
              
              PL/SQL procedure successfully completed.
              
              SQL> select n.NAME, t.VALUE
                2    from v$mystat t, v$statname n
                3   where t.STATISTIC# = n.STATISTIC#
                4     and n.NAME like 'session%memory%'
                5   order by name;
              
              NAME                                                                  VALUE
              ---------------------------------------------------------------- ----------
              session pga memory                                                  2629976
              session pga memory max                                              2761048
              session uga memory                                                  1991232
              session uga memory max                                              2114744
              Thanks,
              Tony
              • 4. Re: XMLTYPE.createxml - how to free memory?
                Jason_(A_Non)
                Given you are on 11.2, I would open a bug with Oracle regarding this issue. It should not be leaking memory like this and you have a nice simple test case to work from. I tried some other variants including using DBMS_SESSION.FREE_UNUSED_USER_MEMORY; and none of them leaked memory in the first place. Here, for all to see, is your example using DBMS_SESSION.FREE_UNUSED_USER_MEMORY; and showing no change in memory used.
                Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
                 
                SQL> 
                SQL> select n.NAME, t.VALUE
                  2    from v$mystat t, v$statname n
                  3   where t.STATISTIC# = n.STATISTIC#
                  4     and n.NAME like 'session%memory%'
                  5   order by name;
                 
                NAME                                                                  VALUE
                ---------------------------------------------------------------- ----------
                session pga memory                                                  1767576
                session pga memory max                                              1767576
                session uga memory                                                  1362360
                session uga memory max                                              1362360
                SQL> declare
                  2     v_ref_cur sys_refcursor;
                  3     v_xmltypebuf XMLTYPE;
                  4  begin
                  5     for i in 1..10000 loop
                  6         open v_ref_cur for select * from dual ;
                  7         v_xmltypebuf := XMLTYPE.createxml(v_ref_cur);
                  8         close v_ref_cur;
                  9     end loop;
                 10  end;
                 11  /
                 
                PL/SQL procedure successfully completed
                SQL> select n.NAME, t.VALUE
                  2    from v$mystat t, v$statname n
                  3   where t.STATISTIC# = n.STATISTIC#
                  4     and n.NAME like 'session%memory%'
                  5   order by name;
                 
                NAME                                                                  VALUE
                ---------------------------------------------------------------- ----------
                session pga memory                                                122550424
                session pga memory max                                            122550424
                session uga memory                                                121357872
                session uga memory max                                            121357872
                 
                SQL> 
                SQL> select n.NAME, t.VALUE
                  2    from v$mystat t, v$statname n
                  3   where t.STATISTIC# = n.STATISTIC#
                  4     and n.NAME like 'session%memory%'
                  5   order by name;
                 
                NAME                                                                  VALUE
                ---------------------------------------------------------------- ----------
                session pga memory                                                122550424
                session pga memory max                                            122681496
                session uga memory                                                121357872
                session uga memory max                                            121481324
                 
                SQL> -- Several minutes later
                SQL> /
                 
                NAME                                                                  VALUE
                ---------------------------------------------------------------- ----------
                session pga memory                                                121895064
                session pga memory max                                            122681496
                session uga memory                                                121292408
                session uga memory max                                            121481324
                 
                SQL> begin
                  2     DBMS_SESSION.FREE_UNUSED_USER_MEMORY;
                  3  end;
                  4  /
                 
                PL/SQL procedure successfully completed
                 
                SQL> 
                SQL> select n.NAME, t.VALUE
                  2    from v$mystat t, v$statname n
                  3   where t.STATISTIC# = n.STATISTIC#
                  4     and n.NAME like 'session%memory%'
                  5   order by name;
                 
                NAME                                                                  VALUE
                ---------------------------------------------------------------- ----------
                session pga memory                                                121698456
                session pga memory max                                            122681496
                session uga memory                                                121096016
                session uga memory max                                            121481324
                So I went on to testing alternatives using your structure to verify where the leak was and what could be causing it.
                1) Using just
                v_xmltypebuf := XMLTYPE(v_ref_cur);
                produced the same results as above
                2) Commenting out that line produces memory results in line with my first sample in the previous post, where memory is freed up shortly after execution is done.
                3) Using again
                v_xmltypebuf := XMLTYPE('<root/>');
                does not produce a memory leak,
                4) Sending in a CLOB CLOB set outside the loop to '<root/>' does not produce a memory leak.
                5) Using ANYDATA uses a lot of memory but that gets freed up automatically
                Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
                 
                SQL> 
                SQL> select n.NAME, t.VALUE
                  2    from v$mystat t, v$statname n
                  3   where t.STATISTIC# = n.STATISTIC#
                  4     and n.NAME like 'session%memory%'
                  5   order by name;
                 
                NAME                                                                  VALUE
                ---------------------------------------------------------------- ----------
                session pga memory                                                  1767576
                session pga memory max                                              1767576
                session uga memory                                                  1362360
                session uga memory max                                              1362360
                SQL> declare
                  2     v_ref_cur sys_refcursor;
                  3     v_xmltypebuf XMLTYPE;
                  4  begin
                  5     for i in 1..10000 loop
                  6         open v_ref_cur for select * from dual ;
                  7         v_xmltypebuf := XMLTYPE(anydata.convertVarchar2('root'));
                  8         close v_ref_cur;
                  9     end loop;
                 10  end;
                 11  /
                 
                PL/SQL procedure successfully completed
                SQL> select n.NAME, t.VALUE
                  2    from v$mystat t, v$statname n
                  3   where t.STATISTIC# = n.STATISTIC#
                  4     and n.NAME like 'session%memory%'
                  5   order by name;
                 
                NAME                                                                  VALUE
                ---------------------------------------------------------------- ----------
                session pga memory                                                 90831000
                session pga memory max                                             90831000
                session uga memory                                                 89477120
                session uga memory max                                             89477120
                 
                SQL> /  -- one minute later
                 
                NAME                                                                  VALUE
                ---------------------------------------------------------------- ----------
                session pga memory                                                  2095256
                session pga memory max                                             90962072
                session uga memory                                                  1624216
                session uga memory max                                             89600572
                I don't have everything setup to test a BFILENAME alternative yet but I'll try to get that setup done and update once done.
                Marco, see anything I missed?
                • 5. Re: XMLTYPE.createxml - how to free memory?
                  afd
                  Thank you for your impressive testing!

                  Yes, I'll file a bug for this one, when I'll be sure that I'm not missing some obvious "free resource" technique
                  • 6. Re: XMLTYPE.createxml - how to free memory?
                    Pollocks01

                    Any update/resolution to this?

                    We're getting Out of Memory errors in production and closer inspection reveals that for our XML Loader process, the session's PGA steadily increases to over 4Gb when we typically run out of memory.

                    We do have an SR open with Oracle.

                    My investigation - like the one here - essentially saw that wherever we use the XMLTYPE object in a loop (e.g. processing through a bunch of files) - then memory just grows until we either run out of space or the process completes.

                    To workaround, I'm thinking we're going to have to limit the life of the process to, say, 100 files - or better still - terminate the loop when we detect that we're using a given amount of PGA.

                    I can't post my test harness (that I built for Oracle) here as it's kind of meaningless without the suite of test files (which contain customer data).

                    Essentially, I need a way to free the XMLTYPE memory structures.

                    • 7. Re: XMLTYPE.createxml - how to free memory?
                      Marco Gralike

                      Would be interested in Oracle's follow up via your SR. That said, database version in use was...?

                       

                      Had something simular with a bug, I think in XDK/JDBC code a short while ago, and used as a workaround, to reconnect the session after x amount of records inserted in my table. It was not ideal, but it worked.

                      • 8. Re: XMLTYPE.createxml - how to free memory?
                        Pollocks01
                        Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                        PL/SQL Release 11.2.0.3.0 - Production
                        CORE 11.2.0.3.0 Production
                        TNS for Linux: Version 11.2.0.3.0 - Production
                        NLSRTL Version 11.2.0.3.0 - Production
                        
                        

                         

                        Hi Marco,

                         

                        I'm gonna workaround in a similar way. For me, it's 100% pl/sql, no sdks. I'm going to check my PGA every so often and bail when it gets too big:

                         

                        create or replace function f_get_my_pga return number
                        is
                          v_pga number;
                        begin
                          
                          select ss.value into v_pga
                          from   v$sesstat ss, v$statname sn
                           where ss.sid = sys_context('USERENV', 'SID')   
                           and   sn.statistic# = ss.statistic#
                           and   sn.name = 'session pga memory';
                        
                        
                          return v_pga;
                        exception when NO_DATA_FOUND then
                          return null;
                          when others then
                          raise;
                        end  f_get_my_pga;
                        /    
                        

                         

                         

                        For me, the size of the XML files just varies too much to quite after X files (no pun intended!).

                         

                        In my dev VM (1.5Gb RAM), I have memory_target_max set to 650Mb and see that once PGA gets to around 200Mb, then Oracle's actually de-allocating and re-allocating as needed, trying to keep it at around 200Mb. INTERESTING! In prod, however, which has 32Gb physical memory and 25Gb memory_target_max, the PGA keeps increasing until it blows with out of memory, typically at around 4Gb PGA for the session. The 4Gb represents the sum of all other active sessions, so it's pretty big. I don't know why it's not de-allocating/re-allocating as I see in my VM.

                         

                        I'll update back here with Oracle support's input.

                         

                        -Pollocks01

                        • 9. Re: XMLTYPE.createxml - how to free memory?
                          Marco Gralike

                          > For me, the size of the XML files just varies too much to quite after X files (no pun intended!).

                           

                          No worries.

                           

                          My workaround was used in an environment where I had no control over the sizes of the different XML as well, which varied from x Kb to xx Mb's. Testing it a bit and checking my OS behavior (page in/out, memory), I ended up with a PGA of approx 4 Gb. That said I did not use max_memory_target (don't like it) but sga_target combined with HugePages  (big fan nowadays) memory management.

                           

                          http://www.oracle-base.com/articles/linux/configuring-huge-pages-for-oracle-on-linux-64.php

                           

                          Configuring Linux Hugepages for Oracle Database Is Just Too Difficult! Isn&amp;#8217;t It? Part &amp;#8211; I. | Kevin …