Re: workarea_size_policy being ignored

In article <1103669324.466609.276640_at_f14g2000cwb.googlegroups.com>, Tom says...
>
>According to the documentation, when you set the policy to manual,
>then the pga memory values are taken from the various _area_size
>parameters. Therefore, I would expect the pga usage to only be 524,000
>bytes per connection, but the usage was actually 7.5MB per connection.
>
>I originally set the policy to auto and the pga_aggregate_target to
>1GB. I know it's only a target and that Oracle can allocate more than
>the target value, but Oracle was allocating 15 times more than the
>target. And what's worse, Oracle was allocating more memory than was
>available on the server, causing a tremendous amount of paging and
>terrible performance.
>
>Tom
>

do you run just sql or sql and plsql

and if you run plsql, do your programmers allocate gobs of "data" in the form of plsql tables and other structures.

The sort_area_size stuff is not allocated per connection/session and a session can in fact have more than one sort_area_size/sort_area_retained_size going at the same time.

but if the workarea set to auto with a 1gig target didn't cap it off -- I'd have to guess that you do have plsql and the plsql is allocating gobs of data (and there isn't much we can do about that other than give the sessions memory until there isn't anymore)

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation