Re: Session PGA increase
Date: Wed, 20 Jan 2010 14:40:48 -0800
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