Re: Session PGA increase

From: GovindanK <gkatteri_at_gawab.com>
Date: Thu, 21 Jan 2010 18:00:02 -0800
Message-ID: <4B5906A2.7030905_at_gawab.com>

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_at_mydb >REM XXXXXXXXXXXXXXXXXXXXXXXXXX
SYSTEM_at_mydb >REM X     Run 1              X
SYSTEM_at_mydb >REM XXXXXXXXXXXXXXXXXXXXXXXXXX

SYSTEM_at_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_at_mydb >ALTER SYSTEM FLUSH SHARED_POOL   2 /

System altered.

SYSTEM_at_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_at_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_at_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_at_mydb >REM XXXXXXXXXXXXXXXXXXXXXXXXXX
SYSTEM_at_mydb >REM X     Run 2              X
SYSTEM_at_mydb >REM XXXXXXXXXXXXXXXXXXXXXXXXXX
SYSTEM_at_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_at_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_at_mydb >REM XXXXXXXXXXXXXXXXXXXXXXXXXX
SYSTEM_at_mydb >REM X     Run 3              X
SYSTEM_at_mydb >REM XXXXXXXXXXXXXXXXXXXXXXXXXX
SYSTEM_at_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_at_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_at_mydb >spool off
SYSTEM_at_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_at_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_at_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_at_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

Received on Thu Jan 21 2010 - 20:00:02 CST