Re: Session PGA increase
Date: Thu, 21 Jan 2010 09:39:13 +0900
I don't think that this is an expected behavior especially when the collections are cleared after each iteration as OP insisted.
There could be some memory leaks around the external tables and/or the collections.
Tanel Poder has written an excellent article on how to troubleshoot the PGA problem. It would help.
Dion Cho - Oracle Performance Storyteller
http://dioncho.wordpress.com (english) http://ukja.tistory.com (korean) http://dioncho.blogspot.com (japanese) http://ask.ex-em.com (q&a) ================================
2010/1/21 GovindanK <gkatteri_at_gawab.com>
> This is due to the fact that PL/SQL tables and any similar collections
> impact PGA. This is an expected behaviour. Global temporary tables may
> help, if it does not impact the PGA. Or else you would need to start a new
> session whenever the file arrives.
> Domagoj Smoljanovic wrote:
>> Hi all,
>> I have a procedure that loads data from file using external tables.
>> It bulk collects data from external table into a collection, does a
>> little transformations on the data and inserts into a database table.
>> At the end of procedure collections are deleted. This procedure is ran
>> from another plsql proc that runs it for each file that has arrived on
>> the server.
>> But what I have noticed is constant increase in session PGA usage
>> while the procedure is running. It starts small (cca 20 Mb) but after
>> about 5000 files "session pga usage" grows to about 1 gig. The size of
>> loaded data per file shouldn't be more than 10Mb.
>> It seems as some memory is not released, but the procedure is so
>> simple i really don't know in what part???
>> Any ideas what might be the cause for this?
>> Thanx in advance