Re: Session PGA increase

From: Dion Cho <ukja.dion_at_gmail.com>
Date: Thu, 21 Jan 2010 09:39:13 +0900
Message-ID: <43c2e3d61001201639u650780qdbe619d44041936f_at_mail.gmail.com>

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.

http://blog.tanelpoder.com/2009/01/02/oracle-memory-troubleshooting-part-1-heapdump-analyzer/

http://dioncho.wordpress.com/2009/07/27/playing-with-ora-4030-error/

---

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.
>
> HTH
> GovindanK
>
>
> 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
>> Domagoj
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 20 2010 - 18:39:13 CST