Why is UGA size so large?

From: Marcel D?rr <MARCEL.D_at_GMX.CH>
Date: 24 Mar 2005 02:21:59 -0800
Message-ID: <646fd09f.0503240221.24214fff@posting.google.com>

We are having problems with large UGA's that are allocated for sessions.
We are running Oracle 9.2.0.5 on windows 2000 Server with /3GB set. We have 4 GB physical memory on the server, which is enough, but we overrun the process-adress-space,
which is limited to 3 GB on Windows. Therefore we get ora-04030 when we try to create new sessions.

Our programs use a lot of pl/sql packages, and some of them do have global data which is saved in the
UGA. But the amount of this data should be a few kilobytes only.

My questions are:
How can I see what uses so much memory in the UGA? 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?

During my investigation I also found out that my sessions have more cursors open than is specified by init.ora parameter open_cursors or session_cached_cursors. This quite puzzles me, how can this be?

Thanks for any help on this.

Connected to Oracle9i Release 9.2.0.5.0 Connected as system

SQL>
SQL> select name, value from
  2 v$parameter where name in ('open_cursors',
'session_cached_cursors',

  3                               'workarea_size_policy',

'pga_aggregate_target');

NAME                      VALUE
-------------------------------------
session_cached_cursors    300
open_cursors              255
pga_aggregate_target      335544320
workarea_size_policy      AUTO

SQL> select * from v$sysstat where name like '%uga%';

STATISTIC #NAME CLASS VALUE

---------- ----------------- ---------- ----------
15 session uga memory                 1  963567588
16 session uga memory max             1 7183356652

SQL> select * from (
  2     select vs.sid, round(value/1024/1024) from v$statname n
  3     join v$sesstat s on (s.STATISTIC# = n.STATISTIC#)
  4     join v$session vs on (vs.sid=s.sid)
  5     where name like 'session uga memory'
  6     order by value desc)

  7 where rownum < 4;

       SID ROUND(VALUE/1024/1024)

---------- ----------------------
       335                     32
       203                     28
       164                     25

SQL> select count(*) from v$open_cursor where sid=335;

  COUNT(*)

---

       453 Received on Thu Mar 24 2005 - 04:21:59 CST