Re: Session PGA increase

I do see the PGA increasing , so it is an expected behaviour. The PGA does get released when the session disconnects as seen below (as you had also mentioned). This is on 10.2.0.3. The reason why Domagoj is seeing increase in PGA must be due to the fact that the parent process is probably not exitting. 

SYSTEM@mydb >REM XXXXXXXXXXXXXXXXXXXXXXXXXX
SYSTEM@mydb >REM X     Run 1              X
SYSTEM@mydb >REM XXXXXXXXXXXXXXXXXXXXXXXXXX
SYSTEM@mydb >create or replace package my_pkg
 2  as
 3     type array is table of char(2000) index by binary_integer;
 4     gk_data array;
 5  end;
 6  /

Package created.

SYSTEM@mydb >ALTER SYSTEM FLUSH SHARED_POOL
 2  /

System altered.

SYSTEM@mydb >select a.name, to_char(b.value, '999,999,999') value
 2    from v$statname a, v$mystat b
 3   where a.statistic# = b.statistic#
 4     and a.name like '%ga memory%'
 5  /

NAME                                     VALUE
---------------------------------------- ------------
session uga memory                          1,613,072
session uga memory max                      5,006,984
session pga memory                          2,461,512
session pga memory max                      5,672,776

SYSTEM@mydb >begin
 2     for i in 1 .. 10000
 3     loop
 4       my_pkg.gk_data(i) := 'x';
 5     end loop;
 6  end;
 7  /

PL/SQL procedure successfully completed.

SYSTEM@mydb >select a.name, to_char(b.value, '999,999,999') value
 2    from v$statname a, v$mystat b
 3   where a.statistic# = b.statistic#
 4     and a.name like '%ga memory%'
 5  /

NAME                                     VALUE
---------------------------------------- ------------
session uga memory                         33,008,912
session uga memory max                     33,132,424
session pga memory                         33,853,256
session pga memory max                     33,853,256

SYSTEM@mydb >REM XXXXXXXXXXXXXXXXXXXXXXXXXX
SYSTEM@mydb >REM X     Run 2              X
SYSTEM@mydb >REM XXXXXXXXXXXXXXXXXXXXXXXXXX
SYSTEM@mydb >begin
 2     for i in 10001 .. 20000
 3     loop
 4       my_pkg.gk_data(i) := 'x';
 5     end loop;
 6  end;
 7  /

PL/SQL procedure successfully completed.

SYSTEM@mydb >select a.name, to_char(b.value, '999,999,999') value
 2    from v$statname a, v$mystat b
 3   where a.statistic# = b.statistic#
 4     and a.name like '%ga memory%'
 5  /

NAME                                     VALUE
---------------------------------------- ------------
session uga memory                         64,143,120
session uga memory max                     64,266,632
session pga memory                         65,113,928
session pga memory max                     65,113,928

Elapsed: 00:00:00.20
SYSTEM@mydb >REM XXXXXXXXXXXXXXXXXXXXXXXXXX
SYSTEM@mydb >REM X     Run 3              X
SYSTEM@mydb >REM XXXXXXXXXXXXXXXXXXXXXXXXXX
SYSTEM@mydb >begin
 2     for i in 20001 .. 30000
 3     loop
 4       my_pkg.gk_data(i) := 'x';
 5     end loop;
 6  end;
 7  /

PL/SQL procedure successfully completed.

SYSTEM@mydb >select a.name, to_char(b.value, '999,999,999') value
 2    from v$statname a, v$mystat b
 3   where a.statistic# = b.statistic#
 4     and a.name like '%ga memory%'
 5  /

NAME                                     VALUE
---------------------------------------- ------------
session uga memory                         95,342,736
session uga memory max                     95,466,248
session pga memory                         96,374,600
session pga memory max                     96,374,600

SYSTEM@mydb >spool off
SYSTEM@mydb >quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 
gkatteri>
gkatteri>
gkatteri>
gkatteri>sqlplus system@mydb

SQL*Plus: Release 10.2.0.1.0 - Production on ......

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 

SYSTEM@mydb >select a.name, to_char(b.value, '999,999,999') value
 2  from v$statname a, v$mystat b
 3  where a.statistic# = b.statistic#
 4    and a.name like '%ga memory%'
 5  /

NAME                                     VALUE
---------------------------------------- ------------
session uga memory                          1,416,848
session uga memory max                      1,540,360
session pga memory                          2,264,904
session pga memory max                      2,264,904

SYSTEM@mydb >

HTH
GovindanK

Dion Cho wrote:
I don't think that this is an expected behavior especially when the collections are cleared after each iteration as OP insisted.
There could be some memory leaks around the external tables and/or the collections. 




--
http://www.freelists.org/webpage/oracle-l

Other related posts: