Oracle Memory Structure

Oracle Memory Structure

The memory structure of Oracle is divided into SGA and PGA. SGA is dynamically allocated. It is configured by the sga_max_size parameter and allocated when the oracle database instance is started, share an SGA for all session connections; For PGA, each user creates a PAG memory zone for session connections.

Shared Pool: stores the execution structure (Execution Plan) of the recently executed SQL statement that has been defined (data dictionary). It mainly consists of library cache and data dictionary cache. The size parameter is shared_pool_size.

Library cache mainly stores shared SQL area and shared pl/SQL area.

Data dictionary cache is mainly used to store data systems, dynamic performance views of systems, and system tables.

The major concern of SGA is that database buffer cache is mainly used to store data blocks of data files. It directly stores the data read from the hard disk in the memory, improving the data operation speed. For user operations on data, the first step is to find the data in the database buffer cache. If you can find the data, the data will be directly returned and will not be found from the hard disk, improving the data response speed. An important indicator reflecting database performance is the hit rate of database buffer cache. The main parameters include db_keep_cache_size, db_cache_size, and db_recycle_cache_size.

Redo log buffer: it is the cache of the redo log of the data file. If you need to recover data and read the redo log file, the database operation records will be read from the redo log buffer first. The Operation Records of the database are first written to the redo log buffer, and then stored in the data file redo log files.

Larger pool: When Rman is backed up, the larger pool is used for data caching. Some server procees I/O operations also need to use the larger pool for data caching.

Java pool: The size is determined by the java_pool_size parameter. For Methods written in the java language in oracle, java pool is used during execution. This cache area is mainly used for java-related operations.