RE: Calculating UGA size

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Wed, 20 Sep 2000 05:17:08 +1000
Message-Id: <10624.117374@fatcity.com>

Hi Yong,

The 'session uga memory max' statistic includes the persistent and runtime memory for cursors that were open at the time when the high-water mark of UGA memory usage was set. However, the runtime memory shown in V$SQLAREA is just the "ephemeral frame" and does not include cursor work heap structures such as the sort area. On the other hand, the V$SQLAREA query proposed would include memory for cursors that were not open concurrently. So, although you are looking at some of the right information, there is not much that you can conclude from it.

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
@
@ Going to OpenWorld?
@ Catch the Ixora performance tuning seminar too!
@ See http://www.ixora.com.au/seminars/ for details.

-----Original Message-----
From: yong huang [mailto:yong321_at_yahoo.com] Sent: Wednesday, 20 September 2000 2:56
To: Multiple recipients of list ORACLE-L Subject: Re: Calculating UGA size

Hi, Steve,

I wonder if it helps with a sum of runtime and persistent memory from v$sqlarea:

select parsing_user_id, sum(persistent_mem+runtime_mem) from v$sqlarea
group by parsing_user_id;

But I noticed the value returned is very much higher than the sum of session uga memory (max or not) for the corresponding users. Probably because UGA is only a small part of PGA, which is what my persistent_mem+runtime_mem is?

Steve Adams wrote:

The UGA size is dependent on both the SQL issued by the session, and the data returned, as well as the parameter settings. So there is no "formula" that can be used to predict the UGA size. Instead, you should test the application, measure the UGA size for a representative session and extrapolate. To measure the UGA
size, look at the 'session uga memory max' statistic in V$SESSTAT.