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

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.
  1. 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.
  2. 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:

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

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;
   sum(value) all_sess_mem 
   v$sesstat s, 
   v$statname n 
   s.statistic# = n.statistic# 
and = 'session uga memory max';

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

   substr(b.username,1,10) username,
   substr(b.program,1,30)  program,
   value sess_max_mem 
   v$session b,
   v$sesstat s, 
   v$statname n 
   b.sid = s.sid
  s.statistic# = n.statistic# 
and = 'session uga memory max' 
   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
           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
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
           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
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