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

转载请注明出处：

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

一组LRU链表包含LRU主链。LRU辅助链。LRUW主链，LRUW辅助链，称为一个WorkSet(工作组)例如以下图：

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:工作组总的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) 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

我们用下面语句查下数据库中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 and rownum=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 Trace File';

   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: 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的意思是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> select lru_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 buffers level 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> 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

物理读完毕后。再次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: 'kdswh11: 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)

观察LRU TCH>=2时冷端移到热端

1、BUFFER手动设为100M

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'
-----------------------------------------
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 on gyj1_t80(id);

create index idx_gyj2_t80m on gyj2_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 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

发生一个物理读走索引

set autot on
select 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        581


select 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 xcur


SQL> select LRU_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) 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

再次发生物理读，此时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
---------- ---------------- ----------
         0 000000009fca8000          8

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

  LRU_FLAG LOWER(BA)               TCH
---------- ---------------- ----------
         8 000000009fca8000          0


BH (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]

当TCH=0时。再发生大量物理读，地址为9fca8000的BUFFER就被重用了，彻底从BUFFER消失

SQL>  select LRU_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 where file#=5 and dbablk=581;
no rows selected 

通过实验。我们更清楚地了解到物理读LRU的基本流程。能够进一步理解物理读内部的LRU算法。