Oracle Memory Architecture

Oracle uses three kinds of memory structures

SGA
(System Global Area)
is a large part of memory that all the oracle background processes access.
PGA
(Process Global Area)
This is memory that is private to a single process or thread and is not accessible by any other process or thread
UGA
(User Global Area)

This is memory that is assoicated with your session, it can be found in the PGA or SGA depending on whether you are connected to the database via shared server

Shared Server - the UGA will be in the SGA
Dedicated Server - the UGA will be in the PGA

SGA

There are five memory structures that make up the System Global Area (SGA). The SGA will store many internal data structures that all processes need access to, cache data from disk, cache redo data before writing to disk, hold parsed SQL plans and so on.

Shared Pool

The shared pool consists of the following areas:

Library cache includes the shared SQL area, private SQL areas, PL/SQL procedures and packages the control structures such as locks and library cache handles. Oracle code is first parsed, then executed , this parsed code is stored in the library cache, oracle first checks the library cache to see if there is an already parsed and ready to execute form of the statement in there, if there is this will reduce CPU time considerably, this is called a soft parse, If Oracle has to parse it then this is called a hard parse. If there is not enough room in the cache oracle will remove older parsed code, obviously it is better to keep as much parsed code in the library cache as possible. Keep an eye on missed cache hits which is an indication that a lot of hard parsing is going on.

Dictionary cache is a collection of database tables and views containing information about the database, its structures, privileges and users. When statements are issued oracle will check permissions, access, etc and will obtain this information from its dictionary cache, if the information is not in the cache then it has to be read in from the disk and placed in to the cache. The more information held in the cache the less oracle has to access the slow disks.

The parameter SHARED_POOL_SIZE is used to determine the size of the shared pool, there is no way to adjust the caches independently, you can only adjust the shared pool size.

The shared pool uses a LRU (least recently used) list to maintain what is held in the buffer, see buffer cache for more details on the LRU.

You can clear down the shared pool area by using the following command

    alter system flush shared_pool;

Buffer cache

This area holds copies of read data blocks from the datafiles. The buffers in the cache contain two lists, the write list and the least used list (LRU). The write list holds dirty buffers which contain modified data not yet written to disk.

The LRU list has the following

  • free buffers hold no useful data and can be reused
  • pinned buffers actively being used by user sessions
  • dirty buffers contain data that has been read from disk and modified but hasn't been written to disk

It's the database writers job to make sure that they are enough free buffers available to users session, if not then it will write out dirty buffers to disk to free up the cache.

There are 3 buffer caches

  • Default buffer cache, which is everything not assigned to the keep or recycle buffer pools, DB_CACHE_SIZE
  • Keep buffer cache which keeps the data in memory (goal is to keep warm/hot blocks in the pool for as long as possible), DB_KEEP_CACHE_SIZE.
  • Recycle buffer cache which removes data immediately from the cache after use (goal here is to age out a blocks as soon as it is no longer needed), DB_RECYCLE_CACHE_SIZE.

The standard block size is determined by the DB_CACHE_SIZE, if tablespaces are created with a different block sizes then you must also create an entry to match that block size.

DB_2K_CACHE_SIZE (used with tablespace block size of 2k)
DB_4K_CACHE_SIZE (used with tablespace block size of 4k)
DB_8K_CACHE_SIZE (used with tablespace block size of 8k)
DB_16K_CACHE_SIZE (used with tablespace block size of 16k)
DB_32K_CACHE_SIZE (used with tablespace block size of 32k)

buffer cache hit ratio is used to determine if the buffer cache is sized correctly, the higher the value the more is being read from the cache.

     hit rate = (1 - (physical reads / logical reads)) * 100

You can clear down the buffer pool area by using the following command

    alter system flush buffer_cache;

Redo buffer

The redo buffer is where data that needs to be written to the online redo logs will be cached temporarily before it is written to disk, this area is normally less than a couple of megabytes in size. These entries contain necessary information to reconstruct/redo changes by the INSERT, UPDATE, DELETE, CREATE, ALTER and DROP commands.

The contents of this buffer are flushed:

  • Every three seconds
  • Whenever someone commits a transaction
  • When its gets one third full or contains 1MB of cached redo log data.
  • When LGWR is asked to switch logs

Use the parameter LOG_BUFFER parameter to adjust but be-careful increasing it too large as it will reduce your I/O but commits will take longer.

Large Pool

This is an optional memory area that provide large areas of memory for:

  • Shared Server - to allocate the UGA region in the SGA
  • Parallel execution of statements - to allow for the allocation of inter-processing message buffers, used to coordinate the parallel query servers.
  • Backup - for RMAN disk I/O buffers
The large pool is basically a non-cached version of the shared pool.

Use the parameter LARGE_POOL_SIZE parameter to adjust

Java Pool

Used to execute java code within the database.

Use the parameter JAVA_POOL_SIZE parameter to adjust (default is 20MB)

Streams Pool

Streams are used for enabling data sharing between databases or application environment.

Use the parameter STREAMS_POOL_SIZE parameter to adjust

The fixed SGA contains a set of variables that point to the other components of the SGA, and variables that contain the values of various parameters., the area is a kind of bootstrap section of the SGA, something that Oracle uses to find other bits and pieces of the SGA

For more information regarding setting up the SGA click here.

PGA and UGA

The PGA (Process Global Area) is a specific piece of memory that is associated with a single process or thread, it is not accessible by any other process or thread, note that each of Oracles background processes have a PGA area. The UGA (User Global Area) is your state information, this area of memory will be accessed by your current session, depending on the connection type (shared server) the UGA can be located in the SGA which is accessible by any one of the shared server processes, because a dedicated connection does not use shared servers the memory will be located in the PGA

Memory Area Dedicated Server Shared Server
Nature of session memory Private Shared
Location of the persistent area PGA SGA
Location of part of the runtim area for select statements PGA PGA
Location of the runtime area for DML/DDL statements PGA PGA

Oracle creates a PGA area for each users session, this area holds data and control information, the PGA is exclusively used by the users session. Users cursors, sort operations are all stored in the PGA. The PGA is split in to two areas

Session Information
(runtime area)
PGA in an instance running with a shared server requires additional memory for the user's session, such as private SQL areas and other information.
Stack space
(private sql area)
The memory allocated to hold a sessions variables, arrays, etc and other information relating to the session.

Automatic PGA Management

To reduce response times sorts should be performed in the PGA cache area (optimal mode operation), otherwise the sort will spill on to the disk (single-pass / multiple-pass operation) this will reduce performance, so there is a direct relationship between the size of the PGA and query performance. You can manually tune the below to increase performance

Staring with Oracle 9i there is a new to manage the above settings that is to let oracle manage the PGA area automatically by setting the parameter following parameters Oracle will automatically adjust the PGA area basic on users demand.

Oracle will try and keep the PGA under the target value, but if you exceed this value Oracle will perform multi-pass operations (disk operations).

System Parameters
workarea_size_policy manual or auto (default)
pga_aggregate_target total amount of memory allocated to the PGA
 
PGA/UGA amount used

select a.name, to_char(b.value, '999,999,999') value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name like '%ga memory%';

Display if using memory or disk sorts set autotrace traceonly statistics;
set autotrace off;
Display background process PGA memory usage select program, pga_used_mem, pga_alloc_mem, pga_max_mem from v$process;