Memory Structure
Oracle's memory structure consists of two memory areas known as:
SGA(System global area): Allocated at instance start up,and is a fundamental componet of an Oracle instance.
PGA(Program Global area):Allocated when the server process is started.
●SGA:
The SGA consists of serval memory structures:
Sharaed pool
Database Buffer Cache
Redo log buffer
Other structures(for example,lock and latch management,statiscal data)
There are two additional memory Structures that can be configured with the SGA
Large Pool
Java Pool
String pool(11G)
$ps -ef | grep oracle
SQL>show SGA
SGA
・Is dynamic
・Sized by the SGA_MAX_SIZE parameter
・Allocated and tracked in granules by SGA components
Contiguous virtual memory allocation
Granule size based on total estimated SGA_MAX_SIZE
(SGA<128M,Granules is 4M; SGA >128M,Granules is 16M)
SQL>select component,granule_size from v$sga_dynamic_components;
SGA
DB_CACHE_SIZE
LOG_BUFFER
SHARED_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE
■ Sharaed pool
Used to store:
・Most recently executed SQL statements
・Most recently used data definitions
It consists of two key performance-ralated memory structures;
・Library Cache
・Data Dictionary Cache
Sized by the parameter SHARED_POOL_SIZE
SQL>alter System set SHARED_POOL_SIZE =64M
Library Cache
Stores information about the mosst recently used SQL and PL/SQL statements;
Enables the sharing of commonly used statements;
Consists of two strutures:
・Shared SQL area
・Shared PL/SQL area
Size determined by the Shared Pool Sizing
Data Dictionary Cache
A collection of the most recently used definitions in the database
Includes information about database file,tables,indexes,columns,users,privileges
and other database objects
During the parse phase,the server process looks at the data dictionary for information to resolve objects names and valaidate access
Caching data dictionary information into memory improves respose time on queries and DML
Size determined by the Shared pool sizing
■ Database Buffer Cache
Stores copies of data blockes that have been retrieved from the data files
Enables greaat performance gains when you obtain and update data
Managed through an LRU algorithm
DB_BLOCK_SIZE detemines primary block size
Consists of independent subcaches:
DB_CACHE_SIZE
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
Can by dynamically resized
SQL>alter system set DB_CACHE_SIZE=96M;
DB_CACHE_ADVICE set to gathe statistics for predicting differnt cache size behavior
Statistics displayed by v$DB_CACHE_ADVICE
■ Redo Log Buffer
Records all changes made to the database data blocks
Primary purpose is recovery
Changes recorded within are called redo entries
Redo entries contain information to reconstructe or redo changes
Size defined by LOG_BUFFER
■ Large Pool
An optional area of memory in the SGA
Relieves the burden placed on the Shared pool
Used for
Session memory(UGA) for the Shared Server
I/O server processes
Backup and restore opreation or RMAN(备份工具)
Parallel execution message buffers
PARALLEL_AUTOMATIC_TUNING set to TURE
Does not use an LRU list
Sized by LARGE_POOL_SIZE
Can be dynamically resized
■ Java Pool
(Oracle 支持用Java来编写存储过程)
Servieces parsing requirements for Java Commands
Requirde if installing and using Java
Sized by JAVA_POOL_SIZE parameter
●PGA
Memory reserved for eache user process connecting to an Oracle database
Allocated when a process is created
Deallocated when the process is terinated
Used by only one process
