技术博客 数据库、运维与工程实践笔记

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

2026-04-26Oracle

**

**

**

**

**

**

**

**

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

1756815340536-6653f8b9-0dc8-493c-afc8-72deb74172e8.png

view plain copy![1756815340638-bc6bbab4-b2d7-4c29-b55f-398f3f7161db.png

](https://code.csdn.net/snippets/437707)[ico_fork.svg](https://code.csdn.net/snippets/437707/fork)

  1. sys@ZMDB> select CNUM_SET,CNUM_REPL,ANUM_REPL,CNUM_WRITE,ANUM_WRITE from x$kcbwds where CNUM_SET>0;

    2. 2.

  2. CNUM_SET CNUM_REPL ANUM_REPL CNUM_WRITE ANUM_WRITE
  3. ———- ———- ———- ———- ———-
  4. 15221 15221 3796 0 0
  5. 15221 15221 3783 0 0

CNUM_SET:工作组总的buffer总数量

CNUM_REPL:工作组中LRU的buffer总数量（主LRU+辅LRU）

ANUM_REPL:工作组中辅LRU总BUFFER的数量

通过隐含参数查到BUFFER的总的个数是30442，正好与上面的CNUM_SET=15221+15221 view plain copy![1756815340638-bc6bbab4-b2d7-4c29-b55f-398f3f7161db.png

](https://code.csdn.net/snippets/437707)[ico_fork.svg](https://code.csdn.net/snippets/437707/fork)

  1. sys@ZMDB> @?/rdbms/admin/show_para
  2. Enter value for p: _db_block_buffers
  3. old 12: AND upper(i.ksppinm) LIKE upper('%&p%')
  4. new 12: AND upper(i.ksppinm) LIKE upper('%_db_block_buffers%')

    5. 5.

  5. P_NAME P_DESCRIPTION P_VALUE ISDEFAULT ISMODIFIED ISADJ
  6. —————————————- ————————————————– —————————— ——— ———- —–
  7. _db_block_buffers Number of database blocks cached in memory: hidden 30442 TRUE FALSE FALSE
  8. Parameter

我们用以下语句查下数据库中buffer所在LRU的状态 view plain copy![1756815340638-bc6bbab4-b2d7-4c29-b55f-398f3f7161db.png

](https://code.csdn.net/snippets/437707)[ico_fork.svg](https://code.csdn.net/snippets/437707/fork)

  1. sys@ZMDB> select lru_flag,count(*) from x$bh group by lru_flag;

    2. 2.

  2. LRU_FLAG COUNT(*)
  3. ———- ———-
  4. 6 208
  5. 2 10
  6. 4 7122
  7. 8 15199
  8. 0 7646

我们对LRU_FLAG=6，2，4，8，0等做出解释，举个例子，对于6是什么含义呢？

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

view plain copy![1756815340638-bc6bbab4-b2d7-4c29-b55f-398f3f7161db.png

](https://code.csdn.net/snippets/437707)[ico_fork.svg](https://code.csdn.net/snippets/437707/fork)

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

    2. 2.

  2. LRU_FLAG LOWER(BA)
  3. ———- —————-
  4. 6 0000000081dae000

DUMP buffer_cache中BH信息，如下命令： view plain copy![1756815340638-bc6bbab4-b2d7-4c29-b55f-398f3f7161db.png

](https://code.csdn.net/snippets/437707)[ico_fork.svg](https://code.csdn.net/snippets/437707/fork)

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

    2. 2.

  2. Session altered.
  3. ys@ZMDB> col value for a85
  4. sys@ZMDB> select * from v$diag_info where name='Default Trace File';

    5. 6.

  5. INST_ID NAME VALUE
  6. ———- ————————————————– ————————————————————————————-
  7. 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

得到如下内容： view plain copy![1756815340638-bc6bbab4-b2d7-4c29-b55f-398f3f7161db.png

](https://code.csdn.net/snippets/437707)[ico_fork.svg](https://code.csdn.net/snippets/437707/fork)

  1. BH (0x81fe7e38) file#: 1 rdba: 0x0040ace1 (1/44257) class: 1 ba: 0x81dae000
  2. set: 6 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
  3. dbwrid: 0 obj: 421 objn: 423 tsn: 0 afn: 1 hint: f
  4. hash: [0x9ef9d710,0x853f8da8] lru: [0x81fe7df0,0x81fe8050]
  5. lru-flags: moved_to_tail on_auxiliary_list
  6. ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  7. st: CR md: NULL fpin: 'kdswh06: kdscgr' tch: 1
  8. cr: [scn: 0x0.80350f4d],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.80350f4d],[sfl: 0x0],[lc: 0x0.8034c532]
  9. 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

view plain copy![1756815340638-bc6bbab4-b2d7-4c29-b55f-398f3f7161db.png

](https://code.csdn.net/snippets/437707)[ico_fork.svg](https://code.csdn.net/snippets/437707/fork)

  1. sys@ZMDB> alter system flush buffer_cache;

    2. 2.

  2. System altered.

    3. 4.

  3. sys@ZMDB> select lru_flag,count(*) from x$bh group by lru_flag;

    4. 6.

  4. LRU_FLAG COUNT(*)
  5. ———- ———-
  6. 6 208
  7. 4 30009
  8. 0 2

第一次DUMP整个BUFFER CACHE: view plain copy![1756815340638-bc6bbab4-b2d7-4c29-b55f-398f3f7161db.png

](https://code.csdn.net/snippets/437707)[ico_fork.svg](https://code.csdn.net/snippets/437707/fork)

  1. sys@ZMDB> alter session set events'immediate trace name buffers level 1';
  2. /u01/app/oracle/diag/rdbms/zmdb/zmdb/trace/zmdb_ora_13480.trc

发生物理读 view plain copy![1756815340638-bc6bbab4-b2d7-4c29-b55f-398f3f7161db.png

](https://code.csdn.net/snippets/437707)[ico_fork.svg](https://code.csdn.net/snippets/437707/fork)

  1. gyj@ZMDB> conn gyj/gyj
  2. Connected.
  3. gyj@ZMDB> set autot on;
  4. 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;

    5. 5.

  5. ID NAME FILE# BLOCK#
  6. ———- —————————— ———- ———-
  7. 1 gyj1 7 139

    8. 9.

  8. Execution Plan
  9. ———————————————————-
  10. Plan hash value: 59758809

    11. 13.

  11. —————————————————————————-
  12. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  13. —————————————————————————-
  14. | 0 | SELECT STATEMENT | | 1 | 14 | 68 (0)| 00:00:01 |
  15. |* 1 | TABLE ACCESS FULL| GYJ_T1 | 1 | 14 | 68 (0)| 00:00:01 |
  16. —————————————————————————-

    17. 20.

  17. Predicate Information (identified by operation id):
  18. —————————————————

    19. 23.

  19. 1 – filter("ID"=1)

    20. 25. 26.

  20. Statistics
  21. ———————————————————-
  22. 1 recursive calls
  23. 1 db block gets
  24. 254 consistent gets
  25. 248 physical reads
  26. 0 redo size
  27. 733 bytes sent via SQL*Net to client
  28. 523 bytes received via SQL*Net from client
  29. 2 SQL*Net roundtrips to/from client
  30. 0 sorts (memory)
  31. 0 sorts (disk)
  32. 1 rows processed

    33. 40.

  33. sys@ZMDB> select LRU_FLAG,lower(BA),TCH from x$bh where file#=7 and dbablk=139;

    34. 42.

  34. LRU_FLAG LOWER(BA) TCH
  35. ———- —————- ———-
  36. 0 000000007d1b2000 1
  37. 4 0000000078558000 0
  38. 4 0000000085f68000 0

物理读完成后，再次dump整个buffer cache, view plain copy![1756815340638-bc6bbab4-b2d7-4c29-b55f-398f3f7161db.png

](https://code.csdn.net/snippets/437707)[ico_fork.svg](https://code.csdn.net/snippets/437707/fork)

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

    2. 2.

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

拿BA=7d1b2000,搜索第一次DUMP的trace文件 view plain copy![1756815340638-bc6bbab4-b2d7-4c29-b55f-398f3f7161db.png

](https://code.csdn.net/snippets/437707)[ico_fork.svg](https://code.csdn.net/snippets/437707/fork)

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

    2. 2.

  2. BH (0x7d3e8098) file#: 3 rdba: 0x00c0586b (3/22635) class: 34 ba: 0x7d1b2000
  3. set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
  4. dbwrid: 0 obj: -1 objn: 0 tsn: 2 afn: 3 hint: f
  5. hash: [0x9efa7570,0x9efa7570] lru: [0x7f7f5d30,0x7d3e8050]
  6. lru-flags: on_auxiliary_list
  7. ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
  8. st: FREE md: NULL fpin: 'ktuwh03: ktugnb' tch: 0 lfb: 33
  9. flags:

拿BA=7d1b2000,搜索第二次DUMP的trace文件 view plain copy![1756815340638-bc6bbab4-b2d7-4c29-b55f-398f3f7161db.png

](https://code.csdn.net/snippets/437707)[ico_fork.svg](https://code.csdn.net/snippets/437707/fork)

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

    2. 2.

  2. BH (0x7d3e8098) file#: 7 rdba: 0x01c0008b (7/139) class: 1 ba: 0x7d1b2000
  3. set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
  4. dbwrid: 0 obj: 22919 objn: 19567 tsn: 7 afn: 7 hint: f
  5. hash: [0x787e4bd8,0x9e4cda50] lru: [0x7f7f5d30,0x7d3e8050]
  6. ckptq: [NULL] fileq: [NULL] objq: [0x9a88e518,0x7d3e8078] objaq: [0x9a88e508,0x7d3e8088]
  7. st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1
  8. flags: only_sequential_access
  9. 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 view plain copy![1756815340638-bc6bbab4-b2d7-4c29-b55f-398f3f7161db.png

](https://code.csdn.net/snippets/437707)[ico_fork.svg](https://code.csdn.net/snippets/437707/fork)

  1. ALTER SYSTEM SET memory_max_target=0 scope=spfile;
  2. ALTER SYSTEM SET memory_target=0;
  3. alter system set sga_target=0;

    4. 4. 5.

  4. create table gyj1_t80 (id int,name char(2000));

    5. 7.

  5. create table gyj2_t80 (id int,name char(2000));

    6. 9.

  6. begin
  7. for i in 1 .. 30000
  8. loop
  9. insert into gyj1_t80 values(i,'gyj'||i);
  10. commit;
  11. end loop;
  12. end;
  13. /

    14. 18.

  14. SQL> SQL> select bytes/1024/1024||'M' from dba_segments where segment_name='GYJ1_T80' and owner='GYJ';

    15. 20.

  15. BYTES/1024/1024||'M'
  16. —————————————–
  17. 80M

    18. 24. 25.

  18. begin
  19. for i in 1 .. 30000
  20. loop
  21. insert into gyj2_t80 values(i,'gyj'||i);
  22. commit;
  23. end loop;
  24. end;
  25. /

    26. 34. 35.

  26. create index idx_gyj1_t80m on gyj1_t80(id);

    27. 37.

  27. create index idx_gyj2_t80m on gyj2_t80(id);

    28. 39.

  28. SQL> show user;
  29. USER is "GYJ"
  30. SQL> conn / as sysdba
  31. Connected.
  32. SQL> shutdown immediate;
  33. Database closed.
  34. Database dismounted.
  35. ORACLE instance shut down.
  36. SQL> startup
  37. ORACLE instance started.

第一次dump view plain copy![1756815340638-bc6bbab4-b2d7-4c29-b55f-398f3f7161db.png

](https://code.csdn.net/snippets/437707)[ico_fork.svg](https://code.csdn.net/snippets/437707/fork)

  1. SQL> alter session set events'immediate trace name buffers level 1';

    2. 2.

  2. Session altered.

    3. 4. 5.

  3. SQL> select * from v$diag_info where name='Default Trace File';

    4. 7.

  4. INST_ID NAME
  5. ———- ——————–
  6. VALUE
  7. ——————————————————————————–
  8. 1 Default Trace File
  9. /u01/app/oracle/diag/rdbms/jfdb/jfdb/trace/jfdb_ora_7210.trc

发生一个物理读走索引 view plain copy![1756815340638-bc6bbab4-b2d7-4c29-b55f-398f3f7161db.png

](https://code.csdn.net/snippets/437707)[ico_fork.svg](https://code.csdn.net/snippets/437707/fork)

  1. set autot on
  2. select id,name,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from gyj1_t80 where id=1;

    3. 3. 4.

  3. SQL> select id,name,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from gyj1_t80 where id=1;

    4. 6.

  4. ID NAME FILE# BLOCK#
  5. ———- ——————– ———- ———-
  6. 1 gyj1 5 581

    7. 10. 11.

  7. select LRU_FLAG,lower(BA),TCH from x$bh where file#=5 and dbablk=581;
  8. SQL> select LRU_FLAG,lower(BA),TCH, decode(state,0,'free',1,'xcur',2,'scur'
  9. 2 ,3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,
  10. 3 'donated', 12,'protected', 13,'securefile', 14,'siop',15,'recckpt', 16, 'flashf
  11. 4 ree', 17, 'flashcur', 18, 'flashna') from x$bh where file#=5 and dbablk=581;

    12. 17.

  12. LRU_FLAG LOWER(BA) TCH DECODE(STA
  13. ———- —————- ———- ———-
  14. 0 000000009fca8000 1 xcur

    15. 21. 22.

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

    16. 24.

  16. LRU_FLAG LOWER(BA) TCH
  17. ———- —————- ———-
  18. 0 000000009fca8000 5

    19. 28. 29. 30. 31.

  19. SQL> set autot traceonly;
  20. SQL> select /*+ index(G) */ count(name) from gyj1_t80 G where id<=8000;

    21. 34. 35.

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

    22. 37.

  22. LRU_FLAG LOWER(BA) TCH
  23. ———- —————- ———-
  24. 0 000000009fca8000 6

再次发生物理读，此时LRU_FLAG=0变为8，同时TCH=8重置为0 view plain copy![1756815340638-bc6bbab4-b2d7-4c29-b55f-398f3f7161db.png

](https://code.csdn.net/snippets/437707)[ico_fork.svg](https://code.csdn.net/snippets/437707/fork)

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

    2. 2.

  2. LRU_FLAG LOWER(BA) TCH
  3. ———- —————- ———-
  4. 0 000000009fca8000 8

    5. 6.

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

    6. 8.

  6. LRU_FLAG LOWER(BA) TCH
  7. ———- —————- ———-
  8. 8 000000009fca8000 0

    9. 12. 13.

  9. BH (0x9ffe02a8) file#: 5 rdba: 0x01400245 (5/581) class: 1 ba: 0x9fca8000
  10. set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 15,19
  11. dbwrid: 0 obj: 13537 objn: 13537 tsn: 5 afn: 5 hint: f
  12. hash: [0xb6a86de0,0xb6a86de0] lru: [0x9ffe0260,0x9ffe9a60]
  13. lru-flags: hot_buffer
  14. ckptq: [NULL] fileq: [NULL] objq: [0x9ffe0618,0x9ffe0028] objaq: [0x9ffe0628,0x9ffe0038]
  15. st: XCURRENT md: NULL fpin: 'kdswh05: kdsgrp' tch: 0
  16. flags:
  17. LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]

当TCH=0时，再发生大量物理读，地址为9fca8000的BUFFER就被重用了，彻底从BUFFER消失 view plain copy![1756815340638-bc6bbab4-b2d7-4c29-b55f-398f3f7161db.png

](https://code.csdn.net/snippets/437707)[ico_fork.svg](https://code.csdn.net/snippets/437707/fork)

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

    2. 2.

  2. LRU_FLAG LOWER(BA) TCH
  3. ———- —————- ———-
  4. 8 000000009fca8000 0

    5. 6.

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

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

[ ]()

    .#### 我的同类文章

                                Oracle Internal_（16）_
                            _•_[CBO之B*Tree Index Range Scan - IRS算法](http://blog.csdn.net/guoyJoe/article/details/44262353)2015-03-14_•_[理解 B*tree index内部结构](http://blog.csdn.net/guoyJoe/article/details/40589651)2014-10-29_•_[数据块内部偏移量的基本计算方法](http://blog.csdn.net/guoyJoe/article/details/32715157)2014-06-20_•_[UNDO段头块格式深度解析](http://blog.csdn.net/guoyJoe/article/details/18355579)2014-01-16_•_[redolog switch会发生完全检查点还是增量检查点？](http://blog.csdn.net/guoyJoe/article/details/8918280)2013-05-12_•_[CBO之Full Table Scan - FTS算法](http://blog.csdn.net/guoyJoe/article/details/44261859)2015-03-14_•_[利用bbed来修复ora-08102错误](http://blog.csdn.net/guoyJoe/article/details/39677575)2014-09-29_•_[计算SGA各池的内存地址的边界](http://blog.csdn.net/guoyJoe/article/details/18508283)2014-01-19_•_[揭密一致性读------之UNDO一致性构造](http://blog.csdn.net/guoyJoe/article/details/14088663)2013-11-02_•_[一条UPDATE从生到死的整个过程的深入解析](http://blog.csdn.net/guoyJoe/article/details/8792563)2013-04-12[更多文章](http://blog.csdn.net/guoyJoe/article/category/1363998)