Re: Why is UGA size so large?

Noons <wizofoz2k_at_yahoo.com.au> wrote in message news:<42429bd4$0$902$5a62ac22_at_per-qv1-newsreader-01.iinet.net.au>...
> Marcel D?rr apparently said,on my timestamp of 24/03/2005 9:21 PM:
>
> > Is the problem caused by PL/SQL cached cursors, session cached
> > cursors, PL/SQL runtime overhead, PL/SQL global variables?
> > How can I avoid that this much memory is used?
> >
>
> are you using sort area by any chance?

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. 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?
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?

Thank you for your help,
Marcel Received on Thu Mar 24 2005 - 13:58:30 CST