测试环境:
oracle11g r2
如文:
网上一些关于oracle buffer cache机制管理的贴子:
http://blog.csdn.net/tianlesoftware/article/details/6573438
http://www.knowsky.com/384953.html
/*******--表示dump缓冲区的头部***********/
SQL> alter session set events 'immediate trace name buffers level 1';
Session altered
/********dump level 1 buffer cache****仅dump buffer header *********/
Dump of buffer cache at level 1 for tsn=2147483647, rdba=0 /*****也指明是level 1 trace****/
BH (0x000007FF067F0338) file#: 3 rdba: 0x00c07ca4 (3/31908) class: 38 ba: 0x000007FF0668E000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 3,28 --注意这里有个pool
dbwrid: 0 obj: -1 objn: 0 tsn: 2 afn: 3 hint: f
hash: [0x000007FF3DE83238,0x000007FF3DE83238] lru: [0x000007FF167EB100,0x000007FF273E8050]--lru链表的地址
ckptq: [NULL] fileq: [NULL] objq: [0x000007FF1C7F9FD8,0x000007FF16FDA858]--oracle内部数据结构采用链表方式管理
st: XCURRENT md: NULL tch: 2
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [32] --缓冲区的lscn,hscn
cr pin refcnt: 0 sh pin refcnt: 0 --一致性pin与共享pin
/****level 2直接dump整个buffer cache************/
alter session set events 'immediate trace name buffers level 2';
trace文件内容不贴出,它是把缓冲在buffer cache所有的数据块:包括表的data block
和undo block,undo header block全部展示出来;
/**********测试下buffer cache缓冲块的状态变更与重用*************/
SQL> show user
USER is "SCOTT"
SQL> create table t_buffer(a int);
Table created.
SQL> insert into t_buffer select 1 from dual;
1 row created.
SQL> commit;
Commit complete.
/********确认测试表的文件号及块号**********/
SQL> select dbms_rowid.rowid_block_number(rowid) block_id,dbms_rowid.rowid_relative_fno(rowid) file_id from scott.t_buffer;
BLOCK_ID FILE_ID
---------- ----------
718388 10
/********连续在另一会话查询测试14次*****发现lru_flag未发生变化*******/
SQL> select lru_flag,tch from x$bh where dbarfil=10 and dbablk=718388
LRU_FLAG TCH
---------- ----------
0 14
/******直接查询测试表未改变lru_flag**********/
SQL> declare
2 x pls_integer;
3 begin
4 for i in 1..100000 loop
5 select a into x from t_buffer;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
/********对于循环处理测试表tch仅增1*********/
SQL> /
LRU_FLAG TCH
---------- ----------
0 20
SQL> alter session set events 'immediate trace name buffers level 4';
Session altered
/***level 4可以分析lru链表的信息*******/
Dump of buffer cache at level 4 for tsn=2147483647, rdba=0
摘录一些网上的知识,对于梳理buffer cache机制很有帮助:
1,buffer同时只在在lru及lruw一个链表中
2,内存增大,为了提升性能,仅用一条lru和lruw管理buffer cache不够用;
因为多个dbwr会去竞争扫描同一个lruw或lru,这样cache buffers lru chain latch竞争很高;
(此latch是为了保护cache buffer lru chain数据结构)
3,所以oracle提出working set概念,即每个working set包含一组lru和lruw list;
每个working set由一个cache buffers lru chain来保护.
从这个意义上来讲,lru chain latch对应一个working set.
4,如果新的数据块要加入到buffer cache中,就要在每个working set查找,即轮询每个working set的lru latch,
如果得到lru latch,则加入数据块到此working set对应的lru list或lruw list;
如果查完全部的lru latch,仍未找到;则在v$session_wait增加latch misses;并产生等待事件latch free
5,lru latch即一个working set,则working set即lru latch数量.
而lru latch由_db_block_lru_latches决定;
默认值为dbwr进程的8倍
*** 2013-03-29 16:59:27.907
/****里面有很多的workset,每个workset包括不同类型的list,如下:ws_repl_list,即lru,wr_write_list即lruw,ws_req_list我估计是请求分配buffer cache的list;ws_l2keep_list我估计是固化一些buffer列表***/
(WS) size: 0 (0) wsid: 1 state: 0 pool: 1
(WS_REPL_LIST) main_prev: 0x000007FF3D4A6EE8 main_next: 0x000007FF3D4A6EE8 aux_prev: 0x000007FF3D4A6EF8 aux_next: 0x000007FF3D4A6EF8
curnum: 0 auxnum: 0
/******cold表明冷端*********/
cold: 7ff3d4a6ee8 hbmax: 0 hbufs: 0
(WS_WRITE_LIST) main_prev: 0x000007FF3D4A6F18 main_next: 0x000007FF3D4A6F18 aux_prev: 0x000007FF3D4A6F28 aux_next: 0x000007FF3D4A6F28
curnum: 0 auxnum: 0
(WS_XOBJ_LIST) main_prev: 0x000007FF3D4A6F48 main_next: 0x000007FF3D4A6F48 aux_prev: 0x000007FF3D4A6F58 aux_next: 0x000007FF3D4A6F58
curnum: 0 auxnum: 0
(WS_XRNG_LIST) main_prev: 0x000007FF3D4A6F78 main_next: 0x000007FF3D4A6F78 aux_prev: 0x000007FF3D4A6F88 aux_next: 0x000007FF3D4A6F88
curnum: 0 auxnum: 0
(WS_REQ_LIST) main_prev: 0x000007FF3D4A6FA8 main_next: 0x000007FF3D4A6FA8 aux_prev: 0x000007FF3D4A6FB8 aux_next: 0x000007FF3D4A6FB8
curnum: 0 auxnum: 0
(WS_L2WRT_LIST) main_prev: 0x000007FF3D4A6FD8 main_next: 0x000007FF3D4A6FD8 aux_prev: 0x000007FF3D4A6FE8 aux_next: 0x000007FF3D4A6FE8
curnum: 0 auxnum: 0
(WS_L2REPL_LIST) main_prev: 0x000007FF3D4A7008 main_next: 0x000007FF3D4A7008 aux_prev: 0x000007FF3D4A7018 aux_next: 0x000007FF3D4A7018
curnum: 0 auxnum: 0
(WS_L2KEEP_LIST) main_prev: 0x000007FF3D4A7038 main_next: 0x000007FF3D4A7038 aux_prev: 0x000007FF3D4A7048 aux_next: 0x000007FF3D4A7048
curnum: 0 auxnum: 0
(WS) fbwanted: 0
(WS) bgotten: 0 sumwrt: 0
(WS) pwbcnt: 0, last: 0
/***插补lru知识/
1,8i后,引入更为复杂机制管理lru上的数据块
2,8i后,lru及lruw链表都有2个子链表,分别叫作辅助链表和主键表
3,且x$bh增加tch,用于对lru链表进行管理
MAIN RPL_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN RPL_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY RPL_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY RPL_LST Queue header (PREV_DIRECTION)[NULL]
MAIN WRT_LST Queue header (NEXT_DIRECTION)[NULL] --与上相应:主lru列表
MAIN WRT_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY WRT_LST Queue header (NEXT_DIRECTION)[NULL] ---与上相应:辅助lru列表
AUXILIARY WRT_LST Queue header (PREV_DIRECTION)[NULL]
MAIN XOBJ_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN XOBJ_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY XOBJ_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY XOBJ_LST Queue header (PREV_DIRECTION)[NULL]
MAIN XRNG_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN XRNG_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY XRNG_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY XRNG_LST Queue header (PREV_DIRECTION)[NULL]
MAIN REQ_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN REQ_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY REQ_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY REQ_LST Queue header (PREV_DIRECTION)[NULL]
MAIN L2W_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN L2W_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY L2W_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY L2W_LST Queue header (PREV_DIRECTION)[NULL]
MAIN L2R_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN L2K_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN L2R_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY L2R_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY L2K_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY L2R_LST Queue header (PREV_DIRECTION)[NULL]
MAIN L2K_LST Queue header (NEXT_DIRECTION)[NULL]
MAIN L2K_LST Queue header (PREV_DIRECTION)[NULL]
AUXILIARY L2K_LST Queue header (NEXT_DIRECTION)[NULL]
AUXILIARY L2K_LST Queue header (PREV_DIRECTION)[NULL]
/*****chain细节********已略去同性质的chain/
/*******发现一个chain对应多个buffer******即x$bh每个条目********/
CHAIN: 5969 LOC: 0x000007FF3DEA2438 HEAD: [0x000007FF0CBF94C8,0x000007FF0CBF94C8]
BH (0x000007FF0CBF9418) file#: 2 rdba: 0x008174b7 (2/95415) class: 1 ba: 0x000007FF0CB82000
set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 5,28
dbwrid: 0 obj: 6192 objn: 6192 tsn: 1 afn: 2 hint: f
hash: [0x000007FF3DEA2438,0x000007FF3DEA2438] lru: [0x000007FF013EE8D0,0x000007FF16FED110]
lru-flags: debug_dump --lru-flag标志
ckptq: [NULL] fileq: [NULL] objq: [0x000007FF25BDAE48,0x000007FF013EE8F8]
st: XCURRENT md: NULL tch: 12
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
CHAIN: 5970 LOC: 0x000007FF3DEA2448 HEAD: [NULL]
/*********hash chain即上述的chain,由多个buffer 挂在其上******/
/****一个hash join的buffer链表list****/
尾端 首端
buffer1
1,select的buffer为上述的buffer1,依次类推
2,buffer为lru的头端;buffer4为lru 的尾端
3,如果此时再加入一个buffer,发现buffer cache不足了,就要重用上述的buffer
从lru最冷端开始,即替换buffer1,清空其内存内容,换上新的数据块
4,首端永远是最近访问的buffer地址
新添加一个buffer后的列表如下:
尾端 首端
buffer2 新替换的buffer1
小结下:
1,新添加的buffer在之前添加的buffer之后,新添加就首端的,之前叫尾端
2,替换时替换掉最先添加的即lru 冷端的buffer1,并修改其指向到buffer4,且buffer4
指向buffer1;这样buffer2变成了最冷端;而新替换的buffer1为最热端
/***oracle8i后的lru机制变化:非常好****/
http://oracle.chinaitlab.com/serial/533718_2.html
1,采用主与辅助列表,就是为了让数据块尽量多在内存呆一会儿;
2,数据块在主与辅助列表之间移动;
3,先从辅助列表开始扫描
4,如果使用完辅助列表,再有新的数据块请求时,自辅助列表尾端扫描,重用;
不会像8i前清空被替换的buffer,而是把原buffer内容移动到主列表中
5,之后再有新的数据块请求,先在辅助列表查看;直到把辅助列表替换后,
即把新的添加到辅助列表上,把辅助列表原被替换的buffer插入到主列表中间
6,从辅助列表移出来的buffer要插入到主列表中间
7,直接辅助列表空了,如果再有新的数据块请求,则必须从(反过来,原从辅助列表扫描,现辅助列表空了),
和前面的规则算法一样;又把被替换的主列表上的buffer移动到辅助列表上去;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-757438/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-757438/