Lean Oracle Memory Architecture in detail

Memory structure

In modern computers, the CPU accesses memory more than a few times faster than the disk, so Oracle accesses the data as much as possible in memory, rather than directly modifying the data on the hard disk. Memory content is synced to disk at the right time. Oracle uses memory to overcome the constraints of disk IO, the more activity in memory, the better the system performance, conversely, the more operations on disk, the system performance is worse. (Original address: http://blog.sunansheng.com/2016/04/29/oracle-memory/)

Oracle is a large memory consumer, and it consumes memory that can be divided into two parts-a process-sharing part and a process-specific part . The process here refers specifically to the Oracle process (Server process and background process), which is called the SGA (shared Global area), and the process-specific part is called the PGA (Program Global area).

For different types of applications, these two parts have different meanings. For OLTP systems, the SGA is more important than the PGA, which features a large number of small transactions. For OLAP systems, it is characterized by the association of large data volumes, so the PGA has a greater impact on the system than the SGA. Because the PGA directly determines the efficiency of large sorting and hashing operations.

1. Sga

The SGA is a general term for a large chunk of memory, which is divided into several components and is a typical SGA, each with its own unique role.

2. Data Buffer Cache

The data that is read from the disk by the server Process is placed in this memory. When modifying the data, the data is also modified in this region. The data Buffer Cache retains copies of the database before it is modified (before image, pre-image) and modified (after image, post-image).

The memory in the Data Buffer Cache can be divided into 3 categories:

• Free buffers: Refers to memory that does not contain any data that can be used to load data that is read later.
• Dirty Buffer: This section contains data blocks that have been modified but have not yet been written to disk.
• Pinned Buffer: The memory that is being used by the user.

When the user process requests data, Oracle first checks to see if the data is already in the buffer cache, and if the data is already in the cache, the Server process does not have to access the disk and reads the data directly from the SGA. If the data has not yet been loaded into the SGA, server process must read the data from disk, but before it is read in, the server process must ensure that there is enough free buffer in the data buffer Cache to accommodate this, if the server P Rocess after scanning a certain number of buffer has not found the free buffer, it will notify the DBWN process to write dirty buffer to disk, once the completion, the original dirty buffer becomes free buffer, it can be reused.

What kind of dirty Buffer is written to disk? This will use the LRU (Least recently used) algorithm, which ensures that the less recently used data blocks are written to disk, the more space is released first. The algorithm is based on the assumption that "the closer the data is used, the more likely it is to be used again". In the Data Buffer Cache, not only an LRU list exists, but multiple LRU lists are used at the same time, such as free buffer, Dirty buffer, and Pinned buffer, all using the LRU algorithm.

(1) Multiple Buffer caches with different uses.

In most cases, it is sufficient for the database to have a default buffer cache to place all the data, but Oracle allows for fine-tuning through multiple buffer caches.

Different data in the database access frequency is not the same, such as user attributes, commodity list of the word typical data may need to be used frequently, so this kind of data should be placed in the Buffer cache as long as possible, and some log data will only be used occasionally, such data should be as soon as possible from the buffer Cache in order to free up space. For applications that meet this feature, Oracle offers the other two buffer cache--keep buffer pool and recycle buffer pool, plus the default buffer Cache of 3 buffer pool. The 3 differences are in the retention strategy of the data, keep is kept as long as possible, and recycle is released as soon as possible, as shown in the table.

buffer Pool name	initialization parameter	description
keep Buffer Pool	db_keep_cache_size
recycle Buffer Pool	db_recycle_cache_size
default Buffer Pool	db_cache_size	The default cache

(2) Multiple Buffer Pool According to the size of the data block.

In addition to dividing buffer cache,oracle by retention time, you can also allocate multiple buffer caches according to the size of the data block. A table space in Oracle 10g that allows the creation of different block sizes, which the DBA needs to use when creating a database, which determines the size of the database standard data block, and the db_block_size of the table space created later inherits the block size. But DBAs can also create table spaces with other chunk sizes, with up to 5 different block sizes (2KB, 4KB, 8KB, 16KB, 32KB), which are called table spaces of nonstandard block sizes. One important condition for using multiple data block sizes within a database is the need to configure the corresponding nonstandard buffer Cache. The corresponding parameter is db_nk_cache_size.

The previously mentioned keep buffer pool, Recycle buffer pool are used only for standard data blocks. The default buffer pool is made up of all standard and non-standard buffer pool groups. The so-called standard, non-standard is based on the creation of the database specified by the db_block_size, if the value is 2KB, then the other 4KB, 8KB, 16KB, 32KB is called non-standard data blocks. If defined as 32KB, then 2KB, 4KB, 8KB, 16KB are called non-standard data blocks.

3. Shared Pool

The shared pool is another very important area in the SGA, especially for OLTP systems where the shared pool configuration is important for system performance. Unlike the data Buffer Cache, which is stored in a different user profile, the Shared Pool contains code, including PL/SQL code, statements, and data dictionary information. The Shared pool can also be divided into the Library cache and Dictionary cache two zones.

If the user has experience in other languages, be sure to know the compilation process, that is, to convert the user code into machine language, such as the Java language to compile the *.java file into a. class file. The same is true of the database, whether the user submits a stored procedure written in PL/SQL or a pure code, which is written in human language, and if the computer is able to execute the code, the first thing to do is compile it into a machine code that can be recognized by the CPU. The machine code generated by the compilation is stored in the library Cache, and the shared Pool is an area that can be shared by all users, so there are two benefits: first, if the user executes the same piece of code again, it can skip the parsing process and reuse the machine code generated previously; If other users execute the same code, they can reuse and reduce parsing.

The complete execution of an SQL statement can be divided into 4 stages:

(1) analysis.
At this stage, semantics, parsing, and permission checking are performed, including whether the statement is spelled correctly, whether there is a syntax error, whether the database has a object, whether the user has access to a, and so on.

After these checks are passed, check to see if the statement has been parsed before. Oracle calculates a hash value for each statement, using this value to see if there is a corresponding execution plan in the shared Pool. If there is, it means that the statement has been executed, the existing execution plan can be used, and no longer need to perform the following (2), (3) step (this is the only soft parse Soft parse), if not, it must (1), (2), (3 The execution plan (so-called hard Parse) will not be generated until the step is completed.

(2) Optimization.

This phase determines the optimal access path based on the statistics of the object and the currently selected Optimizer mode (Cbo/rbo). The statistics of the so-called objects, such as how many records are in a table, the average length of each record, how many blocks of data are occupied, and the height of the index. This information is recorded in a system dictionary, such as User_tables.num_rows.

Another factor that affects optimization is the "optimizer mode" (Optimizer modes). This configuration tells Oracle how you want to return records, such as First_rows_n to return the first N records as soon as possible, and all_rows to return the entire recordset as soon as possible, without worrying about the return speed of the first few. Different patterns will result in different execution plans.

Based on these two types of information, Oracle optimizes the engine to produce multiple access paths, such as full table scan, index Scan, Nest Loop join, or hash join, and Oracle calculates the cost of each access path, taking the least expensive access path as the best path.

(3) Generate execution plan.

This phase generates the final execution plan based on the least cost access path generated in the previous phase.

(4) Returns the result of the query.

Based on the execution plan of the previous step, access to the data to obtain the result set, returned to the user. If it is a DML statement, the data is modified and the result is returned.
The first 3 phases are resource-intensive operations, including CPU, Latch, lock and various resources, which have a significant impact on system performance. The shared Pool is designed to reduce the activities of the first 3 phases.

The part of the code stored in the Library cache, Dictionary the data dictionary information stored in the cache. In the parsing phase, the parser will determine the name of the section table, whether the column name is correct, whether the user has permission to perform the requested operation, which is the data dictionary, including in the next optimization stage, the CBO generates execution plan of the object statistics used in the data dictionary. If this data is not in memory, Oracle needs to read the data into the Dictionary Cache from the data dictionary. The more data dictionaries are cached in the Dictionary cache, the longer the retention time is, and the shorter the parsing time, the better the system performance.

4. Redo Log Buffer

Redo log buffer is used to save the Redo record, which is very small, usually only a few megabytes, compared to Buffer Cahce, Shared Pool. When a server Process changes data in the Cache (DML operation), a Redo record is generated that is stored in the Redo log buffer. The LGWR process is responsible for writing the contents of the Redo log Buffer to the redo log File on disk.

Redo Log buffer works in a circular manner, and once the LGWR process writes the log contents to disk, LGWR can overwrite the content. LGWR is usually the busiest process in the system, and Oracle provides a nologging technology that can skip Redo log generation to reduce the load on LGWR, although this technique is useful in a limited number of situations, such as direct data loading.

5. Large pool, Java pool, Stream pool

These memory components are relatively unimportant, and the main purpose of the Large Pool is for RMAN to back up and share the connection mode. Java pool is used to support a Java Virtual machine (JVM) and stream Pool is used to support streams.

6. SGA Automatic Management

In previous versions of Oracle 10g, the adjustment of each component of the SGA was one of the daily tasks of the DBA. Starting with Oracle 10g, this area is automatically adjusted, and this feature is part of the Oracle Auto-management feature that allows users to automatically adjust the SGA by setting a new parameter sga_target. The SGA auto-adjustment is shown in the later chapters.

Pga

For each service process, background process, Oracle assigns it a PGA, which is used to hold each user's private data. Unlike the SGA share, the PGA is used exclusively. The PGA can be divided into two parts.

• Private SQL Area : This region is used to hold SQL binding variable information, although the execution plan of the SQL statement can be shared, but each user's binding variable information is stored in the PGA; Open_crusro defines the Cursor that the user retains in this area. Number.

• Runtime Area : Each time the Session executes a statement allocated space, this space can be used for data sorting, Hash, connection, etc., when the statement is completed, the region is released by Oracle.

Oracle 9i began with the introduction of the PGA Automatic management, by setting the Workarea_size_policy to auto to enable PGA Auto-management, if this parameter is set to MANUAL, it is returned to manual management. Auto-management just need to define a parameter pga_aggregate_traget just fine. PGA Automatic Management is more efficient for memory use, such as if manual management is used, then the PGA assigned to each session is not automatically returned to the system, regardless of whether the session is performing an operation or idle state, it must be returned to the system after the user exits (Log Off) of the PGA space. This can cause a lot of memory to be idle, and the session that really needs to work has not been able to get enough PGA, and in automatic management, once the operation of the session is finished, the Runtime-area will be automatically returned to the operating system.

Lean Oracle Memory Architecture in detail