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