ORACLE Memory Management !

The Memory Stucture
There are five memory stuctures 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.
SGA
Oracle Architecture
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

Dictionary cache is a collection of database tables and views containing information about the
database, its structures and users.

Buffers for parallel execution messages and control structures
Use the parameter SHARED_POOL_SIZE in the init.ora file to adjust

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 least used list holds free buffers (no useful
data) , pinned buffers (being accessed) and dirty buffers that have not yet been moved to the
write list. This pool is broken down into three pools, recycle (goal here is to age out a block as
soon as it is no longer needed), keep (goal is to keep warm/hot block in the pool for as long as
possible) and default buffer pool.
The size is deteremnied by the following in the system paramenter file:
DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE

Use the parameter DB_BLOCK_BUFFERS in the init.ora file to adjust

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. However the contents of this area are flushed:
· Every three seconds
· Whenever someone commits
· When its gets one third full or contains 1MB of cached redo log data.
These entries contain necessary information to reconstruct/redo changes by the INSERT,
UPDATE, DELETE, CREATE, ALTER and DROP commands.

Use the parameter LOG_BUFFER in the init.ora file to adjust
Large Pool This is an optional memory area that provide large areas of memory for:
· MTS - 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.
Oracle Architecture
Parallel execution message buffers.
Use the parameter LARGE_POOL_SIZE in the init.ora file to adjust
Java Pool
used to execute java code within the database.
Use the parameter JAVA_POOL_SIZE in the init.ora file to adjust
Display information regarding the SGA
Memory area Memory
location Displaying the information
SGA sql> show sga;
SGA (detailed) use table v$sgastat;
Buffer cache SGA use table v$bh;
Display memory allocation SGA compute sum of bytes on pool
break on pool skip 1
select pool, name bytes from v$sgastat order by pool, name;

Display the redo buffer SGA select * from v$sga where name = 'Redo Buffers';

Library cache shared pool use table v$librarycache;
Dictionary cache shared pool use table v$rowcache;

PGA and UGA
If you have MTS configured then the UGA must be stored in a memory stucture that everyone has access to and this would be the SGA. However if you are using a dedicated server connection then the UGA becomes part of the PGA

Each process connected to the database requires its own area of memeory this is know as the Program Global Area (PGA). This area stores variables, arrays and other information that do not need to be shared with other processes.

Oracle Architecture Session Information:
PGA in an instance running without the multi-threaded server
(named Shared Server in Oracle9i) requires additional memory for
the user's session, such as private SQL areas and other information.
If the instance is running the multi-threaded server, this extra
memory is not in the PGA, but is instead allocated in the SGA (the Shared Pool).

Stack space
The memory allocated to hold a sessions variables, arrays, etc and
other information relating to the session. However for a shared
server the session memory is shared and not private Display information regarding the PGA
Session information v$sesstat, v$statname

Display PGA and UGA usage
select a.name, b.name from v$statname a, v$mystat b where a.statistic# = b.statistic#
and a.name like '%ga %';

Tunning the PGA/UGA
alter the following parameters
SORT_AREA_SIZE (PGA)
SORT_AREA_RETAINED_SIZE (UGA)
Shrink the PGA area > exec dbms_session.free_unused_user_memory;