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

A set of LRU linked lists include the LRU main chain, the LRU auxiliary chain, the LRUW main chain, and the LRUW auxiliary chain, which are called a WorkSet (Working Group) such as 650)this.srcwww.68idc.cnhelpuploadsallimg15121410060LM3-0.jpgtitle111.png altwKioL1PXLA_RrQwAAAE_O1bng.

A set of LRU linked lists include the LRU main chain, LRU auxiliary chain, LRUW main chain, and LRUW auxiliary chain, which is called a WorkSet (Working Group) such as: 650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151214/10060LM3-0.jpg "title =" 111.png" alt = "wKioL1PXLA_RrQwAAAE_O1bng

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:

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151214/10060LM3-0.jpg "title =" 111.png" alt = "wKioL1PXLA_RrQwAAAE_O1bngXE780.jpg"/>

Sys @ ZMDB> selectCNUM_SET, CNUM_REPL, ANUM_REPL, CNUM_WRITE, ANUM_WRITE from x $ kcbwds whereCNUM_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_buffers

Old 12: AND upper (I. ksppinm) LIKEupper ('% & p % ')

New 12: AND upper (I. ksppinm) LIKEupper ('% _ db_block_buffers % ')

P_NAME P_DESCRIPTION P_VALUE ISDEFAULT ISMODIFIEDISADJ

Certificate ------------------------------------------------------------------------------------------------------------------------------------------------

_ Db_block_buffers Number of database blocks cached inmemory: HID 30442 TRUE 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 15199

0 7646

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 andrownum = 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 a85

Sys @ ZMDB> select * from v $ diag_info where name = 'default tracefile ';

INST_ID NAME VALUE

---------- Certificate -------------------------------------------------------------------------------------------------------------------------------------------------

1 Default Trace File/u01/app/oracle/diag/rdbms/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> selectlru_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 bufferslevel 1 ';

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

Physical read

Gyj @ ZMDB> conn gyj/gyj

Connected.

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 139

Execution 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 cballs

1 db block gets

254 consistent gets

248 physical reads

0 redo size

733 bytes sent via SQL * Net to client

523 bytes encoded ed via SQL * Net from client

2 SQL * Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

Sys @ ZMDB> selectLRU_FLAG, lower (BA), TCH from x $ bh where file # = 7 and dbablk = 139;

LRU_FLAG LOWER (BA) TCH

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

0 1000000007d1b2000 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/trace/zmdb_ora_13511.trc

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

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

BH (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/trace/zmdb_ora_13511.trc

BH (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 LRUTCH> = 2, the cold end is moved to the hot end.

1. manually set the BUFFER to 100 MB.

Alter system SETmemory_max_target = 0 scope = spfile;

Alter system set memory_target = 0;

Alter system set sga_target = 0;

Create table gyj1_t80 (idint, name char (2000 ));

Create table gyj2_t80 (idint, name char (2000 ));

Begin

For I in 30000

Loop

Insert into gyj1_t80 values (I, 'gyj' | I );

Commit;

End loop;

End;

/

SQL> selectbytes/1024/1024 | 'M' from dba_segments where segment_name = 'gyj1 _ t80' andowner = 'gyj ';

BYTES/1024/1024 | 'M'

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

80 M

Begin

For I in 30000

Loop

Insert into gyj2_t80 values (I, 'gyj' | I );

Commit;

End loop;

End;

/

Create index idx_gyjpolict80m ongyjpolict80 (id );

Create index idx_gyj2_t80m ongyj2_t80 (id );

SQL> show user;

USER is "GYJ"

SQL> conn/as sysdba

Connected.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

First dump

SQL> alter session set events 'immediate trace name buffers level1 ';

Session altered.

SQL> select * fromv $ diag_info where name = 'default Trace file ';

INST_ID NAME

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

VALUE

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

1 Default Trace File

/U01/app/oracle/diag/rdbms/jfdb/trace/jfdb_ora_7210.trc

A physical read takes an index.

Set autot on

Selectid, name, dbms_rowid.rowid_relative_fno (rowid) file #, dbms_rowid.rowid_block_number (rowid) block # from gyj1_t80 where id = 1;

SQL> selectid, 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 581

Select LRU_FLAG, lower (BA), TCHfrom 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, 'c C', 7, 'write', 8, 'Pi ', 9, 'memory ', 10, 'mwrite', 11,

3 'donated ', 12, 'protected', 13, 'securefile', 14, 'siop', 15, 'retries pt', 16, 'flashf

4 ree ', 17, 'flashcur', 18, 'flashna') from x $ bh where file # = 5 anddbablk = 581;

LRU_FLAG LOWER (BA) tch decode (STA

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

0 1000000009fca8000 1 xcur

SQL> selectLRU_FLAG, lower (BA), TCH from x $ bh where file # = 5 and dbablk = 581;

LRU_FLAG LOWER (BA) TCH

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

0 1000000009fca8000 5

SQL> set autot traceonly;

SQL> select/* + index (G) */count (name) fromgyj1_t80 G where id <= 8000;

SQL> selectLRU_FLAG, lower (BA), TCH from x $ bh where file # = 5 and dbablk = 581;

LRU_FLAG LOWER (BA) TCH

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

0 1000000009fca8000 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

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

0000000009fca8000 8

SQL> select LRU_FLAG, lower (BA), TCH from x $ bh where file # = 5 anddbablk = 581;

LRU_FLAG LOWER (BA) TCH

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

80010000009fca8000 0

BH (0x9ffe02a8) file #: 5 rdba: 0x01400245 (5/581) class: 1 ba: 0x9fca8000

Set: 5 pool: 3 bsz: 8192bsi: 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> selectLRU_FLAG, lower (BA), TCH from x $ bh where file # = 5 and dbablk = 581;

LRU_FLAG LOWER (BA) TCH

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

8 1000000009fca8000 0

SQL> select LRU_FLAG, lower (BA), TCH from x $ bh wherefile # = 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.