Oracle memory problems

Oracle memory problems

In Oracle, the memory structure mainly includes two categories: SGA and PGA. SGA represents the system global zone, and PGA represents the program global zone.

Oracle memory mainly stores the execution program code (PL/SQL, JAVA); Information about connected sessions, including all currently active and inactive sessions; required information when running the program; for example, query plans, information shared and exchanged between Oracle processes, such as locks, data required during program running, and buffer information stored on external storage.

Memory is a very important part of the database architecture and one of the main factors affecting the database performance. When the database is running, the memory is mainly used to store various information. According to the system's memory usage, the memory of the Oracle database can be divided into the following five structures: system global zone (SGA), program global zone (PGA ), the sorting AREA (sort area) large pool and java POOL ).

1. system global zone (SGA)

The system global zone (SGA) is composed of a group of memory structures, which is a memory area shared by all user processes. It can contain data and control information of a database instance. In a database instance, there can be multiple user processes that can share data in the global zone of the system. Therefore, the global zone of the system is also called the shared global zone.

The total memory size of the system global zone (SGA) is determined by the PARAMETER sga_max_size. You can view the PARAMETER information by using the show parameter statement, as shown below:

SQL> SHOW PARAMETER SGA_MAX_SIZE;

NAME TYPE VALUE

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

Sga_max_size big integer 2432 M

The system global zone (SGA) can be divided into data buffer, log buffer, and sharing pool according to different functions.

(1) Data Buffer Zone

The data buffer zone stores information about recently accessed data blocks. When a user requests data from a database, if the required data is already in the data buffer zone, oracle extracts data from the data buffer and returns it to the user without reading data from the data file. The data buffer size is determined by the db_cache_size PARAMETER. You can use the show parameter statement to view the PARAMETER information, as shown below:

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 the modification operation information of data. When the log Buffer

(4) When the log data reaches a certain limit, the log writing process LGWR writes the log to the log file on the disk. The log buffer size is determined by the log_buffer PARAMETER. You can use the show parameter statement to view the PARAMETER information, as shown below:

SQL> SHOW PARAMETER LOG_BUFFER;

NAME TYPE VALUE

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

Log_buffer big integer 12896 K

(3) Sharing pool

A shared pool is a memory area for parsing, compiling, and executing SQL statements and PL/SQL programs. It is used to save the latest SQL statements and data dictionary information of PL/SQL programs.

The Shared Pool mainly includes three types of caches: database cache, data dictionary cache, and user global zone. The database buffer analyzes the code and execution plan of the SQL statements in the white village. The data dictionary buffer stores the tables, column definitions, and permissions obtained from the data dictionary. The user global area stores the user's session information.

The size of the Shared Pool is determined by the shared_pool_size parameter. You can use the show parameter statement to query the parameter information, as shown below:

SQL> SHOW PARAMETER SHARED_POOL_SIZE;

NAME TYPE VALUE

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

Shared_pool_size big integer 0

2. Program global zone (PGA)

The global area of the program contains data and control information of a single user or server. It is the private memory area allocated to a process by the Oracle system. When a user process connects to the Oracle database and creates a session, it is automatically allocated by Oracle. The size of the global area of the program is determined by the pga_aggregate_target parameter. You can use the show parameter statement to query the information of this parameter, as shown below:

SQL> SHOW PARAMETERPGA_AGGREGATE_TARGET;

NAME TYPE VALUE

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

Pga_aggregate_target big integer 807 M

3. sorting area

The sorting area is the memory space provided by the Oracle System for temporary data generated by sorting operations. In fact, in Oracle, the temporary data generated by user sorting operations is stored in two areas: the memory sorting area and the disk temporary segment.

When sorting is performed, the Oracle system uses the memory sorting area for sorting first. If the memory space is insufficient, the temporary disk segments are used for sorting.

The size of the sorting area is determined by sort_area_size. You can use the show parameter statement to query the information of this PARAMETER, as shown below:

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 global area of the system. It is used to provide a large buffer for database backup and recovery operations. The size of a large pool is determined by the large_pool_size PARAMETER. You can use the show parameter statement to query the PARAMETER information, as shown below:

SQL> SHOWPARAMETER LARGE_POOL_SIZE;

 

NAME TYPE VALUE

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

Large_pool_size big integer 0

5. java pool

Java pool, used to support java running in the database. For example, if you use java to write a stored procedure, Oracle uses the java pool to process the java stored procedure in the user session through the java Virtual Machine. The size is determined by the java_pool_size PARAMETER. You can use the show parameter statement to query the PARAMETER information, as shown below:

SQL> SHOWPARAMETER JAVA_POOL_SIZE;

NAME TYPE VALUE

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

Java_pool_size big integer 0