Dear Sir,

When ever I use BULK COLLECT feature to collect thousands of rows from a table PGA memory is running
Out of scope. Here I have a table named DB_TRAN2004 which has got 72369 rows / 21 MB size(segment size).
With this table when I try to use bulk collect to fetch all rows it occupies 228 MB of “session pga memory”.

Below I listed the steps followed for collecting all rows using bulk collect and server informations:

OS  : AIX 5L 5.2
Oracle Release : Oracle 9i Release 2 (9.2.0.6.0)
Table Name : db_tran2004 (Blocks: 2628, Avg row len: 249)
Table Size  : 21 MB
No. of Rows : 72369
DB Block Size : 8192

/*** Procedure sp_test used to fetch all rows from db_tran2004 ***/
CREATE OR REPLACE PROCEDURE sp_test
AS
TYPE typ_tran2004 IS TABLE OF tran2004%ROWTYPE;
tab_tran2004 typ_tran2004;
v_count NUMBER DEFAULT 0;
BEGIN
SELECT * BULK COLLECT INTO tab_tran2004 FROM db_tran2004;
FOR i IN 1..tab_tran2004.LAST LOOP
    v_count := v_count + 1;   
    tab_tran2004.delete;
END LOOP;
dbms_output.put_line('Total Records : '|| v_count);
END sp_test;

/*** SQL to check PGA utilization ***/
select s.sid,s.machine,username,name,value/1048576 fsize
from v$session s, v$sesstat t, v$statname n
where s.sid = t.sid
and s.username is not null
and s.type='USER'
and n.name LIKE 'session pga memory%'
and t.statistic# = n.STATISTIC#
and s.sid = 21

/*** SQL Returned ***/
session pga memory   228.108
session pga memory max   228.170

After forcefully cleaning the memory using
EXEX DBMS_SESSION.FREE_UNUSED_USER_MEMORY it comes down to normal position

session pga memory   0.440
session pga memory max   228.170

Now I have some questions on above activities:

a) Is the value obtained (228 MB) is the amount of memory occupied on PGA area by my session is right.

b) How to get exact amount PGA & SGA memory my session takes when bulk collect is used. And is there any way to
Overcome this problem because if one single session going to takes 228 MB of memory on my PGA, then my database server
should have TB’s of physical memory. Is this a bug or any other workaround is there to solve this problem.

c) And more over I like to know is any way to free up memory blocked by a sessions (as how we can use
DBMS_SESSION.FREE_UNUSED_FREE_MEMORY to free my own session). Because it takes long time for
PMON to clear the session memory blocked (even after 30 mint it remain same) and whenever a user get
terminated/killed the resource is locked for quite long time and even it end up in server hang.

All suggestions and solutions are welcomed.

 

Thanks & Regards,

Dayananda Shenoy