My Oracle Support Banner

Repeated Execution of TABLE(XMLSequence) I a PL/SQL Procedure Allocates Memory In PGA And Does Not Release It When The Code Completes (Doc ID 1456006.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Server - Enterprise Edition - Version 11.1.0.7 to 11.2.0.3 [Release 11.1 to 11.2]
Information in this document applies to any platform.

Symptoms

Executing several times a query from table(XMLSequence) increases pga_used_mem and pga_alloc_mem
more and more.

 -- Example of code allocating the memory --

conn TEST_CLOB

declare
PROCEDURE process_xml

 IS
    l_xml                 XMLType;
    i                         number;
    v_PLT_CD            VARCHAR2(100);
   v_PROCS_NM          VARCHAR2(100);
   v_TRANSF_TYPE       VARCHAR2(100);
   v_TRANSF_STAT_TYPE  VARCHAR2(100);
   v_SESSION_ID        VARCHAR2(100);
   v_SESSION_USR       VARCHAR2(100);
   v_AFCTD_REC_CNT     VARCHAR2(100);
   v_VERSN_NBR         VARCHAR2(100);
 BEGIN
    l_xml := XMLType('<PLT_TRANSF_PROCS PLT_CD="212"
PROCS_DT="2009-02-23">
<PROCS_DT>23-FEB-0911:06:14</PROCS_DT>
<PLT_CD>212</PLT_CD>
<PROCS_NM>UPD_SILHOUETTE_POLYGON</PROCS_NM>
<TRANSF_TYPE>PROMOTE</TRANSF_TYPE>
<TRANSF_STAT_TYPE>COMPLETE</TRANSF_STAT_TYPE>
<START_DT>23-FEB-09 11:08:10</START_DT>
<END_DT>23-FEB-0911:08:10</END_DT>
<SESSION_ID>0</SESSION_ID>
<SESSION_USR>WZ5CTH</SESSION_USR>
<AFCTD_REC_CNT>0</AFCTD_REC_CNT>
<VERSN_NBR>103</VERSN_NBR>
</PLT_TRANSF_PROCS>');


     SELECT v.PLT_CD,
             v.PROCS_NM,
             v.TRANSF_TYPE,
             v.TRANSF_STAT_TYPE,
             v.SESSION_ID,
             v.SESSION_USR,
             v.AFCTD_REC_CNT,
             v.VERSN_NBR
 INTO v_PLT_CD, v_PROCS_NM, v_TRANSF_TYPE,
v_TRANSF_STAT_TYPE, v_SESSION_ID, v_SESSION_USR, v_AFCTD_REC_CNT,
v_VERSN_NBR
           FROM XMLTable('/PLT_TRANSF_PROCS' passing l_xml
COLUMNS
PLT_CD VARCHAR2(7 BYTE) PATH '/PLT_TRANSF_PROCS/PLT_CD',
PROCS_NM VARCHAR2(30 BYTE) PATH '/PLT_TRANSF_PROCS/PROCS_NM',
TRANSF_TYPE VARCHAR2(30 BYTE) PATH '/PLT_TRANSF_PROCS/TRANSF_TYPE',
TRANSF_STAT_TYPE VARCHAR2(20 BYTE) PATH '/PLT_TRANSF_PROCS/TRANSF_STAT_TYPE',
SESSION_ID NUMBER PATH '/PLT_TRANSF_PROCS/SESSION_ID',
SESSION_USR VARCHAR2(10 BYTE) PATH '/PLT_TRANSF_PROCS/SESSION_USR',
AFCTD_REC_CNT NUMBER PATH '/PLT_TRANSF_PROCS/AFCTD_REC_CNT',
VERSN_NBR NUMBER PATH '/PLT_TRANSF_PROCS/VERSN_NBR' ) v;



dbms_output.put_line(v_PLT_CD||','||
                              v_PROCS_NM||','||
                              v_TRANSF_TYPE||','||
                              v_TRANSF_STAT_TYPE||','||
                              v_SESSION_ID||','||
                              v_SESSION_USR||','||
                              v_AFCTD_REC_CNT||','||
                              v_VERSN_NBR);
END process_xml;

begin
for i in 1..100000 loop
  process_xml;
end loop;
end;
/

-- Example of code to check the memory allocation --


SELECT pid, category, allocated, used, max_allocated
         FROM   v$process_memory
         WHERE  pid = (SELECT pid
                                   FROM   v$process
                                   WHERE  addr= (select paddr
                                                                 FROM   v$session
                                                                WHERE  sid = (SELECT s.sid
       FROM  v$session s
       WHERE   s.username = 'TEST_CLOB')));

Note: 'TEST_CLOB' is the user       

Output of above statement
- before running the code:

      PID CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- --------------- ---------- ---------- -------------
       19 SQL                  12432       9332         24944
       19 PL/SQL               21320      16664         21320
       19 Other               882664                   882664



- after the code completed:

      PID CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- --------------- ---------- ---------- -------------
       19 SQL                  27152          0        165292
       19 PL/SQL               21320          0         23352
       19 Other             15023720                 15023720


The memory is freed only when the session is closed.

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.