Buffer pool and shared pool (1), buffershared

Buffer pool and shared pool (1), buffershared

[In-depth analysis-eygle] Study Notes

1.1 buffer pool Principle

Buffer Cache is an important part of Oracle SGA. Generally, data access and modification must be completed through BufferCache.When a process needs to access data, 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 needs to be constructed based on the data status;If the data does not exist in the Buffer, you need to find enough space in the Buffer Cache to load the required data. If the Buffer Cache cannot find enough memory space, DBWR needs to be triggered to write dirty data and release the Buffer space.

1.1.1 LRU and LRUW List

In Buffer Cache, Oracle manages memory through several linked lists. The most familiar ones are LRU List and LRUW List (also known as Write/Dirty List ), various lists store information such as pointers to specific Buffer.

Starting from Oracle8, Oracle also introduced Checkpoint Queue-Checkpoint Queue and File Queue-File Queue to implement incremental checkpoints. Starting from Oracle8i, due to the introduction of asynchronous DBWn, the more precise concept of various lists and Queue is the Working Sets (WS-Working Sets). Each WS contains several lists with different functions,Each List is protected by Cache Buffers lru chain Latch. When multiple DBWR processes are used (the DB_WRITER_PROCESSES parameter can be used to set the database to use multiple DBWR processes ), multiple WS exist in the Database. When the multi-Buffer pool technology of Buffer Cache is used, each independent Buffer pool also has its own WS.

LRU List is used to maintain the Buffer in the memory. It is managed according to the LRU algorithm (in different versions, the management method is different). During database initialization, all the Buffer is hashed to LRUList for management. To read data from a data file, you must first find the Free Buffer on the LRU List, and then read the data to the Buffer Cache. After the data is modified, when the status changes to Dirty, it can be moved to the LRUW List. The LRUW List is a candidate Buffer that can be written to the data file by DBWR. A Buffer is either on the LRU List, it either exists on the LRUW List and cannot exist on both lists at the same time.

Is a brief description of LRU and LRUW List in Buffer Cache:

 

 

Checkpoint QueueRecord Data blocks in the order they are modifiedAt the same time, the RBA and the data block are associated. In this way, when performing incremental check, the database can write the data block in the order of modification, so that during recovery, you can start quick recovery based on the final written data blocks and their related RBA. When a checkpoint is triggered, DBWR writes data according to the checkpoint queue. When other conditions are triggered, DBWR is written by the Dirty List. The checkpoint queue is allocated in the Shared Pool memory:

 

Also related Latch protection:

15:48:38 sys @ felix SQL> select name, gets, missesfrom v $ latch where name like '% checkpoint queue % ';

 

NAME GETS MISSES

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

Active checkpoint queue latch 10247 0

Checkpoint queue latch 145659 1

 

15:48:39 sys @ felix SQL>

 

 

The following describes the principles and usage of BufferCache:

 

1. When a Server process needs to read data to the Buffer Cache, it must first determine that the data in the Buffer

(The process shown in Figure ①). If the data is stored and available, the data is obtained.

The algorithm increases the access count. If this data does not exist in the Buffer, it needs to be read from the data file.

2. before reading data, the Server process needs to scan the LRU List to find the Free Buffer, during the scan, the Server process registers all the modified buffers to the LRUW List (as shown in Figure ②). These Dirty buffers can then be written to the data file.

3. If the LRUW Queue exceeds the threshold, the Server process will notify DBWn to write dirty data (the process shown in ③ );

 

This is also a condition for triggering DBWn write. This threshold was once mentioned to be 25%, that is, when the Dirty Queue exceeds 25%, the DBWn write operation will be triggered:

 

16:13:58 sys @ felix SQL> select kvittag, kvitval, kvitdsc fromX $ kvitWhere kvittag = 'kcbldq ';

 

KVITTAG KVITVAL KVITDSC

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

Kcbldq 25 large dirty queue ifkcbclw reaches this

 

16:14:04 sys @ felix SQL>

 

If the Server process still cannot find enough Free Buffer after scanning for LRU exceeding a threshold value, it will stop searching and notify DBWn to write dirty data and release the memory space.

 

Similarly, this threshold value can be obtained from the dictionary table above. The value is 40%. That is to say, when the Server process fails to find enough Free Buffer to scan LRU more than 40%, the search will stop, notify DBWn to write data. This process will be in the free buffer wait state.

 

 

16:18:03 sys @ felix SQL> col KVITDSC for a60

16:18:20 sys @ felix SQL> select kvittag, kvitval, kvitdsc from x $ kvit where kvittag = 'kcbfsp ';

 

KVITTAG KVITVAL KVITDSC

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

Kcbfsp 40 Max percentage of LRUlist foreground can scan for free

 

16:18:24 sys @ felix SQL>

 

 

At the same time, we know that due to the introduction of incremental checkpoints, DBWn will also actively scan the LRU List and register the discovered Dirty Buffer to the Dirty List and Checkpoint Queue. This scan is also subject to an internal constraint, in Oracle9iR2, the ratio is 25%:

4. After a sufficient Buffer is found, the Server process can read the Buffer from the data file into the Buffer Cache (as shown in Figure 4)

5. If the read Block does not meet the read consistency requirements, the Server process needs to construct a pre-image and return it to the user through the current Block version and rollback segment.

Starting from Oracle 8i, LRU List and LRUW List have added AUXILIARY List (aupoliciary List) to improve management efficiency.After a secondary List is introduced, when the database is initialized, the Buffer is first stored on the secondary List of LRU (AUXILIARY RPL_LST). When used, it is moved to the primary List of LRU (MAIN RPL_LST ), in this way, when the user process searches for FreeBuffer, it can start from the LRU-AUX List, while DBWR searches for Dirty Buffer, it can start from LRU-Main List, thus improving the search efficiency and database performance.

You can run the following command to dump the Buffer Cache content to clearly view the data structure described above:

 

 

Alter session set events 'immediate trace name buffers level4 ';

 

16:33:14 sys @ felix SQL> select value from v $ diag_info;

 

VALUE

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

/U01/app/oracle/diag/rdbms/felix/trace/felix_ora_7187.trc

 

 

 

Different levels have different levels of dump details. The available levels of this command are mainly 1 ~ Level 10. The meanings of different levels are as follows.

(1) Level 1: contains only BufferHeaders information.

(2) Level 2: Contains BufferHeaders and Buffer summary information dump.

(3) Level 3: Contains BufferHeaders and complete Buffer content dump.

(4) Level 4: Level 1 + Latch dump + LRU queue.

(5) Level 5: Level 4 + Buffer summary information dump.

(6) Level 6 and Level 7: Level 4 + complete Buffer content dump.

(7) Level 8: Level 4 + displays users/waiters information.

(8) Level 9: Level 5 + displays users/waiters information.

(9) Level 10: Level 6 + displays users/waiters Information

 

Dump is only used in the test environment. The dump trace file may be huge. To obtain the complete trace file, we recommend that you set the initialization parameter max_dump_file_size to UNLIMITED.

 

16:33:27 sys @ felix SQL> show parameter max_dump

 

NAME TYPE VALUE

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

Max_dump_file_size string unlimited

16:47:37 sys @ felix SQL> show parametermemory_target

 

NAME TYPE VALUE

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

Memory_target big integer 400 M

16:48:46 sys @ felix SQL>

 

 

 

View the file/u01/app/oracle/diag/rdbms/felix/trace/felix_ora_7187.trc:

The following information can be obtained from the beginning of Level 4 trace file, which records the Prev and Next positioning information of different lists. WS indicates WorkingSets. Note that WSID indicates the number of different WS:

 

* ** 16:33:13. 185

* ** Session id: (41.237) 16:33:13. 185

* ** Client id: () 16:33:13. 185

* ** Service name :( SYS $ USERS) 2014-07-2216:33:13. 185

* ** Module name :( sqlplus @ felix (TNS V1-V3) 16:33:13. 185

* ** Action name :() 16:33:13. 185

 

Dump of buffer cache at level 4 for tsn = 2147483647 rdba = 0

(WS) size: 0 (0) wsid: 1 state: 0 pool: 1

(WS_REPL_LIST) main_prev: 0x774b2f18 main_next: 0x774b2f18 aux_prev: 0x774b2f28 aux_next: 0x774b2f28

Curnum: 0 auxnum: 0

Cold: 774b2f18 hbmax: 0 hbufs: 0

(WS_WRITE_LIST) main_prev: 0x774b2f48 main_next: 0x774b2f48 aux_prev: 0x774b2f58 aux_next: 0x774b2f58

Curnum: 0 auxnum: 0

(WS_XOBJ_LIST) main_prev: 0x774b2f78 main_next: 0x774b2f78 aux_prev: 0x774b2f88 aux_next: 0x774b2f88

Curnum: 0 auxnum: 0

(WS_XRNG_LIST) main_prev: 0x774b2fa8 main_next: 0x774b2fa8 aux_prev: 0x774b2fb8 aux_next: 0x774b2fb8

Curnum: 0 auxnum: 0

(WS_REQ_LIST) main_prev: 0x774b2fd8 main_next: 0x774b2fd8 aux_prev: 0x774b2fe8 aux_next: 0x774b2fe8

Curnum: 0 auxnum: 0

(WS_L2WRT_LIST) main_prev: 0x774b3008 main_next: 0x774b3008 aux_prev: 0x774b3018 aux_next: 0x774b3018

Curnum: 0 auxnum: 0

(WS_L2REPL_LIST) main_prev: 0x774b3038 main_next: 0x774b3038 aux_prev: 0x774b3048 aux_next: 0x774b3048

Curnum: 0 auxnum: 0

(WS_L2KEEP_LIST) main_prev: 0x774b3068 main_next: 0x774b3068 aux_prev: 0x774b3078 aux_next: 0x774b3078

Curnum: 0 auxnum: 0

(WS) fbwanted: 0

(WS) bgotten: 0 sumwrt: 0

(WS) pwbcnt: 0, last: 0

MAIN RPL_LST Queue header (NEXT_DIRECTION) [NULL]

 

Next is the specific List information. Note that there are multiple NULL lists, which are pre-allocated lists for different parts of the Buffer Cache (using the Keep pool, Recycle pool, and memory of different block_size:

 

MAIN RPL_LSTQueue header (NEXT_DIRECTION) [NULL]

MAIN RPL_LSTQueue header (PREV_DIRECTION) [NULL]

AUXILIARY RPL_ LST Queue header (NEXT_DIRECTION) [NULL]

AUXILIARY RPL_LSTQueue header (PREV_DIRECTION) [NULL]

MAIN WRT_LST Queue header (NEXT_DIRECTION) [NULL]

MAIN WRT_LST Queue header (PREV_DIRECTION) [NULL]

AUXILIARY WRT_LST Queue header (NEXT_DIRECTION) [NULL]

AUXILIARY WRT_LST Queue header (PREV_DIRECTION) [NULL]

MAIN XOBJ_LST Queue header (NEXT_DIRECTION) [NULL]

MAIN XOBJ_LSTQueue header (PREV_DIRECTION) [NULL]

AUXILIARY XOBJ_LST Queue header (NEXT_DIRECTION) [NULL]

AUXILIARY XOBJ_LST Queue header (PREV_DIRECTION) [NULL]

MAIN XRNG_LST Queue header (NEXT_DIRECTION) [NULL]

MAIN XRNG_LST Queue header (PREV_DIRECTION) [NULL]

AUXILIARY XRNG_LST Queue header (NEXT_DIRECTION) [NULL]

AUXILIARY XRNG_LST Queue header (PREV_DIRECTION) [NULL]

MAIN REQ_LST Queue header (NEXT_DIRECTION) [NULL]

MAIN REQ_LST Queue header (PREV_DIRECTION) [NULL]

AUXILIARY REQ_LST Queue header (NEXT_DIRECTION) [NULL]

AUXILIARY REQ_LST Queue header (PREV_DIRECTION) [NULL]

MAIN L2W_LST Queue header (NEXT_DIRECTION) [NULL]

MAIN L2W_LSTQueue header (PREV_DIRECTION) [NULL]

AUXILIARY L2W_LST Queue header (NEXT_DIRECTION) [NULL]

AUXILIARY L2W_LST Queue header (PREV_DIRECTION) [NULL]

MAIN L2R_LST Queue header (NEXT_DIRECTION) [NULL]

MAIN L2K_LST Queue header (NEXT_DIRECTION) [NULL]

MAIN L2R_LST Queue header (PREV_DIRECTION) [NULL]

AUXILIARY L2R_LST Queue header (NEXT_DIRECTION) [NULL]

AUXILIARY L2K_LST Queue header (NEXT_DIRECTION) [NULL]

MAIN L2K_LST Queue header (PREV_DIRECTION) [NULL]

AUXILIARY L2K_LST Queue header (NEXT_DIRECTION) [NULL]

AUXILIARY L2K_LST Queue header (PREV_DIRECTION) [NULL]

(WS) size: 0 (0) wsid: 2 state: 0 pool: 2

(WS_REPL_LIST) main_prev: 0x774ce4b0 main_next: 0x774ce4b0 aux_prev: 0x774ce4c0 aux_next: 0x774ce4c0

Curnum: 0 auxnum: 0

Cold: 774ce4b0 hbmax: 0 hbufs: 0

(WS_WRITE_LIST) main_prev: 0x774ce4e0 main_next: 0x774ce4e0 aux_prev: 0x774ce4f0 aux_next: 0x774ce4f0

Curnum: 0 auxnum: 0

(WS_XOBJ_LIST) main_prev: 0x774ce510 main_next: 0x774ce510 aux_prev: 0x774ce520 aux_next: 0x774ce520

Curnum: 0 auxnum: 0

(WS_XRNG_LIST) main_prev: 0x774ce540 main_next: 0x774ce540 aux_prev: 0x774ce550 aux_next: 0x774ce550

Curnum: 0 auxnum: 0

(WS_REQ_LIST) main_prev: 0x774ce570 main_next: 0x774ce570 aux_prev: 0x774ce580 aux_next: 0x774ce580

Curnum: 0 auxnum: 0

(WS_L2WRT_LIST) main_prev: 0x774ce5a0 main_next: 0x774ce5a0 aux_prev: 0x774ce5b0 aux_next: 0x774ce5b0

Curnum: 0 auxnum: 0

(WS_L2REPL_LIST) main_prev: 0x774ce5d0 main_next: 0x774ce5d0 aux_prev: 0x774ce5e0 aux_next: 0x774ce5e0

Curnum: 0 auxnum: 0

(WS_L2KEEP_LIST) main_prev: 0x774ce600 main_next: 0x774ce600 aux_prev: 0x774ce610 aux_next: 0x774ce610

Curnum: 0 auxnum: 0

(WS) fbwanted: 0

(WS) bgotten: 0 sumwrt: 0

(WS) pwbcnt: 0, last: 0

MAIN RPL_LST Queue header (NEXT_DIRECTION) [NULL]

MAIN RPL_LST Queue header (PREV_DIRECTION) [NULL]

AUXILIARY RPL_LST Queue header (NEXT_DIRECTION) [NULL]

 

From the above output, we can also see that in the Buffer Cache, in addition to RPL_LST and WRT_LST, there are other categories of lists, with different functions. The multi-Buffer pool and multi-WS structure of the Buffer Cache are as follows:

 

 

At the same time, in the Level 4 Dump, we can see the queue information of the main RPL_LST, which is also the most intuitive manifestation of the linked list:

 

(WS) bgotten: 17019 sumwrt: 6954

(WS) pwbcnt: 0, last: 25

MAIN RPL_LST Queue header (NEXT_DIRECTION) [0x6a7e6170, 0x6bbe17d0]

0x6a7e6088 => 0x6a7e61b8 => 0x6a7e62e8 => 0x6a7e6418 => 0x6a7e6548 => 0x6a7e6678 => 0x6a7e67a8 => 0x6a7e68d8

0x6a7e6a08 => 0x6b3e8c78 => 0x6bfd8608 => 0x6cbf9c68 => 0x6bbf4358 => 0x69bdf5a8 => 0x6cbd8278 => 0x6abfc268

0x6abfc398 => 0x6abfc4c8 => 0x6abfc5f8 => 0x6afd8018 => 0x6afd8148 => 0x6afd8278 => 0x6afd83a8 => 0x6afd84d8

0x6afd8738 => 0x6afd8868 => 0x6afd8f88 => 0x6afd8e58 => 0x6afd8998 => 0x6b7f38a8 => 0x6afdc038 => 0x6afdc168

0x6afdc4f8 => 0x6afdc628 => 0x6afdc758 => 0x6afdc888 => 0x6afdd208 => 0x6afdd338 => 0x6afdd468 => 0x6afdd6c8

0x6afdd7f8 => 0x6afdd928 => 0x6afdda58 => 0x6afddb88 => 0x6afddcb8 => 0x6afddde8 => 0x6afddf18 => 0x6afde048

0x6afde178 => 0x6afde2a8 => 0x6afde3d8 => 0x6afde508 => 0x6afde898 => 0x6afde9c8 => 0x6afdeaf8 => 0x6afdec28

0x6afded58 => 0x6afdf0e8 => 0x6afdf218 => 0x6afdf478 => 0x6afdf5a8 => 0x6afdf6d8 => 0x6afdfb98 => 0x6afe0e98

0x6afe36f8 => 0x6afe54a8 => 0x6afe55d8 => 0x6afe5708 => 0x6afe5968 => 0x6afe5bc8 => 0x6afe5cf8 => 0x6afe5e28

0x6afe62e8 => 0x6afe6418 => 0x6b7f2478 => 0x6afe6a08 => 0x6afe6b38 => 0x6afe6c68 => 0x6afe6d98 => 0x6afe6ec8

0x6afe6ff8 => 0x6afe7128 => 0x6afe7258 => 0x6afe7f68 => 0x6afe8b48 => 0x6afe8c78 => 0x6afe9988 => 0x6afe9ab8

0x6afe9be8 => 0x6afe9f78 => 0x6afea0a8 => 0x6afea1d8 => 0x6afea8f8 => 0x6afeb3a8 => 0x6afeb998 => 0x6afebac8

0x6afebbf8 => 0x6afebf88 => 0x6afec0b8 => 0x6afec1e8 => 0x6afec318 => 0x6afec578 => 0x6afec6a8 => 0x6afec7d8

0x6afec908 => 0x6afeca38 => 0x6afecdc8 => 0x6afecef8 => 0x6afed028 => 0x6afed158 => 0x6afed288 => 0x6afed3b8

 

Who occupies my Buffer Pool? (SQL server Cache

For example, can you know which database, which table, and which index occupies the buffer Pool ?" At that time, I did not find the answer to this question, but I kept remembering this question. This problem was solved until SQL server 2005. The answer is to use the dynamic view (DMV) sys. dm_ OS _buffer_descriptors. This DMV is very powerful. According to SQL Server books online, this view is used to return information about all data pages in the SQL Server Buffer Pool. You can use the output of this view to determine the distribution of database pages in the buffer pool based on the database, object, or type ". Specifically, this view can return the following attributes of an 8 K data page in the buffer pool: (1) which database the page belongs to (2) which database the page belongs to (3) page_ID (4) of the page. Based on this, you can determine the index page or the data page (5) the number of rows of data on the page (6) the size of the available space on the page. (7) Whether the page has been modified since the disk was read. With the above information, we can easily collect several useful data, as shown below. 1. What database accounts for the Buffer Pool memory? SELECTcount (*) * 8 as cached_pages_kb, CASE database_id WHEN 32767 THEN 'resourcedb' ELSEdb_name (database_id) ENDAS region (database_id), database_idORDERBY includesc; the result is as follows: from the above results, we can see that the database AdventureWorks occupies about 30 mb of buffer pool space. Note that this DMV does not return information about non-data pages (such as execution plan cache) in the Buffer Pool. That is to say, this DMV does not return information of all pages in the Buffer Pool. 2. Specifically, which table or index of the current database occupies the largest Pool buffer space? SELECTcount (*) * 8 AS cached_pages_kb, obj. name, obj. index_id, B. type_desc, B. nameFROMsys. dm_ OS _buffer_descriptorsAS bd INNERJOIN (SELECTobject_name (object_id) AS name, index_id, allocation_unit_id, object_id FROMsys. allocation_unitsAS au INNERJOINsys. partitionsAS p ON au. container_id = p. hobt_id AND (au. type = 1 OR au. type = 3) UNIONALL SELECTobject_name (object_id) AS name, index_id, allocation_unit_id, object_id FROMsys. allocation_unitsAS au INNERJOINsys. partitionsAS p ON au. container_id = p. partition_id AND au. type = 2) AS obj ON bd. allocation_unit_id = obj. allocation_unit_id LEFTJOINsys. indexes B on B. object_id = obj. object_idAND ...... remaining full text>

How does the shared_pool_size value of Oracle Change the size? I want it to have a value which is always 0.

Amount