Oracle Memory Structure

Oracle memory structure Oracle's three main memory structures: System Global Area (SGA): This is a large shared memory segment, almost all Oracle processes need to access the process Global Area (PGA) in this region: this is a process or thread-specific memory, and other processes/Threads cannot access it. Www.2cto.com User Global Area (UGA): This Area is associated with a specific session. It may be allocated in SGA or PGA, depending on whether a dedicated server is used or a Shared Server is used to connect to the database. If a dedicated server is used, UGA is allocated in PGA. If a Shared Server is used, UGA is allocated in SGA. PGA is a memory segment specific to a process. This is a memory dedicated to the operating system or thread and cannot be accessed by other threads or processes in the system. PGA will never be allocated in the SGA of Oracle, but will always be allocated locally in the process or thread. UGA is your session status. Sessions can always access this part of memory.
SGA is a group of shared memory structures used by all server processes and background processes, so it is also called a global shared area. It consists of the following parts: high-speed database buffer, log buffer, sharing pool, JAVA pool, large pool and stream pool. Its maximum size is controlled by SGA_MAX_SIZE. Database Buffer Cache: used to store data blocks read from data files, determined by the initialization parameter db_catch_size. Buffer cache can be divided into three parts: Default, Keep, and Recycle. The Keep pool stores persistent data, and the Recycle pool stores frequently used data at one time. The Default pool is generally used when data is not specified to exist in the first two places, generally, our tables are stored in the Default pool. Unless you specify Store (buffer_pool keep) or Store (buffer_pool recycle) when creating a table ). Their size is specified by the db_keep_cache_size and db_recycle_cache_size parameters. When managing the buffer cache, we usually use two lists-lru list and Dirty LIST (write list or CheckPoint Queue ). Lists store pointers to the actual buffer. Generally, the read data is stored on the LRU list. The modified data is identified as Dirty, transferred to the Dirty List, and written to the data file by DBWR. Simulate the process of using the high-speed buffer for the oracle process: an SQL statement is sent. First, search for unwanted data on the LRU LIST of the high-speed buffer zone. If yes, return directly. Otherwise, you need to write data from the data file to the lru list in the high-speed buffer zone. First, find the free buffer space in LRU. If yes, directly write data from the data file to the buffer specified by the high-speed buffer lru list, at the same time, it will also move the scanned modified buffer to the Dirty List. If the LRU scan threshold is exceeded, generally 40% does not find enough free buffer, you must notify DBWR to write the buffer pointed to in the dirty list to the data file to free up space for the lru list, then fill in the data to the newly added free buffers. It can be seen that if your high-speed buffer is very small and writes constantly, it will cause a large I/O overhead. Redo Log Buffer: used to store Log entries. A Log entry is used to record changes to data. When this area is used up, the background process LGWR writes log entries to online log files on the disk. It is determined by the initialization parameter log_buffer. In the same way, the log buffer should be slightly larger, especially when there are long-running transactions, which can greatly reduce I/O. Shared Pool: used to store SQL statements, PL/SQL code, data dictionary, resource locks, and other control information. It is controlled by the initialization parameter SHARED_POOL_SIZE.
It contains the following buffers: www.2cto.com Library Buffer (Library Cache): stores parsed and executed SQL and PL/SQL code. It can be used to share the parsing tree and execution plan to speed up statement execution. Shared SQL zone: stores the parsing tree and execution plan of SQL statements. Private SQL zone: these items are only private to sessions and generally contain some variables and session parameters. Data Dictionary Cache: stores frequently accessed Data Dictionary information to prevent excessive access to system tablespace. Store data in the form of rows, so it is also called the row storage area. Large Pool: generally used for database backup and recovery, parallel query, and session memory in Shared Server mode. If no large pool is set up, the required content must be obtained from the shared pool or PGA. It is controlled by the initialization parameter large_pool_size. JAVA Pool: used to support running JAVA code in the database, generally controlled by java_pool_size. Stream Pool: Improves streaming support, which is generally controlled by stream_pool_size. Query the SGA zone: 1. show SGA 2. select * from v $ sga name value -------------------- ---------- Fixed Size 1249488 Variable Size 159387440 Database Buffers 281018368 Redo Buffers 7135232 the Fixed Size here indicates a Fixed area to store information about each SGA component. The size cannot be changed. Variable Size indicates a Variable region, such as a shared pool, a java pool, or a large pool. 'Database Buffers' indicates the high-speed buffer of the Database. Www.2cto.com Redo Buffers indicates the log buffer.