| 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
Oracle Database is a powerful, flexible, and complex system that offers numerous configuration options to ensure the optimal performance of your applications. Tuning Oracle is a multi-faceted process, and understanding how SQL SELECT statements and memory allocation interact is crucial to maximize database efficiency.
- SQL SELECT: SQL SELECT statements are fundamental database operations, used to retrieve data from tables within the database. They execute within Oracle's system memory, making use of the instance's memory components. Each SELECT statement's performance depends on a multitude of factors, including how it's written, the data distribution, database design, and indexing strategy. Badly written or sub-optimal SQL SELECTs can lead to performance problems. For example, a SELECT statement without a WHERE clause on a large table will lead to a full table scan, consuming excessive CPU and I/O resources, and in some cases causing high memory consumption as well. Similarly, complex joins, subqueries, or inefficient WHERE conditions may lead to increased memory usage and CPU consumption, thereby slowing the response times. SQL tuning involves refining these queries to ensure they use resources as efficiently as possible, leveraging appropriate indexes, avoiding full table scans, and limiting the returned data.
- Memory Allocation: Oracle Database instance memory is divided into two main areas: the System Global Area (SGA) and the Program Global Area (PGA). The SGA includes multiple components such as the database buffer cache, shared pool, and redo log buffer. The PGA, on the other hand, is a memory region that contains data and control information for a server process—a memory region private to each server process. Properly allocating memory to these areas can dramatically affect database performance. For example, the buffer cache stores copies of data blocks read from data files. A larger buffer cache means more data can be kept in memory, reducing physical I/O operations and increasing database performance.
The shared pool stores most of the SQL execution plans. If it's too small, the database will continually swap out execution plans to make room for new ones, leading to performance degradation. Similarly, the PGA, which stores data and control information for server processes, must be appropriately sized. Inadequate PGA size could lead to excessive I/O operations, thus negatively impacting performance.
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.
Interplay of SQL SELECTs and Memory Allocation
The interplay between SQL SELECT statements and memory allocation is significant. When a SQL SELECT statement is executed, it's parsed and an execution plan is generated. This plan is stored in the shared pool. If the shared pool is not large enough to store all execution plans, Oracle may need to age out older plans to accommodate new ones, causing additional parsing activity when the aged-out statements are executed again. This can degrade performance. SELECT statements that retrieve large amounts of data may require significant PGA memory for sorting and joining operations. If the PGA is undersized, these operations may need to spill over to disk, leading to slower performance.
Therefore, optimizing SQL SELECTs can lead to more efficient use of memory, and allocating sufficient memory to Oracle's various components can enhance the performance of SELECT statements. Thus, tuning both these aspects in harmony is essential for optimal Oracle Database performance. Remember, tools like Oracle's SQL Tuning Advisor and Automatic Memory Management can help you automate some aspects of this process, but they don't replace a deep understanding of SQL and Oracle architecture.
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