you are a new oracle dba and you would like to know what UGA is and from where memory is allocated to the UGA.
Determining an Effective Setting for Shared Server UGA Storage The exact amount of UGA
Oracle uses depends on each application. To determine an effective setting for the
large or shared pools, observe UGA use for a typical user and multiply this amount by
the estimated number of user sessions.
Table 7–3 V$SESSTAT Statistics Reflecting Memory
Statistic Description
session UGA memory The value of this statistic is the amount of memory in
bytes allocated to the session.
Session UGA memory max The value of this statistic is the maximum amount of
memory in bytes ever allocated to the session
asktom response
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4269591917792
We use UGA memory in dedicated server mode. It is just where is the UGA located — in
dedicated server mode, it’s in the PGA. In shared server mode, it’s in the SGA.
If you have my book — i describe these structures in some detail, here is a short
extract:
…
PGA and UGA
As stated earlier, the PGA is a process piece of memory. This is memory specific to a
single operating system process or thread. This memory is not accessible by any other
process/thread in the system. It is typically allocated via the C run-time call
malloc(), and may grow (and shrink even) at run-time. The PGA is never allocated out of
Oracle’s SGA – it is always allocated locally by the process or thread.
The UGA is in effect, your session’s state. It is memory that your session must always be
able to get to. The location of the UGA is wholly dependent on how Oracle has been
configured to accept connections. If you have configured MTS, then the UGA must be
stored in a memory structure that everyone has access to – and that would be the SGA. In
this way, your session can use any one of the shared servers, since any one of them can
read and write your sessions data. On the other hand, if you are using a dedicated
server connection, this need for universal access to your session state goes away, and
the UGA becomes virtually synonymous with the PGA – it will in fact be contained in the
PGA. In fact, when you look at the system statistics, you’ll find the UGA reported in the
PGA in dedicated server mode (the PGA will be greater then or equal to the UGA memory
used, the PGA memory size will include the UGA size as well).
Followup August 28, 2009 – 4pm Central time zone:
Ok, let me turn this around on you
you have the definitions of what these are:
(‘CPU used by this session’,’logons current’,’opened cursors current’,’parse time cpu’,’parse time elapsed’,
‘session pga memory’,’session pga memory max’,’session uga memory’,’session uga memory max’,’consistent gets’,’db block gets’,’physical reads’)do YOU have what YOU need?
If you just want memory, cpu, IO – you have lots more than what you asked for.
Do you use shared server? Do you use dedicated server – that would affect the answer greatly (in shared server, the UGA is separate from the PGA, in dedicated the UGA is a subset of a the PGA. In shared server – the pga is soley used by the process – not the session – a session borrows a process for a short bit of time – you would count it differently)
