Re: bulk collect memory leak

On Thu, 24 May 2001 23:45:09 GMT, "Aleksey Kochetov" <kochetov_at_optonline.net> wrote:

There was a problem with DELETE for a collection at one stage. I can't remember which version. It might be there again. The fastest way is to define an empty collection variable of the same type and copy it to the one you want to clear. That releases all memory.
See if the annotated code below fixes it (from memory, you may have to fiddle it):

>
>CREATE OR REPLACE PROCEDURE stress_test IS
> TYPE strings IS TABLE OF VARCHAR2(1000)
> INDEX BY BINARY_INTEGER;
> lv_dummy strings;

Add this:

   lv_clear strings;
>BEGIN
> FOR i IN 1..10 LOOP
> SELECT dummy BULK COLLECT INTO lv_dummy
> FROM dual WHERE dummy IS NULL;

 replace this:
> lv_dummy.DELETE;

with this:

     lv_dummy := lv_clear;
> END LOOP;

 and again this:
> lv_dummy.DELETE;

with this:

   lv_dummy := lv_clear;
>END stress_test;
>/
>
>BEGIN
> FOR i IN 1..10000 LOOP
> stress_test;
> END LOOP;
>END;
>/
>
>SELECT * FROM v$sesstat t1, v$statname t2
> WHERE name IN ('session uga memory','session pga memory')
> AND t1.statistic# = t2.statistic#
> AND sid = 14;

Trick is to never populate lv_clear.
HTH Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Fri May 25 2001 - 09:43:57 CDT