Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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 = 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.
------------------------------------------=20A v a l o q - essential for banking=20
-- http://www.freelists.org/webpage/oracle-lReceived on Thu May 12 2005 - 11:18:36 CDT
![]() |
![]() |