oracle buffer cache管理機制_buffer cache dump與lru機制小記
測試環境:
oracle11g r2
如文:
網上一些關於oracle buffer cache機制管理的貼子:
http://blog.csdn.net/tianlesoftware/article/details/6573438
/*******--表示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機制變化:非常好****/
1,採用主與輔助列表,就是為了讓資料塊儘量多在記憶體呆一會兒;
2,資料塊在主與輔助列表之間移動;
3,先從輔助列表開始掃描
4,如果使用完輔助列表,再有新的資料塊請求時,自輔助列表尾端掃描,重用;
不會像8i前清空被替換的buffer,而是把原buffer內容移動到主列表中
5,之後再有新的資料塊請求,先在輔助列表檢視;直到把輔助列表替換後,
即把新的新增到輔助列表上,把輔助列表原被替換的buffer插入到主列表中間
6,從輔助列表移出來的buffer要插入到主列表中間
7,直接輔助列表空了,如果再有新的資料塊請求,則必須從(反過來,原從輔助列表掃描,現輔助列表空了),
和前面的規則演算法一樣;又把被替換的主列表上的buffer移動到輔助列表上去;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9240380/viewspace-757438/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle buffer cache管理機制之lruw_dbwrOracle
- Linux記憶體管理Swap和Buffer Cache機制Linux記憶體
- buffer cache 內部機制深入探索【一】
- buffer cache部分原理(LRU)
- Linux記憶體管理機制中buffer和cache的區別Linux記憶體
- cache buffer lru chain latch等待事件AI事件
- Android快取機制-LRU cache原理與用法Android快取
- 【BUFFER】Oracle buffer cache之 latch 學習記錄Oracle
- Oracle Buffer Cache原理Oracle
- Oracle database buffer cacheOracleDatabase
- buffer cache實驗6-latch:cache buffers lru chainsAI
- oracle實驗記錄(buffer_cache分析(3)cbc lru chain latch)OracleAI
- Oracle Cache Buffer ChainsOracleAI
- page cache與buffer cache的關係
- Python 的快取機制: functools.lru_cachePython快取
- linux下的快取機制及清理buffer/cache/swap的方法梳理Linux快取
- Buffer Cache 原理
- cache buffer chainAI
- 【Cache】將常用的“小表”快取到Buffer Cache快取
- [Oracle Script] Buffer Cache Hit RatioOracle
- buffer與cache的區別
- Buffer 與 Cache 的區別
- Oracle中Buffer Cache記憶體結構Oracle記憶體
- Linux下的快取機制free及清理buffer/cache/swap的方法梳理Linux快取
- IO之核心buffer----"buffer cache"
- Buffer Cache結構及LRU, LRBA , Checkpoint Queue[final]
- Buffer Cache Hit Ratio
- CACHE BUFFER CHAINSAI
- Database Buffer Cache (79)Database
- Oracle Buffer Cache原理總結(一)Oracle
- Oracle Buffer Cache原理總結(二)Oracle
- oracle實驗記錄 (buffer_cache分析(1))Oracle
- buffer 與cache 的區別2
- 記憶體中,cache與buffer的含義記憶體
- 使用events DUMP buffer cache中指定的資料塊
- 將Buffer Cache內容強制寫出到資料檔案
- linux cache and buffer【轉】Linux
- Latch: cache buffer chains (%)AI