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

aks5201314 · 发表于 2014-7-30 15:08

收藏。

guoyJoe · 发表于 2014-7-30 15:41

还不算晕发表于 2014-7-29 22:19
郭大师霸气，，特地上来回复下

白总做点补充呗

guoyJoe · 发表于 2014-7-30 15:42

diashad 发表于 2014-7-30 08:08
先收藏，再了解。

了解，沟通，总结，记录。

guoyJoe · 发表于 2014-7-30 15:42

aks5201314 发表于 2014-7-30 15:08
收藏。

guoyJoe · 发表于 2014-7-31 10:51

*****************************
  总一下辅助链LRU_AUX
*****************************
1、CR块是挂在辅助链表中的，在一个CBC链上最多放几个CR块有隐含参数决定：_db_block_max_cr_dba，
当前11g是6个之后开始就覆盖了，否则无限增多，消耗 buffer量太大
做个update操作就能构造出CR块来
sys@OCM>  select file#,dbablk,ba,lru_flag,
  2 decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi',
  3 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt',
  4 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,
  5 decode(bitand(flag,1), 0, 'N', 'Y') dirty,tch from x$bh where file#=7 and dbablk=5959;

   FILE#    DBABLK BA                LRU_FLAG STATUS    D       TCH
---------- ---------- ---------------- ---------- ---------- - ----------
      7    5959 000000008C77E000       8 xcur    N       1

gyj@OCM> update gyj1_80m set name='AAAAAA' where id=1;

1 row updated.

gyj@OCM> select * from gyj1_80m where id=1;

      ID NAME
-----------  ------
  1          gyj1

sys@OCM> select file#,dbablk,ba,lru_flag,
  2 decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi',
  3 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt',
  4 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,
  5 decode(bitand(flag,1), 0, 'N', 'Y') dirty,tch from x$bh where file#=7 and dbablk=5959;

   FILE#    DBABLK BA                LRU_FLAG STATUS    D       TCH
---------- ---------- ---------------- ---------- ---------- - ----------
      7    5959 000000008DC4A000       0 cr       N       1
      7    5959 000000008C77E000       8 xcur    Y       1

2、大表全表扫描，也挂在辅助链表上
有一个隐藏参数，_small_table_threshold，单位是块数。
表的块数低于此参数的值，就算小表，否则就是大表。该参数的缺省值是Buffer Cache总Buffer数的2%。
在辅助链上大表的数据也可以重用的，只要辅助链buffer的状态不是free.
sys@OCM> shutdown immediate;
sys@OCM> startup
sys@OCM> select CNUM_SET,CNUM_REPL,ANUM_REPL from x$kcbwds where CNUM_SET>0;

  CNUM_SET  CNUM_REPL  ANUM_REPL
---------- ---------- ----------
   6138    6138    3650
   6137    6137    3658


gyj@OCM> set autot traceonly;
gyj@OCM>  select * from gyj1_80m;

30000 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2945941664

------------------------------------------------------------------------------
| Id  | Operation       | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |       | 34137 | 65M|  2740 (1)| 00:00:33 |
| 1 |  TABLE ACCESS FULL| GYJ1_80M | 34137 | 65M|  2740 (1)| 00:00:33 |
------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
      25  recursive calls
      2  db block gets
   12503  consistent gets
   10420  physical reads
      0  redo size
61022045  bytes sent via SQL*Net to client
   22509  bytes received via SQL*Net from client
   2001  SQL*Net roundtrips to/from client
      6  sorts (memory)
      0  sorts (disk)
   30000  rows processed

select file#,dbablk,ba,lru_flag,
  decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi',
  9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt',
  16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,
  decode(bitand(flag,1), 0, 'N', 'Y') dirty,tch from x$bh where file#=7 and dbablk=5959;

  sys@OCM> select file#,dbablk,ba,lru_flag,
  2 decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi',
  3 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt',
  4 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,
  5 decode(bitand(flag,1), 0, 'N', 'Y') dirty,tch from x$bh where file#=7;

   FILE#    DBABLK BA                LRU_FLAG STATUS    D       TCH
---------- ---------- ---------------- ---------- ---------- - ----------
      7    1288 000000008C942000       0 xcur    N       0
      7    1322 000000008C934000       0 xcur    N       0
      7    6016 000000008CC74000       0 xcur    N       1
      7    1246 000000008CB12000       0 xcur    N       0
      7    1293 000000008CB06000       0 xcur    N       0
      7    5953 000000008CE76000       0 xcur    N       1
      7    6144 000000008CE72000       0 xcur    N       1
      7       384 000000008CBCC000       0 xcur    N       1
      7    1316 000000008C93A000       2 xcur    N       0
      7    2049 000000008CBD2000       0 xcur    N       1
      7    1287 000000008CB0C000       0 xcur    N       0
      7    6036 000000008CBC6000       6 xcur    N       0

3、刷新BUFFER CACHE

gyj@OCM> select dbms_rowid.ROWID_RELATIVE_FNO(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id,name from gyj1_80m where id=1;

FILE_ID BLOCK_ID NAME
---------- ----------  -------
  7       5959       gyj1

select file#,dbablk,ba,lru_flag,
  decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi',
  9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt',
  16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,
  decode(bitand(flag,1), 0, 'N', 'Y') dirty,tch from x$bh where file#=7 and dbablk=5959;

FILE#    DBABLK BA                LRU_FLAG STATUS    D       TCH
---------- ---------- ---------------- ---------- ---------- - ----------
      7    5959 000000008DF24000       0 xcur    N       1

sys@OCM> alter system flush buffer_cache;

System altered.

sys@OCM> select file#,dbablk,ba,lru_flag,
  2 decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi',
  3 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt',
  4 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,
  5 decode(bitand(flag,1), 0, 'N', 'Y') dirty,tch from x$bh where file#=7 and dbablk=5959;

   FILE#    DBABLK BA                LRU_FLAG STATUS    D       TCH
---------- ---------- ---------------- ---------- ---------- - ----------
      7    5959 000000008DF24000       4 free    N       0

gyj@OCM> select dbms_rowid.ROWID_RELATIVE_FNO(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id,name from gyj1_80m where id=1;

FILE_ID BLOCK_ID NAME
---------- ----------  -------
  7       5959       gyj1

sys@OCM> select file#,dbablk,ba,lru_flag,
  2 decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi',
  3 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt',
  4 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,
  5 decode(bitand(flag,1), 0, 'N', 'Y') dirty,tch from x$bh where file#=7 and dbablk=5959;

   FILE#    DBABLK BA                LRU_FLAG STATUS    D       TCH
---------- ---------- ---------------- ---------- ---------- - ----------
      7    5959 000000008942C000       0 xcur    N       1
      7    5959 000000008DF24000       4 free    N       0 -------看状态是free空闲的。

4、数据库重启
sys@OCM>shutdown immediate;
sys@OCM>startup

sys@OCM> select CNUM_SET,CNUM_REPL,ANUM_REPL from x$kcbwds where CNUM_SET>0;

  CNUM_SET  CNUM_REPL  ANUM_REPL
---------- ---------- ----------
      0       0       0
      0       0       0
      0       0       0
      0       0       0
   6138    6138    3707
   6137    6137    3715
      0       0       0
      0       0       0
      0       0       0
      0       0       0
      0       0       0
      0       0       0
      0       0       0
      0       0       0
      0       0       0
      0       0       0

16 rows selected.