RE: How to locate packages that do not deallocate PL/SQL collections
- From: "Buechi Martin" <Martin.Buechi@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 13 May 2005 11:35:49 +0200
All,
I found the answer to my question myself. I am sharing it because it
might be of general interest. Enjoy!
The basic idea is that by recompiling a package body, the associated
session memory of global variables gets freed, but no other memory.
The script below works provided that package headers don't cause the
memory leak, it is possible to recompile the bodies (no other sessions
locking them), and the script is run in the session that holds on to the
excessive amount of memory. The first restriction can be overcome if
there are no circular dependencies - but proper information hiding
should not make this necessary in most cases. The second is a conditio
sine qua non. The third can be solved by a small modification of the
script.
Regards,
Martin
--
-- Setup
drop table pkg_mem_alloc;
create table pkg_mem_alloc(
name varchar2(1000)
,pga_alloc_mem number
,pga_used_mem number
);
create or replace procedure mem_alloc(
o_pga_alloc_mem out number
,o_pga_used_mem out number
)
as
begin
dbms_session.free_unused_user_memory;
select v.pga_alloc_mem
,v.pga_used_mem
into o_pga_alloc_mem
,o_pga_used_mem
from gv$session g
,v$process v
where sid =3D (select sid from v$mystat where rownum=3D1)
and g.paddr =3D v.addr;
end mem_alloc;
/
-- Run
-- No other sessions running or compile will hang!
-- No useful results if memory hangs off packages rather than bodies!
set serveroutput on size 100000
truncate table pkg_mem_alloc;
variable l_last_pga_alloc_mem number
variable l_last_pga_used_mem number
declare
type t_varchar_tab is table of v$access.object%type index by
pls_integer;
l_pkg_list t_varchar_tab;
l_last_pga_alloc_mem number;
l_cur_pga_alloc_mem number;
l_last_pga_used_mem number;
l_cur_pga_used_mem number;
l_pkg_name v$access.object%type;
begin
select distinct(object)
bulk collect into l_pkg_list
from v$access
where sid =3D (select sid from v$mystat where rownum=3D1)
and type like 'PACKAGE%'
and owner =3D 'K';=20
mem_alloc(l_last_pga_alloc_mem, l_last_pga_used_mem);
for i in 1..l_pkg_list.count loop
l_pkg_name :=3D l_pkg_list(i);
begin
execute immediate 'alter package "' || l_pkg_name || '" compile
body';
exception
when others then
dbms_output.put_line('Problem compiling package body ' ||
l_pkg_name);
end;
mem_alloc(l_cur_pga_alloc_mem, l_cur_pga_used_mem);
insert into pkg_mem_alloc values(
l_pkg_name
,l_last_pga_alloc_mem - l_cur_pga_alloc_mem
,l_last_pga_used_mem - l_cur_pga_used_mem
);
commit;
l_last_pga_alloc_mem :=3D l_cur_pga_alloc_mem;
l_last_pga_used_mem :=3D l_cur_pga_used_mem;=20
end loop;
:l_last_pga_alloc_mem :=3D l_last_pga_alloc_mem;
:l_last_pga_used_mem :=3D l_last_pga_used_mem; =20
end;
/
exec dbms_session.reset_package
declare
l_cur_pga_alloc_mem number;
l_cur_pga_used_mem number;
begin
mem_alloc(l_cur_pga_alloc_mem, l_cur_pga_used_mem);
insert into pkg_mem_alloc values(
'Package headers/unknown'
,:l_last_pga_alloc_mem - l_cur_pga_alloc_mem
,:l_last_pga_used_mem - l_cur_pga_used_mem
);
commit;
end;
/
column name format a30
select name
,pga_used_mem / 1024 / 1024 "pga_used_mem MB"
from pkg_mem_alloc
order by 2 desc;
=20
-----Original Message-----
From: Buechi Martin=20
Sent: Donnerstag, 12. Mai 2005 17:10
To: oracle-l@xxxxxxxxxxxxx
Subject: How to locate packages that do not deallocate PL/SQL
collections
All,
We have an application with 4,200 PL/SQL packages on Oracle 9.2.0.5 on =
=3D
AIX 5.3. Many of them use associative arrays (index-by tables) and SQL =
=3D
varrays. One or more of these packages does not properly free its memory
=3D
after usage resulting in an ever growing PGA size (up to 1GB followed by
=3D
ERROR: ORA-04030: out of process memory when trying to allocate 16408 =
=3D
bytes (koh-kghu sessi,pl/sql vc2), ORA-06500: PL/SQL: storage error, =3D
v$process.pga_freeable_mem below 1 MB).
If I grow a session and then execute =3D
dbms_session.free_unused_user_memory, the PGA size =3D
(v$process.pga_alloc_mem and v$process.pga_used_mem) does not decrease.
=3D
If I execute first dbms_session.reset_package and then =3D
dbms_session.free_unused_user_memory, both values do decrease down to a
=3D
couple of MB.
My assumption is that this is our own programming error (missing =3D
table.delete) rather than a PL/SQL memory leak because it started to =3D
appear when the application was changed (lots of changes, making it hard
=3D
to isolate the faulty change) and not on an Oracle release change.
To locate the source of the problem, we are looking for a way to find =
=3D
out which package holds on to how much heap memory in terms of PL/SQL =
=3D
and SQL collection variables. Is there a way to find this out, e.g., by
=3D
dumping the process memory? (I tried with immediate trace name heapdump
=3D
1 and 4, but could not find the desired information.)
If such a feature is not available in 9.2.0.5, but in 10.1.0.3 or 10gR2
=3D
Beta 3, we could load the application onto one of these releases.
Regards,
Martin
P.S. Unrelated question: Does Oracle use distinct cache buffer hash =3D
buckets and cache buffer chain latches for tablespace with non-default =
=3D
block sizes? If not, I guess I can find out by creating such a =3D
tablespace and looking at x$bh.
------------------------------------------=3D20
A v a l o q - essential for banking=3D20
Avaloq Evolution AG=3D20
Allmendstrasse 140, 8027 Z=3DFCrich=3D20
T +41 44 488 6888, F +41 44 488 6868, <http://www.avaloq.com/>=3D20
Martin B=3DFCchi <mailto:martin.buechi@xxxxxxxxxx>=3D20
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
