Oracle memory structure SGA

The following articles mainly introduce the Oracle memory structure research-SGA. I saw the introduction of PGA in Oracle memory structure research-PGA two days ago. PGA is a dedicated private memory zone for a server process, while SGA is a shared memory zone.

As mentioned in the Oracle Memory Structure Study-PGA, PGA is a dedicated private memory zone for server processes, while SGA is a shared memory zone.

SGA consists of multiple parts:

1, fixed SGAFixed SGA)

2, block buffer Db cache)

3. Redo log buffer)

4. Java pool)

5. Large pool)

6. Shared pool)

7. Stream pool)

The following parameters are used to control the size of Shared Pool components:

1. JAVA_POOL_SIZE: controls the Java pool size.

2. SHARED_POOL_SIZE: 9i controls the largest portion of the shared pool and the size of the shared pool over 10 Gb.

3. LARGE_POOL_SIZE: controls the size of a large pool.

4. DB _ * K_CACHE_SIZE: controls the buffer size of different blocks.

5. LOG_BUFFER: controls the size of the redo log buffer.

6. SGA_TARGET: 10 Gb or above to control the total memory size for automatic SGA memory management.

7. SGA_MAX_SIZE: controls the maximum size that SGA can achieve. To change the size, restart the database.

The following describes in detail the role and Recommended settings of each part in the memory structure of Oracle-SGA.

Ii. Functions of SGA Components

1, fixed SGA:

As the name suggests, it is a constant memory zone, pointing to other parts of SGA. Oracle uses it to find other areas in SGA, which can be simply understood as a memory zone for management.

2. block buffer zone:

During query, Oracle first puts the data read from the disk into the memory, and then queries the relevant data without reading the disk again. When data is inserted and updated, Oracle caches data in the zone and then writes the data to the hard disk in batches. Through the block buffer, Oracle can improve the disk I/O performance through the memory cache.

The block buffer has three areas:

Default pool): all data is cached here by Default.

Keep pool): Used to cache data that needs to be reused multiple times.

Recycle pool): Used to cache data that is rarely reused.

It turns out that there is only one default pool, and all data is cached here. This will cause a problem: a large amount of data that is rarely reused will "squeeze out" the data to be reused in the buffer, resulting in increased disk I/O and reduced operation speed. Later, we separated the retention pool and the recycle pool to cache data based on whether data is frequently reused.

The size of these three memory areas needs to be determined manually, and there is no sharing between them. For example, if the pool is full and there is a large amount of idle memory in the recycle pool, the memory in the recycle pool is not allocated to the keep pool.

You can also set db_nk_cache when 9i starts. Before 9i, the database can only use the same block size. 9i multiple block sizes of 2 kb, 4 kb, 8 KB, 16 KB, and 32 KB can be used in the same database. These blocks must be cached in their respective db_nk_cache. If you specify different block sizes for different tablespaces, you need to set their own buffer.

3. Redo log buffer ):

Data is cached here before being written to the redo log file, which is triggered in the following cases:

Every 3 seconds

When the cache reaches 1 MB or 1/3 full

When users submit

Before data in the buffer is written to the disk

The above content is the description of the SGA article on Oracle memory structure, hoping to help you in this aspect.