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@(protected) 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 (See ORA-04030.ora-code.com): out of process memory when trying to allocate 16408 = =3D bytes (koh-kghu sessi,pl/sql vc2), ORA-06500 (See ORA-06500.ora-code.com): 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@(protected)>=3D20
