About Oracle's memory issues

The memory structure in Oracle consists of two main categories: SGA and PGA, in which the SGA represents the system global area and the PGA represents the global area of the program.

Oracle memory is primarily stored with executable code (PL/SQL,JAVA), information about connected sessions, including all currently active and inactive sessions, information necessary to run the program, such as query plans, information that is shared between Oracle processes, and information that communicates with each other, such as locks , data needed during program operation and buffering information stored on external storage.

Memory is a very important part of database architecture and one of the main factors that affect database performance. When the database is running, memory is used primarily to store a variety of information. Depending on how the system uses memory, the memory of the Oracle database can be divided into the following five structures, namely the system Global Area (SGA), the program Global (PGA), the Sort area candidates (LARGE Pool), and the Java pool.

1 system Global Area (SGA)

The system global Area (SGA) has a set of memory structures that are a chunk of memory shared by all user processes. It can contain data and control information for a DB instance. In a DB instance, there can be multiple user processes that can share data in the system global zone, so the system global Zone is also known as the shared global zone.

The total memory size of the system global Region (SGA) is determined by the parameter sga_max_size, which can be viewed by using the show parameter statement, as follows:

sql> SHOW PARAMETER sga_max_size;

NAME TYPE VALUE

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

Sga_max_size Big Integer 2432M

The system global Area (SGA) can be divided into: data buffers, log buffers, and shared pools, depending on the role.

(1) Data buffer

The data buffer primarily holds the most recently accessed block information, and when the user requests data from the database, if the required data is already in the data buffer, Oracle will fetch the data directly into the data buffer and return it to the user without having to read the data from the data file. The size of the data buffer is determined by the parameter db_cache_size, which can be viewed through the show parameter statement, as follows:

sql> SHOW PARAMETER db_cache_size;

NAME TYPE VALUE

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

Db_cache_size Big Integer 0

(2) Log buffer

(3) The log buffer is used to store data modification operation information. Log buffers

(4) When the log data reaches a certain limit, the log writing process LGWR writes the log to the disk's log file. The size of the log buffer is determined by the parameter log_buffer, and the information for the parameter is viewed through the show parameter statement, as follows:

sql> SHOW PARAMETER Log_buffer;

NAME TYPE VALUE

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

Log_buffer Big Integer 12896K

(3) Shared pool

A shared pool is an area of memory that parses, compiles, and executes SQL statements and the PL. SQL program, which holds the data dictionary information for the most recently executed statement, the PL/SQL program.

A shared pool consists of three caches: the library cache, the data dictionary cache, and the user global zone. Where the library buffer White Village SQL statement Analysis code and execution plan; The data dictionary buffer holds the tables, column definitions, and permissions that are obtained in the data dictionary, and the user's global zone holds the user's session information.

The size of the shared pool is determined by the parameter shared_pool_size, which can be queried by the show parameter statement, as follows:

sql> SHOW PARAMETER shared_pool_size;

NAME TYPE VALUE

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

Shared_pool_size Big Integer 0

2. Program Global Zone (PGA)

The program global zone contains individual user or server data and control information, which is the private memory area assigned to a process by the Oracle system. It is automatically assigned by Oracle when a user process connects to the Oracle database and creates a session. The size of the program global area is determined by the parameter pga_aggregate_target, which can be queried by the show parameter statement, as follows:

Sql> SHOW Parameterpga_aggregate_target;

NAME TYPE VALUE

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

Pga_aggregate_target Big Integer 807M

3. Sorting area

The sort area is the memory space provided by the Oracle system for temporary data generated by the sort operation. In fact, in Oracle, two of the areas that hold the temporary data generated by the user sort operation are the memory sort area and the disk staging segment.

When a sort operation is performed, the Oracle system prioritizes using the memory sort area, and if there is not enough memory space, the disk staging segment is used for sorting.

The size of the sorting area is determined by the parameter sort_area_size, and the information for the parameter is queried by the show parameter statement, as follows:

Sql> Showparameter sort_area_size;

NAME TYPE VALUE

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

Sort_area_size integer 65536

4. Large Pool

A large pool is an optional memory structure in the system global zone that provides a large buffer for use by backup and recovery operations of the database. The size of the large pool is determined by the parameter large_pool_size, and the information for the parameter is queried by the show parameter statement, as follows:

Sql> Showparameter large_pool_size;

NAME TYPE VALUE

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

Large_pool_size Big Integer 0

5. Java Pool

Java pool, which is used to support Java operations in the database. For example, using Java to write a stored procedure, Oracle's virtual machine through Java uses Java pools to process Java stored procedures in user sessions. The size is determined by the parameter java_pool_size, and the information for the parameter is queried by the show parameter statement, as follows:

Sql> Showparameter java_pool_size;

NAME TYPE VALUE

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

Java_pool_size Big Integer 0

About Oracle's memory issues