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

From: Buechi Martin <Martin.Buechi_at_avaloq.com>
Date: Thu, 12 May 2005 17:09:47 +0200
Message-Id: <20050512151217.AAAAC61C9F@gw1.avaloq.com>

All,

We have an application with 4,200 PL/SQL packages on Oracle 9.2.0.5 on = AIX 5.3. Many of them use associative arrays (index-by tables) and SQL = varrays. One or more of these packages does not properly free its memory = after usage resulting in an ever growing PGA size (up to 1GB followed by = ERROR: ORA-04030: out of process memory when trying to allocate 16408 = bytes (koh-kghu sessi,pl/sql vc2), ORA-06500: PL/SQL: storage error, = v$process.pga_freeable_mem below 1 MB).

If I grow a session and then execute =
dbms_session.free_unused_user_memory, the PGA size = (v$process.pga_alloc_mem and v$process.pga_used_mem) does not decrease. = If I execute first dbms_session.reset_package and then = dbms_session.free_unused_user_memory, both values do decrease down to a = couple of MB.

My assumption is that this is our own programming error (missing = table.delete) rather than a PL/SQL memory leak because it started to = appear when the application was changed (lots of changes, making it hard = 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 = out which package holds on to how much heap memory in terms of PL/SQL = and SQL collection variables. Is there a way to find this out, e.g., by = dumping the process memory? (I tried with immediate trace name heapdump = 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 = 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 = buckets and cache buffer chain latches for tablespace with non-default = block sizes? If not, I guess I can find out by creating such a = tablespace and looking at x$bh.

------------------------------------------=20

A v a l o q - essential for banking=20
Avaloq Evolution AG=20
Allmendstrasse 140, 8027 Z=FCrich=20
T +41 44 488 6888, F +41 44 488 6868, <http://www.avaloq.com/>=20 Martin B=FCchi <mailto:martin.buechi_at_avaloq.com>=20

--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 12 2005 - 11:18:36 CDT