Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to locate packages that do not deallocate PL/SQL collections

RE: How to locate packages that do not deallocate PL/SQL collections

From: Buechi Martin <>
Date: Fri, 13 May 2005 11:35:49 +0200
Message-Id: <>


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.




-----Original Message-----

From: Buechi Martin=20
Sent: Donnerstag, 12. Mai 2005 17:10
Subject: How to locate packages that do not deallocate PL/SQL collections


We have an application with 4,200 PL/SQL packages on Oracle 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, but in or 10gR2 =3D
Beta 3, we could load the application onto one of these releases.



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.

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, <>=3D20 Martin B=3DFCchi <>=3D20

-- Received on Fri May 13 2005 - 05:40:24 CDT

Original text of this message