PL/SQL Garbage Collection

In another blog a question was raised about PL/SQL garbage collection, and how memory used, for example, by PL/SQL table is reused. I'm sure it's been covered elsewhere, but it never hurts to restate things so....

set serveroutput on size 1000000

declare
  procedure show_mem (p_text in varchar2) is
    cursor c_mem is 
     select a.name, b.value
     from v$statname a, v$mystat b
     where a.statistic# = b.statistic#
     and a.name like '%pga %';
  begin
    dbms_output.put_line('---'||p_text||'---');
    for i in c_mem loop
      dbms_output.put_line(rpad(i.name,35)||':'||lpad(i.value,20));
    end loop;
    dbms_output.put_line('------');
  end show_mem;
begin
  show_mem('before');
  declare
    type tab_char is table of varchar2(2000) index by pls_integer;
    t_char tab_char;
  begin
    for i in 1..2000 loop
      t_char(i) := rpad('a',2000);
    end loop;
    show_mem('in scope 1');
  end;
  show_mem('out scope 1');
  declare
    type tab_char is table of varchar2(2000) index by pls_integer;
    t_char tab_char;
  begin
    for i in 1..2000 loop
      t_char(i) := rpad('a',2000);
    end loop;
    show_mem('in scope 2');
  end;
  show_mem('out scope 2');
end;
.
/

---before---
session pga memory                 :             1556052
session pga memory max             :             1556052
------
---in scope 1---
session pga memory                 :             7781972
session pga memory max             :             7781972
------
---out scope 1---
session pga memory                 :             7781972
session pga memory max             :             7913044
------
---in scope 2---
session pga memory                 :            14007892
session pga memory max             :            14007892
------
---out scope 2---
session pga memory                 :            14007892
session pga memory max             :            14138964
------

PL/SQL procedure successfully completed.

USER@xe> /
---before---
session pga memory                 :             1687124
session pga memory max             :            14138964
------
---in scope 1---
session pga memory                 :             7781972
session pga memory max             :            14138964
------
---out scope 1---
session pga memory                 :             7781972
session pga memory max             :            14138964
------
---in scope 2---
session pga memory                 :            14007892
session pga memory max             :            14138964
------
---out scope 2---
session pga memory                 :            14007892
session pga memory max             :            14138964
------

At the original 'before' point, I had a current value of about 1.5 million.
It jumped up to 8 million when the PL/SQL table was loaded.
It stayed there even when the PL/SQL table went out of scope.
It jumped again to over 14 million when a new PL/SQL was loaded, and again stayed there.
However at the start of a rerun, it returns to under 2 million before jumping again as in the original run.

As a slight variation, I used an explict named procedure rather than an anonymous PL/SQL block.

set serveroutput on size 1000000

declare
  procedure show_mem (p_text in varchar2) is
    cursor c_mem is 
     select a.name, b.value
     from v$statname a, v$mystat b
     where a.statistic# = b.statistic#
     and a.name like '%pga %';
  begin
    dbms_output.put_line('---'||p_text||'---');
    for i in c_mem loop
      dbms_output.put_line(rpad(i.name,35)||':'||lpad(i.value,20));
    end loop;
    dbms_output.put_line('------');
  end show_mem;
  procedure set_tab (p_text in varchar2) is
    type tab_char is table of varchar2(2000) index by pls_integer;
    t_char tab_char;
  begin
    show_mem('before:'||p_text);
    for i in 1..2000 loop
      t_char(i) := rpad('a',2000);
    end loop;
    show_mem('after:'||p_text);
  end;
begin
  set_tab('1');
  set_tab('2');
  show_mem('end');
end;
.
/

---before:1---
session pga memory                 :             1752660
session pga memory max             :            14138964
------
---after:1---
session pga memory                 :             7847508
session pga memory max             :            14138964
------
---before:2---
session pga memory                 :             7847508
session pga memory max             :            14138964
------
---after:2---
session pga memory                 :             7847508
session pga memory max             :            14138964
------
---end---
session pga memory                 :             7847508
session pga memory max             :            14138964
------

This time, the memory used in the first execution of the named procedure is reused for the second, so there isn't that extra jump. This shows that there can be a benefit in putting a memory hungry process into a named procedure (or function).

As a final test, I tried

declare
  procedure show_mem (p_text in varchar2) is
    cursor c_mem is
     select a.name, b.value
     from v$statname a, v$mystat b
     where a.statistic# = b.statistic#
     and a.name like '%pga %';
  begin
    dbms_output.put_line('---'||p_text||'---');
    for i in c_mem loop
      dbms_output.put_line(rpad(i.name,35)||':'||lpad(i.value,20));
    end loop;
    dbms_output.put_line('------');
  end show_mem;
begin
  show_mem('before');
  declare
    procedure set_tab (p_text in varchar2) is
      type tab_char is table of varchar2(2000) index by pls_integer;
      t_char tab_char;
    begin
      show_mem('before:'||p_text);
      for i in 1..2000 loop
        t_char(i) := rpad('a',2000);
      end loop;
      show_mem('after:'||p_text);
    end;
  begin
    show_mem('before scope 1');
    set_tab('point 1');
    show_mem('after scope 1');
  end;
  show_mem('out scope 1');
  declare
    type tab_char is table of varchar2(2000) index by pls_integer;
    t_char tab_char;
  begin
    for i in 1..2000 loop
      t_char(i) := rpad('a',2000);
    end loop;
    show_mem('in scope 2');
  end;
  show_mem('out scope 2');
end;
.
/

---before---
session pga memory                 :             1818196
session pga memory max             :            14138964
------
---before scope 1---
session pga memory                 :             1818196
session pga memory max             :            14138964
------
---before:point 1---
session pga memory                 :             1818196
session pga memory max             :            14138964
------
---after:point 1---
session pga memory                 :             7913044
session pga memory max             :            14138964
------
---after scope 1---
session pga memory                 :             7913044
session pga memory max             :            14138964
------
---out scope 1---
session pga memory                 :             7913044
session pga memory max             :            14138964
------
---in scope 2---
session pga memory                 :             7913044
session pga memory max             :            14138964
------
---out scope 2---
session pga memory                 :             7913044
session pga memory max             :            14138964
------

That demonstrates that, you can get the benefits of the memory cleanup and retain a single program flow (ie not skipping to an out of line procedure), by defining a named PL/SQL procedure within the anonymous PL/SQL block.
These tests were done in XE.