Oracle memory structure
SGASystem Global Area (SGA) is shared memory area. All users of database share information maintained in this area. The SGA and other background processes constitute an Oracle instance.
SGA size is limited by SGA_MAX_SIZE initialization parameter.
From 11g, Oracle can manage SGA and PGA completely automatically.
Shared memory:
From 11g, there are two new components in SGA viz Streams pool and Result
cache.
Non-shared memory:
PGA
The database buffer cache is the area of memory that caches database data, holding blocks from data files that have been read recently. Before a user can look at a piece of information in an Oracle database, it must first reside in the database buffer cache. Data gets into this cache based upon the Most Recently Used algorithm. Because the most recently and most frequently used data is kept in memory, less disk I/O is necessary, and overall database performance is improved.
There are 3 types of buffers – dirty, free and pinned.
Oracle uses an LRU mechanism to remove data from DB cache.
DB_CACHE_SIZE, DB_KEEP_CACHE_SIZE,
DB_RECYCLE_CACHE_SIZE determines DB cache size.
DB_CACHE_ADVICE can be set to ON/OFF/READY and the result can be viewed from V$DB_CACHE_ADVICE.
Redo log buffer is a circular buffer in SGA that holds information about changes made to data.
LOG_BUFFER determines its size.
Shared pool
Library cache contains shared SQL area, PL/SQL procedures and packages etc.
It is used for maintaining recently executed SQL commands and their execution plans.
Data dictionary cache is a collection of database tables and views containing metadata about the database, its structures, its privilege and its users. Oracle accesses data dictionary frequently during parsing of SQL statements. Data dictionary cache holds most recently used data dictionary information.
SHARED_POOL_SIZE determines size of shared pool and can be dynamically altered.
PGA contains data and information for single server process.
PGA_AGGREGATE_TARGET specifies total amount of memory that can be used by all server processes.
It is used for maintaining recently executed SQL commands and their execution plans.
Data dictionary cache is a collection of database tables and views containing metadata about the database, its structures, its privilege and its users. Oracle accesses data dictionary frequently during parsing of SQL statements. Data dictionary cache holds most recently used data dictionary information.
SHARED_POOL_SIZE determines size of shared pool and can be dynamically altered.
PGA contains data and information for single server process.
PGA_AGGREGATE_TARGET specifies total amount of memory that can be used by all server processes.
No comments:
Post a Comment