| 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
- can connect to the database using dedicated servers, or
- 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