what is oracle sga

sga refers to the "system global area", which is a set of shared buffer storage areas allocated by ORACLE to the instance. It is used to store database data and control information to achieve the management and operation of database data. SGA is mainly composed of shared pool, data buffer, and redo log buffer; the data buffer is used to store the data recently read and written from the data file.

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

The memory structure is one of the most important components of the Oracle database. Operations in the database more or less rely on memory, which is an important factor affecting database performance. Oracle database includes 3 basic memory structures. :

Let me introduce you to some sga in the memory structure.

Introduction to oracle sga

The full name of sga is "System Global Area", which means "system global area" in Chinese and is allocated by ORACLE to the instance. A set of shared buffer storage areas, mainly used to store database data and control information. This information is shared by the database process (PGA cannot be shared) to realize the management and operation of database data.

It contains the data and control information of the Oracle server. It is allocated in the actual memory of the computer where the Oracle server resides. If the actual memory is not enough, it is written to the virtual memory.

SGA is a set of shared memory structures shared by all services and background processes. When a database instance starts, system global area memory is automatically allocated. When the database instance is shut down, SGA memory is reclaimed. SGA is the area that occupies the largest amount of memory and is also an important factor affecting database performance.

Query the SGA area:

SQL> show SGA

Total System Global Area 3290345472 bytes
Fixed Size            2217832 bytes
Variable Size         1795164312 bytes
Database Buffers     1476395008 bytes
Redo Buffers           16568320 bytes
SQL> select * from v$sga;

NAME              VALUE
-------------------- ----------
Fixed Size        2217832
Variable Size         1795164312
Database Buffers     1476395008
Redo Buffers           16568320

• Fixed Size represents a fixed area that stores information about each component of SGA. The size cannot be modified.

• Variable Size represents variable areas, such as shared pools, java pools, large pools, etc.

• Database Buffers represents the database high-speed buffer.

• Redo Buffers represents the log buffer.

SGA mainly includes:

The system global domain SGA is mainly composed of three parts: shared pool and data buffer , log buffer.

1. Shared pool (shared pool) : Used to store recently executed SQL statements and recently used data dictionary data.

It is mainly composed of two memory structures: Library cache and Data dictionary cache

Modify the size of the shared pool: ALTER SYSTEM SET SHARED_POOL_SIZE = 64M;

Library Cache cache is Information related to the executed SQL and PL/SQL, that is, the text of the SQL statement, the analyzed code and the execution plan. Realize the sharing of commonly used statements and use the LRU algorithm for management. It consists of the following two structures: Shared SQL area and Shared PL/SQL area;

Data Dictionary Cache caches the used database definition, which stores related tables, Column and other object definitions and permissions. It includes information about database files, tables, indexes, columns, users, permissions, and other database objects. During the syntax analysis phase, Server Process accesses information in the data dictionary to parse object names and verify access operations. Data dictionary information is cached in memory to help improve response times.

2. Data buffer (database buffer cache): Used to store data recently read and written from data files.

The data blocks used by the Oracle system (that is, the user's high-speed buffer) are stored in the data high-speed buffer. When data is written to the database, it is read and written in units of data blocks. When the data When the high-speed buffer is full, the system automatically removes some data that is not frequently accessed by users. If the data the user wants to check is not in the data cache, Oracle automatically reads it from the disk. The data high-speed buffer includes three types of areas:

1) Dirty data area (Dirty Buffers): Contains data blocks that have been changed and need to be written back to the data file.

2) Free Buffers: An area that does not contain any data and can be rewritten. Oracle can read data blocks from the data file in this area.

3) Reserved area (Pinned Buffers): This area contains areas that are being processed or are explicitly reserved for future use.

3. Redo log buffer (redo log buffer): Used to record the operations of the service or background process on the database.

Mainly used to restore each modification record is called a redo entry. Modifications can be redone using the information in the Redo entry.

In addition, there are two optional memory structures in SGA:

4. Java Pool: Used to store Java code.

5. Large Pool: Used to store large memory structures that are not directly related to SQL. Backup and restore use.

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of what is oracle sga. For more information, please follow other related articles on the PHP Chinese website!