Oracle Memory Management

When an Oracle database instance is started, it needs to allocate shared memory to start background processes.

The memory used by the Oracle database mainly involves PGA and SGA.

1. PGA

Program global area, as the name suggests, is the global area of the program. It is a memory area used by the server process to contain data and control information. PGA is non-shared memory, it is allocated when the server process is started or created and exclusive access to the server process.

There are two ways to create a process: the dedicated server mode (dedicated server) and the Shared Server mode (shared
Server ). In dedicated server mode, Oracle starts an oracle process for each session. In Shared Server mode, a certain number of server processes are usually started on the server.
Then multiple clients request to share the same oracle service process. Generally, the server runs in dedicated server mode.

PGA creation process

Structure of Oracle

PGA creation process: when a client sends a connection request to the server, the server listens to the client's requirements. In dedicated server mode, a serlver is generated on the server.
Process is used to proxy user requests. The server process then initiates a connection to the instance and creates a session (create Session), while the PGA is a servler.
The process is allocated and used.

 

Generally, PGA includes private SQL zone and session information.

Session memory: used to store session login information and other related information. For Shared Server mode, this part is shared.

Private SQL area: contains information about Bound variables, execution status, and workspace. Each session that sends a query has a private SQL zone. For the dedicated server mode, this part is allocated in PGA, and for the Shared Server mode, this part is allocated in SGA.

The private SQL area consists of two parts: the permanent area (persistent
Area): contains information such as bound variables. The memory is released only when the cursor is closed.
Area). It stores the information required for running SQL statements. It is first created when a request is executed, including the query execution status information and SQL work areas.

 

Ii. UGA (User global
Area): Global Area of the user. It consists of user session data, cursor status, and index area. In Shared Server mode, a shared service process is shared by multiple user processes.
Pool or a portion of the large pool, while UGA is part of the PGA under a dedicated server.

In dedicatred mode, the relationship between PGA and UGA is like the relationship between process and session. PGA serves the memory structure of the process and contains process information. UGA serves sessions, contains session information.

Fixed UGA includes about 70 atomic variables, a small data structure, and pointers to the UGA heap.

Another memory area is CGA (call glabal area, which calls the global zone). It exists instantly and only in the call process, regardless of whether the UGA exists in PGA or SGA, CGA is the sub-heap of PGA. CGA is required for SQL parsing and SQL optimization. We will not discuss it here.

3. SGA (system global area): A memory area used to load data, objects, and save running status and Database Control information. It is allocated when the database instance is started, when the instance is released, each instance has its own SGA zone.

The above figure shows the architecture diagram of SGA.

Users connected to the Oracle database can share the data in SGA, which is usually set to higher performance, it can reduce physical I/O (increasing the data buffer in SGA can effectively reduce physical reads ).

Composition:

1. fixed area: fixed area is a fixed part of SGA. It contains thousands of variables and small data structures, such as latch and address pointers, this part of memory allocation is related to specific database versions and platforms and is not controlled by users. The fixed part only requires a small amount of memory.

2. Buffer
Cache (cache): used to store recently used data blocks. These data blocks may have been modified or not modified. In Oracle's data processing, the most expensive thing is physical
I/O (physical
I/O), the same data is much faster to get from the memory than to read from the disk, so saving as much data as possible in the memory can reduce disk I/O operations, this improves database performance.

Generally, data access and modification must be completed through buffer cache. When a process needs to access data, you must first determine whether the data exists in the memory.
If the data exists in the buffer, You need to determine whether the data can be accessed directly or whether consistent reading is required. If the data does not exist in the buffer, you need
Buffer cache to find enough space to load the required data, if the buffer
If sufficient memory space is not found in the cache, dbwr needs to be triggered to write dirty data and release the buffer space.

Buffer cache uses the hash bucket and cache buffer chain to store data.

The bucket stored in each buffer is composed of the data block address (data block) of the buffer.
Address. Inside the bucket, all the buffers are transferred through the cache buffer chain (two-way linked list) through the buffer
Header information. Buffer
The header stores the summary information of the corresponding data block, including the file number, block address, and status of the data block. Checks whether a data block exists in the buffer.
Header.

 

3. Shared Pool, including the shared memory structure, such as the SQL zone. The SQL area contains information such as the SQL parsing tree and execution plan. Through the sharing pool, the SQL statements executed repeatedly can be shared between different sessions.

Shared Pool is the most complex and important part of Oracle SGA settings. Shared Pool can achieve SQL sharing, reduce hard code parsing, and improve database performance.

Oracle introduces shared pool to help us share and reuse code.

Composition:

Library cache: stores SQL statements, SQL statement parsing trees, execution plans, PL/SQL blocks, and the code that can be executed by Oracle after conversion;

Data Dictionary cache: It stores data dictionary information, including the structure information of tables, views, and other objects, user and object permissions. This information is relatively stable.

Collaboration between shared pool and buffer cache

In addition to the preceding two parts, the shared pool draws out another memory for storing the SQL query result set, which is called result
Cache memory (result set cache ). Previous shared
The main function of the pool is to share SQL statements and reduce hard parsing to improve performance. However, after SQL is shared, executing queries also consumes a lot of time and resources. Now Oracle tries to close the query
If the set is cached, the cached query results can be returned to the user without the need to perform operations, which greatly improves the performance.

4. redo log buffer-log buffer stores redo log entries, logs record database changes, and is eventually written into the redo log file for recovery when the database crashes or fails.

5. Java pool: used for Java options such as JVM.

Recommended Oracle memory allocation: Oracle can use up to 80% of the physical memory, and the remaining 20% of the memory is used by the operating system.