RelationalDBDesign RelationalDBDesign


Oracle Instance  «Prev  Next»
Lesson 6 User Global Area and session memory
Objective List the UGA and session memory considerations.

Memory allocation for Shared Server Users

Oracle users using Network Services
  1. can connect to the database using dedicated servers, or
  2. multiple clients can use a pool of shared servers.
This requires session memory (also known as the UGA) for every session that needs to be accessible to every Oracle Shared server.
UGA comes out of the shared pool instead of the PGA (process memory). If you are using Oracle shared server, then you will need to allow enough memory for all the shared server users to put their session memory in the shared pool.
When using Oracle Shared Server, every user's session memory is in the shared pool and the impact is multiplied by the total concurrent users. Insert, update, delete, and anonymous blocks complete the execution in one round trip. All the memory that is allocated on the server for the execute comes from the PGA and is freed before the call returns to the user.

Table 2-6 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.

SQL selects and memory allocation

But in the case of SQL selects, memory required to execute the statement (which can be very large if a sort is involved), is not freed until the end-of-fetch is reached or the query is cancelled. In these situations, you can get relief by using the (OCI) Oracle Call Interface features to do an exact fetch, and cancel helps free memory back to the pool.

Free memory by closing cached cursors

If the application logic has been embedded into server-side PL/SQL, a large number of cursors may be getting cached on the server for every user. Though this results in reduced latch contention and faster response, it does use more memory in the UGA.
Setting the close_cached_open_cursors init.ora to TRUE closes the PL/SQL cached cursors on the server, freeing the memory.
Legacy Note for Oracle8: If there is a large pool, configured MTS will ONLY try to use this pool for a session's UGA. When a new session is started a small amount of memory (known as the fixed UGA) is allocated in the shared pool and the rest of the session memory (UGA) is taken from the large pool.

Query for total session memory

To select the total session UGA memory for all the currently logged on users, issue the following query:

select 
   sum(value) all_sess_mem 
from 
   v$sesstat s, 
   v$statname n 
where 
   s.statistic# = n.statistic# 
and 
   n.name = 'session uga memory max';
Here is the output:
SQL> @mts
ALL_SESS_MEM
------------
     1365924

You can view more detail for each user's memory by using the following query.

Query for each user's memorys


column all_sess_mem format 999,999,999;
column sess_mem     format 999,999,999;
select 
   sum(value) all_sess_mem 
from 
   v$sesstat s, 
   v$statname n 
where 
   s.statistic# = n.statistic# 
and 
   n.name = 'session uga memory max';

select 
   substr(b.username,1,10) username,
   substr(b.program,1,30)  program,
   value sess_mem 
from 
   v$session b,
   v$sesstat s, 
   v$statname n 
where 
   b.sid = s.sid
and
   s.statistic# = n.statistic# 
and 
   n.name = 'session uga memory' 
and 
   s.sid  in
      (select sid from v$session)
order by 3 desc;

select 
   substr(b.username,1,10) username,
   substr(b.program,1,30)  program,
   value sess_max_mem 
from 
   v$session b,
   v$sesstat s, 
   v$statname n 
where 
   b.sid = s.sid
and
  s.statistic# = n.statistic# 
and 
   n.name = 'session uga memory max' 
and 
   s.sid  in
      (select sid from v$session)
order by 3 desc
;

The following is the output of the query.

Output of Query

USERNAME   PROGRAM                            SESS_MEM
---------- ------------------------------ ------------
QDBA                                           427,600
OPS$ORACLE sqlplus@diogenes (TNS V1-V2)        276,648
OPERATOR   L:\VS\RR01\VS_BIN\lisp.exe           25,944
OPERATOR   L:\VS\RR01\VS_BIN\lisp.exe           23,692
OPERATOR   LISP.EXE                             22,448
OPERATOR   LISP.EXE                             22,072
OPERATOR   LISP.EXE                             22,024
           oracle@diogenes (RECO)               20,216
OPERATOR   LISP.EXE                             20,184
OPERATOR   LISP.EXE                             18,180
OPERATOR   LISP.EXE                             17,676
OPERATOR   lisp.exe                             16,652
OPERATOR   LISP.EXE                             16,320
OPERATOR   L:\VS\RR01\VS_BIN\lisp.exe           15,956
OPERATOR   H:\VS\RR01\VS_BIN\OPTICAL.EXE        13,868
           oracle@diogenes (SMON)               13,276
OPERATOR   LISP.EXE                             13,224
OPERATOR   LISP.EXE                             12,972
OPERATOR   LISP.EXE                             12,884
OPERATOR   LISP.EXE                             12,648
OPERATOR   LISP.EXE                             12,568
OPERATOR   LISP.EXE                             12,188
OPERATOR   LISP.EXE                             12,100
OPERATOR   LISP.EXE                             12,092
                                                11,344
                                                11,344
OPERATOR   LISP.EXE                             11,148
OPERATOR   LISP.EXE                             11,036
OPERATOR   LISP.EXE                             10,896
OPERATOR   LISP.EXE                             10,884
OPERATOR   LISP.EXE                             10,808
OPERATOR   LISP.EXE                             10,708
           oracle@diogenes (DBWR)               10,540
OPERATOR   LISP.EXE                             10,516
OPERATOR   LISP.EXE                             10,236
OPERATOR   LISP.EXE                             10,220
OPERATOR   LISP.EXE                             10,160
OPERATOR   LISP.EXE                              9,952
OPERATOR   lisp.exe                              9,548
OPERATOR   LISP.EXE                              9,520
           oracle@diogenes (LGWR)                8,664
           oracle@diogenes (PMON)                8,128
           oracle@diogenes (ARCH)                8,128
OPERATOR   LISP.EXE                              8,028
           oracle@diogenes (DB01)                7,088
           oracle@diogenes (DB03)                7,088
           oracle@diogenes (DB04)                7,088
           oracle@diogenes (DB02)                7,088
           oracle@diogenes (CKPT)                6,232

USERNAME   PROGRAM                        SESS_MAX_MEM
---------- ------------------------------ ------------
QDBA                                            592492
OPS$ORACLE sqlplus@diogenes (TNS V1-V2)         352932
OPERATOR   L:\VS\RR01\VS_BIN\lisp.exe            25944
OPERATOR   L:\VS\RR01\VS_BIN\lisp.exe            23692
OPERATOR   LISP.EXE                              22448
OPERATOR   LISP.EXE                              22072
OPERATOR   LISP.EXE                              22024
           oracle@diogenes (RECO)                20216
OPERATOR   LISP.EXE                              20184
OPERATOR   LISP.EXE                              18180
OPERATOR   LISP.EXE                              17676
OPERATOR   lisp.exe                              16652
OPERATOR   LISP.EXE                              16476
OPERATOR   LISP.EXE                              16320
OPERATOR   L:\VS\RR01\VS_BIN\lisp.exe            15956
OPERATOR   LISP.EXE                              15360
OPERATOR   H:\VS\RR01\VS_BIN\OPTICAL.EXE         13868
           oracle@diogenes (SMON)                13276
OPERATOR   LISP.EXE                              12972
OPERATOR   LISP.EXE                              12884
OPERATOR   LISP.EXE                              12648
OPERATOR   LISP.EXE                              12568
OPERATOR   LISP.EXE                              12188
OPERATOR   LISP.EXE                              12100
OPERATOR   LISP.EXE                              12092
                                                 11344
                                                 11344
OPERATOR   LISP.EXE                              11148
OPERATOR   LISP.EXE                              11036
OPERATOR   LISP.EXE                              10896
OPERATOR   LISP.EXE                              10808
OPERATOR   LISP.EXE                              10708
           oracle@diogenes (DBWR)                10540
OPERATOR   LISP.EXE                              10516
OPERATOR   LISP.EXE                              10236
OPERATOR   LISP.EXE                              10220
OPERATOR   LISP.EXE                              10160
OPERATOR   LISP.EXE                               9952
OPERATOR   lisp.exe                               9548
OPERATOR   LISP.EXE                               9520
           oracle@diogenes (LGWR)                 8664
           oracle@diogenes (PMON)                 8128
           oracle@diogenes (ARCH)                 8128
OPERATOR   LISP.EXE                               8028
           oracle@diogenes (DB01)                 7088
           oracle@diogenes (DB03)                 7088
           oracle@diogenes (DB04)                 7088
           oracle@diogenes (DB02)                 7088
           oracle@diogenes (CKPT)                 6232

The purpose of these scripts is to locate a specific user who may be using a disproportional amount of the UGA memory in the shared pool. In the next lesson, you will examine tuning considerations for the second important area of the SGA, the data buffer cache.

Shared Pool Tuning - Quiz

Before you continue, click the Quiz link below to test your knowledge of shared pool tuning so far.
Shared Pool Tuning - Quiz