tuning uga?

From: David Spaisman <david.spaisman_at_compaq.com>
Date: Tue, 13 Jul 1999 14:46:22 -0400
Message-ID: <7mg1kd$ldc$1@mailint03.im.hou.compaq.com>

Hello:

Users have been complaining about a slow system. This application is on Oracle 8.0.4, sp4 on NT 4.0.

The optimizer mode is set to choose.
I am in the process of checking some of the sql code running. The shared pool is set to 125 mb. The sort_area_size is set to 1mb.

One example of the query running is below: Note uga is 5 mb, although an extreme example this code is based upon views created on views. I know this is a no-no but the application was purchased and we were stuck with the heavily normalized design and the views had to be built this way to get certain data.

My questions are :

1. what parameters do I need to revise to handle this size uga?
2. other than reorg some indexes and trying to fix the code by some better or new indices, what else can I do?

THanks.

David Spaisman

---

• session 17
  --
  --
  

select distinct cust_name, mla, schedule from v_dp_allasset where cust_id= '975624980' and schedule=
'200000000983'

• Query run on a session id--

select value from v$sesstat s, v$statname n where s.statistic# = n.statistic#
and n.name = 'session uga memory max'
and s.sid = &1;

• Result is:
  --
  SQL> @C:\work\fkoh\sharemem 17 old 4: and s.sid = &1 new 4: and s.sid = 17

VALUE

---

5612828

SUM(SHARABLE_MEM)

---

60863757 Received on Tue Jul 13 1999 - 13:46:22 CDT