PGA Memory Usage In Collection's

scott@10G> SELECT  name,value
  2  FROM V$MYSTAT m,V$STATNAME s
  3  WHERE m.STATISTIC# = s.STATISTIC#
  4  AND   s.STATISTIC# IN (25,26)
  5  ;

NAME                                VALUE
------------------------------ ----------
session pga memory                 974420
session pga memory max            1957460

scott@10G> DECLARE
  2   TYPE T_Ty is TABLE OF T%ROWTYPE INDEX BY PLS_INTEGER;
  3   t1 T_TY;
  4  BEGIN
  5   SELECT *
  6   BULK COLLECT INTO t1
  7   FROM T;
  8  END;
  9  /

PL/SQL procedure successfully completed.

scott@10G> SELECT  name,value
  2  FROM V$MYSTAT m,V$STATNAME s
  3  WHERE m.STATISTIC# = s.STATISTIC#
  4  AND   s.STATISTIC# IN (25,26);

NAME                                VALUE
------------------------------ ----------
session pga memory                 974420
session pga memory max           75882068

scott@10G> DECLARE
  2   TYPE T_Ty is TABLE OF T%ROWTYPE INDEX BY PLS_INTEGER;
  3   t1 T_TY;
  4   t2 T_TY;
  5  BEGIN
  6   SELECT *
  7   BULK COLLECT INTO t1
  8   FROM T;
  9  
 10   SELECT *
 11   BULK COLLECT INTO t2
 12   FROM T; 
 13  END;
 14  /

PL/SQL procedure successfully completed.

scott@10G> SELECT  name,value
  2  FROM V$MYSTAT m,V$STATNAME s
  3  WHERE m.STATISTIC# = s.STATISTIC#
  4  AND   s.STATISTIC# IN (25,26);

NAME                                VALUE
------------------------------ ----------
session pga memory                 777812
session pga memory max          150855252

scott@10G> DECLARE
  2   TYPE T_Ty is TABLE OF T%ROWTYPE INDEX BY PLS_INTEGER;
  3   t1 T_TY;
  4   t2 T_TY;
  5  BEGIN
  6   SELECT *
  7   BULK COLLECT INTO t1
  8   FROM T;
  9  
 10   SELECT *
 11   BULK COLLECT INTO t2
 12   FROM T; 
 13  
 14   t1.DELETE;                                    <<<============= (Array Deleted)
 15   t2.DELETE;                                    <<<============= (Array Deleted.)
 16  END;
 17  /

PL/SQL procedure successfully completed.

scott@10G> SELECT  name,value
  2  FROM V$MYSTAT m,V$STATNAME s
  3  WHERE m.STATISTIC# = s.STATISTIC#
  4  AND   s.STATISTIC# IN (25,26);

NAME                                VALUE
------------------------------ ----------
session pga memory                 777812
session pga memory max          150855252            <<<=== (PGA Memory doesn't get off even Array Deleted.)

scott@10G> disconn
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
scott@10G> connect scott/oracle
Connected.
scott@10G> SELECT  name,value
  2  FROM V$MYSTAT m,V$STATNAME s
  3  WHERE m.STATISTIC# = s.STATISTIC#
  4  AND   s.STATISTIC# IN (25,26);

NAME                                VALUE
------------------------------ ----------
session pga memory                 646740
session pga memory max             777812

scott@10G> DECLARE
  2   TYPE T_Ty is TABLE OF T%ROWTYPE INDEX BY PLS_INTEGER;
  3   t1 T_TY;
  4   t2 T_TY;
  5  BEGIN
  6   SELECT *
  7   BULK COLLECT INTO t1
  8   FROM T;
  9  
 10   t1.DELETE;
 11  
 12   SELECT *
 13   BULK COLLECT INTO t2
 14   FROM T;    
 15  END;
 16  /

PL/SQL procedure successfully completed.

scott@10G> SELECT  name,value
  2  FROM V$MYSTAT m,V$STATNAME s
  3  WHERE m.STATISTIC# = s.STATISTIC#
  4  AND   s.STATISTIC# IN (25,26)
  5  ;

NAME                                VALUE
------------------------------ ----------
session pga memory                 712276
session pga memory max           75816532        <<<=========== ( Even though we used two Arrays, The second one just used the space the First one used.)

Rule of Thumb (ROT) : DELETE Collection Elements once Collection elements are Utilized (or) no more needed.