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

一组LRU链表包括LRU主链，LRU辅助链，LRUW主链，LRUW辅助链，称为一个WorkSet(工作组)如下图：sys@ZMDBselectCNUM_SET,CNUM_REPL,ANUM_REPL,CNUM_WRITE,ANUM_WRI

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

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

 

观察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;

 

   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;