Monday, December 12, 2005

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.

2 comments:

Samir Kulkarni said...

Hi Gary,

Thanks for the code and explaination !!

I am a newbie to Oracle PL/SQL programming.

When I tried to execute the anonymous block it gave me the following error:

from v$statname a, v$mystat b
*
ERROR at line 5:
ORA-06550: line 5, column 25:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 4, column 6:
PL/SQL: SQL Statement ignored
ORA-06550: line 11, column 33:
PLS-00364: loop index variable 'I' use is invalid
ORA-06550: line 11, column 7:
PL/SQL: Statement ignored

Could you please help me with that ??

Kind Regards
Sam

Gary Myers said...

That will be permissions.
You will need to be granted select on those tables. Your DBA would be able to do that. In your own system, if you log on with

sqlplus / as sysdba

you should be able to
GRANT SELECT_CATALOG_ROLE TO user;

and then that user will be able to run the script