物理讀之LRU（最近最少被使用）的深入解析

一組LRU鏈表包括LRU主鏈，LRU輔助鏈，LRUW主鏈，LRUW輔助鏈，稱為一個WorkSet(工作組)如下圖：

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:工作組總的buffer總數量

 CNUM_REPL:工作組中LRU的buffer總數量（主LRU+輔LRU）

 ANUM_REPL:工作組中輔LRU總BUFFER的數量

通過隱含參數查到BUFFER的總的個數是30442，正好與上面的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

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

_db_block_buffers                        Number of database blocks cached inmemory: hidden 30442                         TRUE      FALSE        FALSE

                                          Parameter

我們用以下語句查下數據庫中buffer所在LRU的狀態

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

我們對LRU_FLAG=6，2，4，8，0等做出解釋，舉個例子，對于6是什么含義呢？

首先要在x$bh中找到lru_flag=6的任意的一個BUFFER

sys@ZMDB> select LRU_FLAG,LOWER(BA)from x$bh where lru_flag=6 andrownum=1;

  LRU_FLAG LOWER(BA)

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

         6 0000000081dae000

DUMP buffer_cache中BH信息，如下命令：

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

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

1                        Default Trace File                                /u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13235.trc

通過BA=81dae000搜索trace文件，

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

得到如下內容：

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: 1hint: 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的意思是lru-flags: moved_to_tail on_auxiliary_list，就是向LRU的輔助鏈表的尾部移動，這有可能是SMON從LRU的主鏈表上的非臟塊、TCH<=1并且狀態是非PIN的BUFFER被掛接到LRU輔助鏈表的尾部。

根據以上的方法同理可以解釋出LRU_FLAG的含義：

LRU_FLAG

0==>LRU-主鏈冷端的頭部，這個比較特殊他在DUMP沒有顯示LRU_FLAG

2==>LRU-主鏈冷端的尾部，lru-flags:moved_to_tail

4==>LRU-輔助鏈,lru-flags:on_auxiliary_list

6==>LRU-輔助鏈的尾部，lru-flags:moved_to_tail on_auxiliary_list

8==>LUR-主鏈熱端，lru-flags:hot_buffer

當發生物理讀時，Oracle會從LRU輔助鏈表找空閑的BUFFER，然后把LRU輔助的鏈上的BUFFER掛接到LRU主鏈的冷端頭，實驗如下：

首先要保證有LRU輔助鏈上的BUFFER,即有LRU_FLAG=6或LRU_FLAG=4,如果數據庫剛剛啟來，可能沒有LRU_FLAG=6、LRU_FLAG=4，那需要做大量的物理讀操作，才會有LRU_FLAG=6或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

第一次DUMP整個BUFFER CACHE:

sys@ZMDB> alter session set events'immediate trace name bufferslevel 1';

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

發生物理讀

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 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 processed

sys@ZMDB> selectLRU_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

物理讀完成后，再次dump整個buffer cache,

sys@ZMDB>alter session set events'immediate trace name buffers level 1';

/u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13511.trc

拿BA=7d1b2000,搜索第一次DUMP的trace文件

/u01/app/oracle/diag/rdbms/zmdb/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:

拿BA=7d1b2000,搜索第二次DUMP的trace文件

/u01/app/oracle/diag/rdbms/zmdb/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: 'kdswh21:kdst_fetch' tch: 1

  flags: only_sequential_access

  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN:[0xffff.ffffffff] HSUB: [65535]

從上面的兩個trace可以得出結論ba: 0x7d1b2000

從lru-flags:on_auxiliary_list(LRU_FLAG=4)到LRU-主鏈冷端的頭部，這個比較特殊在DUMP沒有顯示LRU_FLAG(LRU_FLAG=0)

觀察LRUTCH>=2時冷端移到熱端

1、BUFFER手動設為100M

 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 1 .. 30000

  loop

    insert into gyj1_t80 values(i,'gyj'||i);

 commit;

 end loop;

end;

/

SQL> SQL> selectbytes/1024/1024||'M' from dba_segments where segment_name='GYJ1_T80' andowner='GYJ';

BYTES/1024/1024||'M'

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

80M

begin

  for i in 1 .. 30000

  loop

    insert into gyj2_t80 values(i,'gyj'||i);

 commit;

 end loop;

end;

/

create index idx_gyj1_t80m ongyj1_t80(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.

第一次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/jfdb/trace/jfdb_ora_7210.trc

發生一個物理讀走索引

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,'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 anddbablk=581;

  LRU_FLAG LOWER(BA)               TCH DECODE(STA

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

         0 000000009fca8000          1 xcur

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

  LRU_FLAG LOWER(BA)               TCH

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

         0 000000009fca8000          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 000000009fca8000          6

再次發生物理讀，此時LRU_FLAG=0變為8，同時TCH=8重置為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

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

         8000000009fca8000          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]

當TCH=0時，再發生大量物理讀，地址為9fca8000的BUFFER就被重用了，徹底從BUFFER消失

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

  LRU_FLAG LOWER(BA)               TCH

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

         8 000000009fca8000          0

SQL>  select LRU_FLAG,lower(BA),TCH from x$bh wherefile#=5 and dbablk=581;

no rows selected

通過實驗，我們更清楚地了解到物理讀LRU的基本流程，可以進一步理解物理讀內部的LRU算法。