My Oracle Support Banner

XML Query Uses XMLTYPE.TRANSFORM() In Loop Shows Steady Increase Of PGA MEMORY USAGE (Doc ID 2164949.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

Symptoms

While running following XML block query in loop shows steady increase of pga memory usage in the DB during XMLTYPE.TRANSFORM() process:

Session-1:

declare
myxml xmltype;
mystylesheet xmltype := xmltype('<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"><xsl:template
match="/"><xsl:copy-of select="*"/></xsl:template></xsl:stylesheet>');
result xmltype;
begin
myxml := xmltype('<bolek/>');
for i in 1..99999999
loop
result := myxml.transform(mystylesheet);
end loop;
end;
/

 

Session-2:

SQL> select SUBSTR(s.sid,1,5) sid,n.name,s.value
from v$statname n,V$sesstat s,v$session v
where v.SID=&ORASID
and v.sid = s.sid
and n.statistic# = s.statistic#
and value > 0 and n.name like '%memory%'
order by s.sid,n.class,n.name
/ 2 3 4 5 6 7 8
Enter value for orasid: 15
old 3: where v.SID=&ORASID
new 3: where v.SID=15
.
SID NAME
----- ----------------------------------------------------------------
VALUE
----------
15 session pga memory
330458272
.
15 session pga memory max
330458272
.
15 session uga memory
561584
.
15 session uga memory max
561584
.
.
SQL> /
Enter value for orasid: 15
old 3: where v.SID=&ORASID
new 3: where v.SID=15
.
SID NAME
----- ----------------------------------------------------------------
VALUE
----------
15 session pga memory
352478368
.
15 session pga memory max
352478368
.
15 session uga memory
561584
.
15 session uga memory max
561584
.
SQL> /
Enter value for orasid: 15
old 3: where v.SID=&ORASID
new 3: where v.SID=15
.
SID NAME
----- ----------------------------------------------------------------
VALUE
----------
15 session pga memory
363029664
.
15 session pga memory max
363029664
.
15 session uga memory
561584
.
15 session uga memory max
561584
.
.
SQL> /
Enter value for orasid: 15
old 3: where v.SID=&ORASID
new 3: where v.SID=15
.
SID NAME
----- ----------------------------------------------------------------
VALUE
----------
15 session pga memory
379741344
.
15 session pga memory max
379741344
.
15 session uga memory
561584
.
15 session uga memory max
561584

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.