Re: Why is UGA size so large?

Marcel D?rr apparently said,on my timestamp of 25/03/2005 6:58 AM:

> We have the following settings:
> - pga_aggregate_target=335544320
> - workarea_size_policy='AUTO'
> So I belive that any memory used for sorting should be given back to
> the PGA after the sort completes and the last row is fetched.

One would hope. Maybe it's worth a gander at Metalink for any potential "features"?

> Could it be caused by the application not closing cursors that it no
> longer needs, therefore potentially leaving sorted result sets in the
> UGA, the way sort_area_retained_size of sort memory is kept in the UGA
> in manual mode?

That could be. Do you have cursor_space_for_time(sp?) turned on? That seems to use up a LOT of memory in our RAC setup, we had to turn it off.

> If so, how can I distinguish between open cursors that are just cached
> (PL/SQL Cache or Session Cached Cursors) and Cursors that are still
> open in the application?

Good question. I don't think you can. Anyone?

-- 
Cheers
Nuno Souto
in sunny Sydney, Australia
wizofoz2k_at_yahoo.com.au.nospam