Oracle Memory Architecture Overview (i) Introduction to the Oracle SGA

Oracle's memory configuration is closely related to Oracle performance. Configuration of memory is the configuration that most affects Oracle performance. Memory also directly affects the consumption of two other important resources: CPU and IO.

First look at what the main content of Oracle memory storage is:

Program code (PLSQL, Java);

Information about the session that is already connected, including all active and inactive sessions currently in action;

The relevant information that the program must run, such as the query plan;

Information shared between Oracle processes and information exchanged, such as locks;

Data (such as redo log entries, blocks) that are permanently stored on the peripheral storage media and are cache in memory.

Each Oracle database is made up of Oracle Instance (instance) and database (data files, control files, redo log files), where the so-called instance is the media that the user interacts with the database, and the user operates the database by connecting to an instance. The instance is composed of a unified memory structure (SGA,PGA,UGA) and a batch of memory-resident processes. Instances are identified by ORACLE_SID in the operating system and are identified in Oracle with parameter instance_name, and their two values are the same. When the database is started, the system first allocates the system global Area (SGA) in server memory, forms the memory structure of Oracle, and then initiates the operating system processes of several resident memory, that is, the process structure that forms the Oracle, the memory area and the background process are called an Oracle instance.

1, the concept of SGA

The SGA is a set of shared memory structures allocated to the system that can contain data or control information for a single database instance.   If multiple users are connected to the same database instance, in the SGA of the instance, the data can be shared by multiple users.  When the database instance is started, the memory of the SGA is allocated automatically, and the SGA memory is reclaimed when the database instance is closed. The SGA is one of the most memory-intensive areas and also an important factor affecting database performance.

The SGA area is readable and writable. All users who log on to the instance can read the information in the SGA, and the service process writes the modified information to the SGA area when Oracle performs the operation.

The SGA mainly includes the following data structures:

Data buffering (buffer cache)

Redo log buffering (Redo log buffer)

Sharing pools (shared pool)

Java pools (Java pool)

Dachi (Large Pool)

Stream pools (Streams pool---10g only)

Data dictionary caching (Dictionary cache)

Additional information, such as state information for databases and instances

Sql> Show SGA

Total System Global area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 192940932 bytes

Database buffers 411041792 bytes

Redo buffers 7135232 bytes

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Note:

(1) The data dictionary cache and other information in the SGA are accessed by the background process of the instance, which is fixed in the SGA after the instance is started and does not change, so this part is also known as the fixed SGA. This part of the area is generally less than 100K in size.

(2) The size of the memory areas of Shared Pool, Java pool, Large pool, and streams pool is changed by the corresponding system parameter settings, so there is a pass called the variable SGA (Variable SGA).

2, SGA parameter description

Query using the following statement

Sql> Show Parameter SGA

NAME TYPE VALUE

------------------------------------ ----------- -------

LOCK_SGA Boolean FALSE

PRE_PAGE_SGA Boolean FALSE

Sga_max_size Big Integer 584M

Sga_target Big Integer 584M

Let's take a look at some of these parameters:

Sql> Select Name,value, issys_modifiable from V$parameter where name like ' sga% ';

NAME VALUE Issys_mod

--------------- --------------- ---------

Sga_max_size 612368384 FALSE

Sga_target 612368384 IMMEDIATE

If Issys_modifiable returns FALSE, the parameter cannot be dynamically modified with the ALTER SYSTEM statement, and the database needs to be restarted.

Therefore, sga_max_size can not be dynamically adjusted. But we can make a dynamic adjustment to the Sga_target.

(1) Sga_max_size:

The SGA area includes a variety of buffers and memory pools, and most can specify their size by specific parameters. However, as an expensive resource, the physical memory size of a system is limited. Although it is not necessary to relate the actual physical memory size to the CPU's memory addressing, excessive use of virtual memory causes page in/out, which can significantly affect system performance and may even lead to system crash. So a parameter is needed to control the maximum size of the SGA using virtual memory, which is sga_max_size.

When the instance is started, each memory area allocates only the minimum size required for the instance, and in subsequent runs, expands their size as needed, and their total size is sga_max_size.

When an attempt is made to increase the size of one memory, and if this value causes all memory area sizes to be greater than sga_max_size, Oracle prompts for an error and does not allow modification.

Of course, if the specified range is spfile when the parameter is set (including modifying the sga_max_size itself), it is not subject to this restriction. This can happen, in SPFile, the total size of the SGA in each memory area is larger than the sga_max_size. Oracle will do the following: When the instance is started again, if the total memory sum of the SGA is found to be greater than sga_max_size, it will modify the Sga_max_size value to the value of the total for each memory area of the SGA.