Oracle Memory Architecture
Oracle uses three kinds of memory structures
(System Global Area)
|is a large part of memory that all the oracle background processes access.|
(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|
(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
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.
The shared pool consists of the following areas:
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;
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
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
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.
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;
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:
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.
This is an optional memory area that provide large areas of memory for:
Use the parameter LARGE_POOL_SIZE parameter to adjust
Used to execute java code within the database.
Use the parameter JAVA_POOL_SIZE parameter to adjust (default is 20MB)
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
|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.|
(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).
|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
|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;|