How to locate packages that do not deallocate PL/SQL collections
- From: "Buechi Martin" <Martin.Buechi@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 12 May 2005 17:09:47 +0200
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@xxxxxxxxxx>=20
--
//www.freelists.org/webpage/oracle-l
Other related posts:
