In-depth analysis of LRU for physical reading (least recently used), lru resolution

In-depth analysis of LRU for physical reading (least recently used), lru resolution

Reprinted please indicate the source:

Http://blog.csdn.net/guoyjoe/article/details/38264883

A set of LRU linked lists include the LRU main chain, LRU auxiliary chain, LRUW main chain, and LRUW auxiliary chain, which are called a WorkSet (Working Group) such:

sys@ZMDB> select CNUM_SET,CNUM_REPL,ANUM_REPL,CNUM_WRITE,ANUM_WRITE from x$kcbwds where CNUM_SET>0;  CNUM_SET  CNUM_REPL  ANUM_REPL CNUM_WRITE ANUM_WRITE---------- ---------- ---------- ---------- ----------     15221      15221       3796          0          0     15221      15221       3783          0          0

CNUM_SET: Total number of buffers in the Working Group

CNUM_REPL: Total number of LRU buffers in the Working Group (primary LRU + secondary LRU)

ANUM_REPL: Total number of backend LRU buffers in the Working Group

The implicit parameter shows that the total number of buffers is 30442, which is exactly the same as the above CNUM_SET = 15221 + 15221.

sys@ZMDB> @?/rdbms/admin/show_para Enter value for p: _db_block_buffersold  12:     AND upper(i.ksppinm) LIKE upper('%&p%')new  12:     AND upper(i.ksppinm) LIKE upper('%_db_block_buffers%')P_NAME                                   P_DESCRIPTION                                      P_VALUE                        ISDEFAULT ISMODIFIED ISADJ---------------------------------------- -------------------------------------------------- ------------------------------ --------- ---------- -----_db_block_buffers                        Number of database blocks cached in memory: hidden 30442                          TRUE      FALSE        FALSE                                          Parameter

Run the following statement to check the LRU status of the buffer in the database:

sys@ZMDB> select lru_flag,count(*) from x$bh group by lru_flag;  LRU_FLAG   COUNT(*)---------- ----------         6        208         2         10         4       7122         8      1519907646

We will explain LRU_FLAG = 6, 2, 0, etc. For example, what does it mean for 6?

First, find any BUFFER of lru_flag = 6 In x $ bh.

sys@ZMDB> select LRU_FLAG,LOWER(BA)from x$bh where lru_flag=6 and rownum=1;  LRU_FLAG LOWER(BA)---------- ----------------         6 0000000081dae000

In DUMP buffer_cache, run the following command:

sys@ZMDB> alter session set events'immediate trace name buffers level 1';Session altered.ys@ZMDB> col value for a85sys@ZMDB> select * from v$diag_info where name='Default Trace File';   INST_ID NAME                                               VALUE---------- -------------------------------------------------- -------------------------------------------------------------------------------------1Default Trace File                                 /u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13235.trc

Use BA = 81dae000 to search for trace files,

/U01/app/oracle/diag/rdbms/zmdb/trace/zmdb_ora_13235.trc

Get the following content:

BH (0x81fe7e38) file#: 1 rdba: 0x0040ace1 (1/44257) class: 1 ba: 0x81dae000  set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25  dbwrid: 0 obj: 421 objn: 423 tsn: 0 afn: 1 hint: f  hash: [0x9ef9d710,0x853f8da8] lru: [0x81fe7df0,0x81fe8050]  lru-flags: moved_to_tail on_auxiliary_list  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]  st: CR md: NULL fpin: 'kdswh06: kdscgr' tch: 1  cr: [scn: 0x0.80350f4d],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.80350f4d],[sfl: 0x0],[lc: 0x0.8034c532]  flags: block_written_once redo_since_read

LRU_FLAG = 6 indicates lru-flags: moved_to_tail on_auxiliary_list, which is to move to the tail of the auxiliary linked list of LRU, this may be because SMON from the LRU main chain table of non-dirty blocks, TCH <= 1 and the status of non-pin buffer is mounted to the end of the LRU auxiliary linked list.

Based on the above method, we can explain the meaning of LRU_FLAG:

LRU_FLAG

0 ==> LRU-the cold end of the main chain. This is special. It does not show LRU_FLAG in DUMP.

2 ==> LRU-tail of the cold end of the main chain, lru-flags: moved_to_tail

 

4 ==> LRU-secondary chain, lru-flags: on_auxiliary_list

 

6 ==> LRU-tail of the secondary chain, lru-flags: moved_to_tail on_auxiliary_list

 

8 ==> LUR-Main Chain hot end, lru-flags: hot_buffer

 

When a physical read occurs, Oracle finds an idle BUFFER from the LRU auxiliary linked list, and then mounts the BUFFER on the LRU auxiliary chain to the cold end of the LRU main chain. The experiment is as follows:

First, ensure that there is a BUFFER on the LRU secondary link, that is, LRU_FLAG = 6 or LRU_FLAG = 4. If the database is just started, there may be no LRU_FLAG = 6 or LRU_FLAG = 4, that requires a lot of physical read operations before LRU_FLAG = 6 or LRU_FLAG = 4

sys@ZMDB> alter system flush buffer_cache;System altered.sys@ZMDB> select lru_flag,count(*) from x$bh group by lru_flag;  LRU_FLAG   COUNT(*)---------- ----------         6        208         4      30009         0          2

The first DUMP of the entire buffer cache:

sys@ZMDB> alter session set events'immediate trace name buffers level 1';/u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13480.trc

Physical read

gyj@ZMDB> conn gyj/gyjConnected.gyj@ZMDB> set autot on;gyj@ZMDB> select id,name, dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from gyj_t1 where id=1;        ID NAME                                FILE#     BLOCK#---------- ------------------------------ ---------- ----------         1 gyj1                                    7        139Execution Plan----------------------------------------------------------Plan hash value: 59758809----------------------------------------------------------------------------| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------|   0 | SELECT STATEMENT  |        |     1 |    14 |    68   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| GYJ_T1 |     1 |    14 |    68   (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("ID"=1)Statistics----------------------------------------------------------          1  recursive calls          1  db block gets        254  consistent gets        248  physical reads          0  redo size        733  bytes sent via SQL*Net to client        523  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processedsys@ZMDB> select LRU_FLAG,lower(BA),TCH from x$bh where file#=7 and dbablk=139;  LRU_FLAG LOWER(BA)               TCH---------- ---------------- ----------         0 000000007d1b2000          1         4 0000000078558000          0         4 0000000085f68000          0

After the physical read is completed, dump the entire buffer cache again,

sys@ZMDB> alter session set events'immediate trace name buffers level 1';/u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13511.trc

Search for the trace file of the first DUMP with BA = 7d1b2000

/u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13480.trcBH (0x7d3e8098) file#: 3 rdba: 0x00c0586b (3/22635) class: 34 ba: 0x7d1b2000  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25  dbwrid: 0 obj: -1 objn: 0 tsn: 2 afn: 3 hint: f  hash: [0x9efa7570,0x9efa7570] lru: [0x7f7f5d30,0x7d3e8050]  lru-flags: on_auxiliary_list  ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]  st: FREE md: NULL fpin: 'ktuwh03: ktugnb' tch: 0 lfb: 33  flags:

Search for the trace file of the second DUMP with BA = 7d1b2000

/u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13511.trcBH (0x7d3e8098) file#: 7 rdba: 0x01c0008b (7/139) class: 1 ba: 0x7d1b2000  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25  dbwrid: 0 obj: 22919 objn: 19567 tsn: 7 afn: 7 hint: f  hash: [0x787e4bd8,0x9e4cda50] lru: [0x7f7f5d30,0x7d3e8050]  ckptq: [NULL] fileq: [NULL] objq: [0x9a88e518,0x7d3e8078] objaq: [0x9a88e508,0x7d3e8088]  st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1  flags: only_sequential_access  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]

From the two traces above, we can conclude that ba: 0x7d1b2000

From lru-flags: on_auxiliary_list (LRU_FLAG = 4) to the LRU-main chain cold end header, this is special when the DUMP does not show LRU_FLAG (LRU_FLAG = 0)

 

Observe that when lru tch> = 2, the cold end is moved to the hot end.

 

1. manually set the BUFFER to 100 MB.

ALTER SYSTEM SET memory_max_target=0 scope=spfile;  ALTER SYSTEM SET memory_target=0; alter system set sga_target=0; create table gyj1_t80 (id int,name char(2000));create table gyj2_t80 (id int,name char(2000));begin  for i in 1 .. 30000  loop    insert into gyj1_t80 values(i,'gyj'||i); commit; end loop;end;/SQL> SQL> select bytes/1024/1024||'M' from dba_segments where segment_name='GYJ1_T80' and owner='GYJ';BYTES/1024/1024||'M'-----------------------------------------80Mbegin  for i in 1 .. 30000  loop    insert into gyj2_t80 values(i,'gyj'||i); commit; end loop;end;/create index idx_gyj1_t80m on gyj1_t80(id);create index idx_gyj2_t80m on gyj2_t80(id);SQL> show user;USER is "GYJ"SQL> conn / as sysdbaConnected.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.

First dump

SQL> alter session set events'immediate trace name buffers level 1';Session altered.SQL> select * from v$diag_info where name='Default Trace File';   INST_ID NAME---------- --------------------VALUE--------------------------------------------------------------------------------         1 Default Trace File/u01/app/oracle/diag/rdbms/jfdb/jfdb/trace/jfdb_ora_7210.trc

A physical read takes an index.

set autot onselect id,name,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from gyj1_t80 where id=1;SQL> select id,name,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from gyj1_t80 where id=1;        ID NAME                      FILE#     BLOCK#---------- -------------------- ---------- ----------         1 gyj1                          5        581select LRU_FLAG,lower(BA),TCH from x$bh where file#=5 and dbablk=581;SQL> select LRU_FLAG,lower(BA),TCH, decode(state,0,'free',1,'xcur',2,'scur'  2   ,3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,  3  'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt', 16, 'flashf  4  ree',  17, 'flashcur', 18, 'flashna')  from x$bh where file#=5 and dbablk=581;  LRU_FLAG LOWER(BA)               TCH DECODE(STA---------- ---------------- ---------- ----------         0 000000009fca8000          1 xcurSQL> select LRU_FLAG,lower(BA),TCH from x$bh where file#=5 and dbablk=581;  LRU_FLAG LOWER(BA)               TCH---------- ---------------- ----------         0 000000009fca8000          5SQL> set autot traceonly;SQL>  select /*+ index(G) */ count(name) from gyj1_t80 G where id<=8000;SQL> select LRU_FLAG,lower(BA),TCH from x$bh where file#=5 and dbablk=581;  LRU_FLAG LOWER(BA)               TCH---------- ---------------- ----------         0 000000009fca8000          6

When physical read occurs again, LRU_FLAG = 0 is changed to 8, and TCH = 8 is reset to 0.

SQL> select LRU_FLAG,lower(BA),TCH from x$bh where file#=5 and dbablk=581;  LRU_FLAG LOWER(BA)               TCH---------- ---------------- ----------         0 000000009fca8000          8SQL> select LRU_FLAG,lower(BA),TCH from x$bh where file#=5 and dbablk=581;  LRU_FLAG LOWER(BA)               TCH---------- ---------------- ----------         8 000000009fca8000          0BH (0x9ffe02a8) file#: 5 rdba: 0x01400245 (5/581) class: 1 ba: 0x9fca8000  set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 15,19  dbwrid: 0 obj: 13537 objn: 13537 tsn: 5 afn: 5 hint: f  hash: [0xb6a86de0,0xb6a86de0] lru: [0x9ffe0260,0x9ffe9a60]  lru-flags: hot_buffer  ckptq: [NULL] fileq: [NULL] objq: [0x9ffe0618,0x9ffe0028] objaq: [0x9ffe0628,0x9ffe0038]  st: XCURRENT md: NULL fpin: 'kdswh05: kdsgrp' tch: 0  flags:  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]

When TCH = 0, a large number of physical reads occur. The BUFFER with the address 9fca8000 is reused and completely disappears from the BUFFER.

SQL>  select LRU_FLAG,lower(BA),TCH from x$bh where file#=5 and dbablk=581;  LRU_FLAG LOWER(BA)               TCH---------- ---------------- ----------         8 000000009fca8000          0SQL>  select LRU_FLAG,lower(BA),TCH from x$bh where file#=5 and dbablk=581;no rows selected 

Through experiments, we have a better understanding of the basic process of reading LRU, and can further understand the LRU Algorithm in physical reading.

Least recently used (LRU) page elimination algorithm

100
Each page can contain 150 variables, while two-dimensional arrays are stored by row. Therefore, three rows of arrays can be saved to two pages, and 150 rows can be saved to 100 pages. A loop is also a row-based operation, that is, the variables on each page are processed in sequence, and all pages are transferred to the next page after full processing (page missing interruption). Only one page missing interruption occurs on each page, therefore, the number of page missing interruptions is 100.
The page where the program segment is located is always used, so it will not be eliminated in the LRU algorithm. The other two pages only use one page within a period of time until all the variables in the page are processed completely. Therefore, the elimination algorithm will remove the other page for loading new pages, therefore, no additional page missing occurs.

 
LRU Algorithm

An algorithm used by the Oracle system. For data blocks (memory blocks) that are in the memory but are not used recently, they are called LRU, oracle will remove the data from the memory based on the LRU and free up space to load other data.
What is the LRU algorithm? LRU is the abbreviation of Least Recently Used, that is, the Least Recently Used page replacement algorithm, is a virtual page storage management service.
As for the memory management of the operating system, it has been an important research direction to save resources for the most processes by taking advantage of memory with a small capacity. Memory virtual storage management is now the most common and most successful method-when the memory is limited, some external memory is extended as the virtual memory, the real memory only stores the information used during the current operation. This undoubtedly greatly expands the memory function and greatly improves the computer concurrency. Virtual page storage management: divides the space required by the process into multiple pages, stores only the currently required pages in the memory, and stores other pages in the external storage management mode.
However, there are advantages and disadvantages. The virtual page-based storage management reduces the memory space required by the process, but it also brings the disadvantage of longer running time: In the process of running, inevitably, some information stored in the external storage should be exchanged with the existing information in the memory. Due to the low speed of the external storage, the time spent in this step cannot be ignored. Therefore, it is quite meaningful to adopt algorithms to reduce the number of times of reading external memory.
For virtual page-based storage, internal and external storage information is replaced by pages. When an external storage page is needed, it is transferred to the memory, and in order to keep the size of the original space, we also need to call out a page in memory to external memory. Naturally, the fewer such transfers, the higher the process execution efficiency. So, which page can be called out to achieve the goal of transferring as little as possible? We need an algorithm.
Naturally, the algorithm to achieve this kind of situation is the most ideal-each time the page is swapped out, all the memory pages will be used at the latest-this can minimize the Page Swap, this algorithm is called the ideal page replacement algorithm. Unfortunately, this algorithm cannot be implemented.
In order to minimize the gap with the ideal algorithm, a variety of exquisite algorithms have been generated. Recently, the least page replacement algorithm has been used. The LRU algorithm is based on the fact that pages that are frequently used in the preceding commands are likely to be frequently used in subsequent commands. On the other hand, pages that have not been used for a long time may not be used for a long time in the future. This is the well-known local principle-the cache is faster than the memory speed, and runs based on the same principle. Therefore, we only need to find the least recently used page to call up the memory each time we change. This is all the content of the LRU algorithm.