Oracle's memory structure consists of SGA system global area, PGA program global area and the software code area
All the user process, the server process can be shared SGA area, which is the process of different users to communicate with the central server process.
SGA is divided into the following sections:
1) data cache
A) for the preservation of the most recent data file read from the data block, or store frequently used recent data, which data can be shared by all users.
B) The size of the part specified by the initialization parameter DB_CACHE_SIZE, typically 1% of database size
C) that part of it by the three smaller buffer pools, each pool is used to store a cache of data with different access characteristics, in order to improve efficiency. Users can also
For specific objects (such as creating, changing table or index) the use of storage clause specified BUFFER_POOL parameter to set the object to use
a) keep to keep buffer pool: one of the data (ie the cache block) to long-term preservation, until the database is shut down. Applicable to long-term preservation and in memory
Frequently accessed objects, such as code tables.
b) recycle buffer pool recovery: Once the data which will be swapped out after use.
c) default default buffer pool: the object using the default buffer pool. oracle using the least recently used LRU algorithm to swap out.
Note: keep, and recycle the size of the initialization parameter to specify BUFFER_POOL_KEEP and BUFFER_POOL_RECYCLE
Parameters DB_CACHE_SIZE minus the value of these two parameters after the default is the size of the remaining
D) data cache size equal to the cache by the number of blocks. The same with the OS block size. Can be divided into three categories
a) dirty buffers dirty cache block: When a SQL statement on a cache block in the revised data, this cache block will be marked as dirty cache blocks,
The block of data will be persisted to a data file.
b) free buffers free cache blocks: these blocks there is no data, they are waiting to be written to the data.
c) pinned buffers cache hit blocks: Save the most recent data from being accessed. These data will always be stored in memory.
E) Oracle management through the following two lists of Appeal cache block:
a) dirty list: save has been modified but not yet written the data file cache block dirty
b) LRU list: Save the hit, idle and not be moved to the list of dirty dirty cache blocks. LRU list can be viewed as a queue.
Was frequently visited in the team first, on the tail will be the first to be removed.
2. Redo log cache
Used to record insert, update, delete statements, and create, alter, drop and other statements of redo records. Log_buffer developed by the parameters.
3. Shared Pool
Save the last executed SQL statements, PL / SQL process and package the data dictionary information, locks, and other control structure information. It is SQL,
PL / SQL for syntax parsing, compilation, execution memory area, mainly by the data dictionary cache and library cache component. Designated by the shared_pool_size.
1) The data dictionary cache (dictionary cache)
A) is used to store frequently used recent data dictionary information, such as table definitions, column definitions, usernames, passwords, permissions, database structure information.
B) Oracle run-time required to access the data dictionary to parse the SQL, to determine whether the object exists, verify permissions. If the data in the cache does not exist
Then take the data file to read the data dictionary.
C) data dictionary cache is stored in one of a record, while the other buffer is the data block stored
2) the library cache
Used to keep the last parsed SQL statement PL / SQL
Oralce will be shared each SQL decomposed and non-shared parts, stored in the shared SQL areas and private SQL area
A) shared SQL area: storage is the recent implementation of the SQL statement syntax tree after parsing and optimized execution plan
B) Private SQL area: storage and private conversations and user-related information, such as: bind variables, the environment, and session parameters.
C) PL / SQL procedures and packages District: Storage PL / SQL parsing code execution. PL / SQL in the SQL or stored in a shared SQL area
D) locks and other control structures District: Oracle instance storage of information required internal operations, such as various locks, latches, registers and other values. Background process needs to
Access to them.
4. Large pool (optional)
Tai Chi for operations that require large memory space to provide relatively independent of memory in order to improve the performance of these operations. Determine the size by large_pool_size
Memory-intensive operations, including:
1) The database backup and recovery, such as the use of RMAN backup in the tape device, dump, restore and other operations.
2) a large number of sort operations with SQL statements.
3) parallel database operations.
Note: If there is no area in the SGA to create a large pool of memory space required for operation of the appeal will take the shared pool of memory
5.Java pool (optional)
After the Oracle 8i, Oracle adds support for Java language, Java provides a pool for the storage of Java code, Java statement syntax analysis table
Java statements in program implementation and Java program development. Used to determine the size of java_pool_size