SQL & PL/SQL

I have faced a very interesting problem recently.

I use PL/SQL tables ( Type TTab is table of ... index by binary_integer; ) and collections ( Type TTab is table of ...; ) in my packages very widely. And have noticed avery strange thing Oracle does. It seems to me that there are memory leaks in PGA when I use PL/SQL tables or collections. Let me a little example.

CREATE OR REPLACE PACKAGE rds_mdt_test IS

TYPE TNumberList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

PROCEDURE test_plsql_table(cnt INTEGER);

END rds_mdt_test;
/
CREATE OR REPLACE PACKAGE BODY rds_mdt_test IS

PROCEDURE test_plsql_table(cnt INTEGER) IS
x TNumberList;
BEGIN
FOR indx IN 1 .. cnt LOOP
x(indx) := indx;
END LOOP;
END;

END rds_mdt_test;


I run the following test code:

BEGIN
rds_mdt_test.test_plsql_table (1000000);
END;

and see that my session uses about 40M in PGA.
If I repeat this example in the same session creating the PL/SQL table of smaller size, for instance:

BEGIN
rds_mdt_test.test_plsql_table (1);
END;

I see again that the size of used memory in PGA by my session was not decreased and still be the same.

The same result I get if I use not PL/SQL tables, but collections or varrays.

I have tried some techniques to make Oracle to free the memory, for instance to rewrite my procedure in the following ways:

PROCEDURE test_plsql_table(cnt INTEGER) IS
x TNumberList;
BEGIN
FOR indx IN 1 .. cnt LOOP
x(indx) := indx;
END LOOP;
x.DELETE;
END;

or

PROCEDURE test_plsql_table(cnt INTEGER) IS
x TNumberList;
BEGIN
FOR indx IN 1 .. cnt LOOP
x(indx) := indx;
END LOOP;
FOR indx in 1 .. cnt LOOP
x.DELETE(indx);
END LOOP;
END;

or

PROCEDURE test_plsql_table(cnt INTEGER) IS
x TNumberList;
empty TNumberList;
BEGIN
FOR indx IN 1 .. cnt LOOP
x(indx) := indx;
END LOOP;
x := empty;
END;

and so on, but result was the same.

This is a huge problem for me as I have to manipulate collections and PL/SQL tables of very big size (from dozens of thousand of rows to millions or rows) and just a few sessions running my procedure may cause server's fall due to memory lack.

I can not understand what Oracle reseveres such much memory for (I use local variables) -- is it a bug or a feature?

I will be appreciated for any help.

I use Oracle9.2.0.1.0 server under Windows2000.

Thank you in advance.

Dmitriy.