This discussion is archived
9 Replies Latest reply: Jul 10, 2013 12:06 PM by MarcoGralike RSS

XMLTYPE.createxml - how to free memory?

afd Explorer
Currently Being Moderated
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) Expert
    Currently Being Moderated
    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?
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated

    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?
    MarcoGralike Oracle ACE Director
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated
    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?
    MarcoGralike Oracle ACE Director
    Currently Being Moderated

    > 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 …

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points