Oracle memory management (Part 4)

Oracle memory management (Part 4)

[In-depth analysis-eygle] Study Notes

1.3 SGA Management

SGA refers to the System Global Area. It is 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.

In chapter I, I used? When the database is started to the nomount status, the SGA has been allocated and the background process is started. In SQL * Plus, run the show sga command to view the SGA allocation:

sys@felix SQL>show parameter sga NAME                                 TYPE                   VALUE---------------------------------------------------------- ------------------------------lock_sga                             boolean                FALSEpre_page_sga                         boolean                FALSEsga_max_size                         big integer            400Msga_target                           big integer            016:12:56 sys@felix SQL>

Users connected to the Oracle database can share data in SGA. for better performance, we always expect to set a higher SGA zone when the physical memory permits, to reduce physical I/O (the increase in the data buffer in SGA can effectively reduce physical read ).

1.4 SGA

Is the most common database instance architecture diagram, showing the structure of SGA:

 

1.4.1 Fixed Area-Fixed Area

Fixed Size is a Fixed part of SGA. It contains thousands of variables and small data structures, such as Latch or address pointers, this part of memory allocation is related to specific database versions and platforms and is not controlled by users. This information is very important for databases, but we usually do not need to care about it.

The fixed part only requires a small amount of memory. You can use an internal table X $ KSMFSV ([K] ernel [S] ervice Layer, [M] emoryManagement, addresses of [F] ixed [S] GA [V] ariables) query. In addition, the Oracle internal table X $ KSMMEM records the address ing relationship of the entire SGA. Through Association of X $ KSMFSV and X $ KSMMEM, you can find the setting of each variable in the Fixed Area.

On the 32-bit platform, each record in the X $ KSMMEM table represents 4 Bytes. On the 64-bit platform, each record represents 4 Bytes:

sys@felix SQL>select * from x$ksmmem whererownum <5; ADDR                   INDX    INST_ID KSMMMVAL---------------- ---------- --------------------------0000000060000000          0          1 000000000060000008          1          1 000000000060000010          2          1 000000000060000018          3          1 00 16:21:39 sys@felix SQL>

sys@felix SQL>select ksmfsnam,ksmfssiz from x$ksmfsv where ksmfsnam='kcsgscn_';KSMFSNAM           KSMFSSIZ-----------------  --------kcsgscn_                 4816:23:51 sys@felix SQL>

The ORADEBUG tool can be used to obtain the SCN value in the current memory:

16:23:51 sys@felix SQL>oradebug setmypidStatement processed.16:26:19 sys@felix SQL>oradebug DUMPvar SGA kcsgscn_kcslf kcsgscn_ [060019598, 0600195C8) = 00183004 00000000 00000000 00000000 000024AE 00000000 00000000 00000000 00000000 00000000 60019278 0000000016:26:22 sys@felix SQL> 

Obtain the SCN value as follows:

sys@felix SQL>selectto_number('183004','xxxxxxxxxx') from dual; TO_NUMBER('183004','XXXXXXXXXX')--------------------------------                         1585156 16:27:05 sys@felix SQL>

Fixed Area contains a lot of control information, but it should be noted that querying the X $ KSMFSV view may cause a process exception, so be cautious when using it.

1.4.1.1 Buffer Cache

Buffer Cache-the Buffer high-speed Cache is used to store recently used data blocks, which may have been modified or not modified. We know that the most expensive data processing process in Oracle is Physical I/O (Physical I/O) operations, the same data is much faster to read from the memory than from the disk. Therefore, saving as much data as possible in the memory can reduce disk I/O operations, this improves the database performance.

Starting from Oracle9i, Oracle introduced a new initialization parameter db_cache_size, which defines the Size of the Default buffer pool for the main Block Size (db_block_size ).

The Granule size used by each memory component can be queried through the dynamic performance View:

16:36:00 sys@felix SQL>select component,granule_size from v$sga_dynamic_components;COMPONENT                         GRANULE_SIZE--------------------------------- ------------shared pool                            4194304large pool                             4194304java pool                              4194304streams pool                           4194304DEFAULT buffer cache                   4194304KEEP buffer cache                      4194304RECYCLE buffer cache                   4194304DEFAULT 2K buffer cache                4194304DEFAULT 4K buffer cache                4194304DEFAULT 8K buffer cache                4194304DEFAULT 16K buffer cache               4194304DEFAULT 32K buffer cache               4194304Shared IO Pool                         4194304ASM Buffer Cache                       419430414 rows selected.17:33:14 sys@felix SQL>

The LRU algorithm is used to manage the Buffer Cache in Oracle, but this poses another problem. Many batch processing operations (such as full table scan? And so on) may cause refresh of the Buffer Cache, and "squeeze out" the frequently used data Buffer Cache. In different versions, Oracle constantly improves the LRU algorithm, to avoid the excessive impact of such operations.

However, Oracle provides BufferCache's multi-buffer pool technology to solve this problem from another aspect. The so-called multi-Buffer pool technology refers to dividing the Buffer Cache into three parts: Default, Keep, and Recycle pools based on different data access methods. For frequently used data, we can specify to store the data in the Keep pool when creating a table. For frequently used data read at one time, we can store the data in the Recycle pool; data in the Keep pool tends to be stored all the time, and the data in the Recycle pool tends to be aged instantly, while the Default pool stores data in the unspecified storage pool and is managed according to the LRU algorithm.

By DEFAULT, all tables use the DEFAULT pool, and the size of the Buffer Cache is determined by the initialization parameter db_cache_size (db_block_size * db_block_buffers in 8i.

17:33:14 sys@felix SQL>show parameter db_cache_sizeNAME                                 TYPE                   VALUE------------------------------------ ---------------------- ------------------------------db_cache_size                        big integer            017:37:24 sys@felix SQL>

If we specify the STORAGE (BUFFER_POOL KEEP) or STROAGE (BUFFER_POOL RECYCLE) statement when creating or modifying a data table, we set the table to use the KEEP or RECYCLE buffer.The buffer sizes are determined by the initialization parameters db_keep_cache_size and db_recycle_cache_size.

17:39:03 sys@felix SQL>show  parameter  db_keep_cache_sizeNAME                                 TYPE                   VALUE------------------------------------ ---------------------- ------------------------------db_keep_cache_size                   big integer            0 

Property	Description
Parameter type	Big integer
Syntax	DB_KEEP_CACHE_SIZE =Integer[K | M | G]
Default value	0 (DB_KEEP_CACHE_SIZE is not configured by default)
Modifiable	ALTER SYSTEM
Range of values	Minimum: 0 (values greater than zero are automatically modified to be either the granule size * number of processor groups, or 4 MB * number of CPUs, whichever is greater) Maximum: operating system-dependent
Basic	No

DB_KEEP_CACHE_SIZE specifiesthe size of the KEEP bufferpool. The size of the buffers in the KEEP buffer pool is the primary blocksize (the block size defined by the DB_BLOCK_SIZE initialization parameter ).

17:39:24 sys@felix SQL>show parameter  db_recycle_cache_sizeNAME                                 TYPE                   VALUE------------------------------------ ---------------------- ------------------------------db_recycle_cache_size                big integer            017:39:42 sys@felix SQL>

Property	Description
Parameter type	Big integer
Syntax	DB_RECYCLE_CACHE_SIZE =Integer[K | M | G]
Default value	0 (DB_RECYCLE_CACHE_SIZE is not configured by default)
Modifiable	ALTER SYSTEM
Range of values	Minimum: 0 (values greater than zero are automatically modified to be either the granule size * number of processor groups, or 4 MB * number of CPUs, whichever is greater) Maximum: operating system-dependent
Basic	No

DB_RECYCLE_CACHE_SIZE specifiesthe size of the RECYCLE bufferpool. The size of the buffers in the RECYCLE pool is the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter ).

17:39:42 sys@felix SQL>show parameter cache_size NAME                                 TYPE                   VALUE---------------------------------------------------------- ------------------------------client_result_cache_size             big integer            0db_16k_cache_size                    big integer            0db_2k_cache_size                     big integer            0db_32k_cache_size                    big integer            0db_4k_cache_size                     big integer            0db_8k_cache_size                     big integer            0db_cache_size                        big integer            0db_flash_cache_size                  big integer            0db_keep_cache_size                   big integer            0db_recycle_cache_size                big integer            0

We can also see that there are a series of db_nk_cache_size parameters after Oracle9i, which are supported by multiple blocks introduced in Oracle9i. After Oracle9i, multiple types of Block_size tablespaces are allowed in the same database, including 2 k, 4 k, 8 k, 16 k, and 32 k Block_size,The block size defined by db_block_size is called Block_size.. If you create tablespaces of different block_size in the database, you must set the db_nk_cache_size parameters respectively.

You can query the settings of each buffer pool.V $ buffer_poolGet:

 

17:43:03 sys@felix SQL>select id,name,block_size,current_size,target_size from v$buffer_pool;        ID NAME                                     BLOCK_SIZE CURRENT_SIZE TARGET_SIZE---------- ---------------------------------------- ---------- ------------ -----------         3 DEFAULT                                        8192           72          7217:46:03 sys@felix SQL>

1.4.1.2 Shared Pool

A Shared Pool is usually called a Shared Pool and contains a Shared memory structure, such as an SQL zone. SQL zone contains SQL Solutions

Information such as tree analysis and execution plan can be shared between different sessions through the sharing pool.

1.4.1.3 Redo Log Buffer

Redo Log Buffer-redo Log entries (Redo entries) stored in the Log Buffer, which records database changes and is eventually written into the redo Log file for recovery when the database crashes or fails; if the data warehouse is running in the archive mode, the final log file will be written into the archive log, which can be used for data recovery during media recovery.

The size of the log buffer is determined by the initialization parameter log_buffer. This is a static parameter and cannot be dynamically adjusted.