Re: workarea_size_policy being ignored

In article <1103640964.485974.301620_at_c13g2000cwb.googlegroups.com>, tdwilsonng_at_yahoo.com says...
>
>I ran a benchmark last week on an AIX server using 10g 64-bit using the
>following parameters:
>
>sga_target=4000M
>workarea_size_policy=manual
>sort_area_size=524000
>sort_area_retained_size=524000
>
>
>However, according to V$PGASTAT and the OS, the PGA was using over 16GB
>of RAM for 2000 dedicated processes. This is the same amount that it
>used when I set workarea_size_policy to AUTO. So it looks like the
>workarea_size_policy is being ignored and Oracle is sucking up all of
>the memory on the server and some that's not there, too.
>Why can't I limit the memory usage?
>
>Thanks,
>
>Tom
>

I confused -- when you set it to manual, it'll grow and grow and grow and grow unbounded.

When you set it to auto
AND you set the pga_aggregate_target (which I don't see)

then Oracle will attempt to restrict dynamic workarea allocations (sorts, hashes) to that aggregate amount for the instance (note: if you have 2000 dedicated server processes each fill up a whopping big plsql table that is a global variable -- well, not too much we can do about that, that is pga memory in dedicated server but falls outside of the workarea policy)

It seems you want

workarea policy => auto

pga_aggregate_target => whatever you want the sorts/hashes to work in, this is an aggregate number, not a per session number. say you wanted Oracle to use about 8gig of RAM for sorting/hashing in total, set this number.

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