buffer cache實驗9-從buffer caceh中讀取資料塊解析-從邏輯讀到物理讀

先來張大圖：

 

所用SQL語句：
BYS@ ocm1>select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,deptno from bys.test;
     FILE#     BLOCK#     DEPTNO
---------- ---------- ----------
         4        391         10

就以上圖為例，文字描述分析一下前臺程式發出查詢語句時獲取所需資料塊的過程:

注：本文不涉及SQL語句的解析部分、客戶端與伺服器互動等，只涉及buffer cache。
這裡的物理讀是非直接路徑讀、非大表全表掃描--此點最後會有介紹。
如果發出的是更新語句，只是在buffer pin上所加的鎖為X獨佔鎖，其它步驟基本一致。
本文的例子只讀取了一個資料塊。
從buffer cache中讀取一個資料塊一般需要100ns左右，從一般的儲存硬碟中讀取一個資料塊需要10ms；所以大概算一下，從記憶體中讀取資料塊比從硬碟中快近十萬倍。
故oracle在讀取資料塊時，先在buffer cache中查詢，如存在，則讀取--邏輯讀；如果資料塊不存在，則發生物理讀，從物理檔案中將資料讀入buffer cache（不考慮直接讀的情況）。
之前寫過的邏輯讀的： 資料讀取之邏輯讀簡單解析--關於BUFFER CACHE

下面正式開始：--首先是邏輯讀的過程

1.前臺程式發出查詢語句select deptno from bys.test;
2.根據DBA計算HASH值，根據HASH值找到相應的Hash bucket
3.獲取CBC LATCH，如獲取失敗，則將產生：latch:cache buffers chains
4.在CBC LATCH保護下，伺服器程式掃描hash chain，查詢是否有所需BH
5.如查詢到所需BH，將在Buffer Header上加buffer pin鎖（這裡是讀操作所以是共享鎖（找到BH時的鎖常見有：當前讀鎖、一致讀鎖或修改鎖），如獲取buffer pin失敗（比如正在X模式申請S模式），會產生buffer busy waits等待），並根據BH中指定的塊在記憶體中實際地址，讀取buffer，並將結果返回前臺程式。讀取完畢（納秒級）後，將再次獲取CBC LATCH，釋放buffer pin鎖，再釋放CBC LATCH。 

-----以上為邏輯讀，如果未找到buffer,將發生如下的物理讀：

6.如果未查詢到所需BH，將發生物理讀。伺服器程式將從磁碟上的相應資料檔案中讀取所需塊，並將此塊讀入buffer cche中。
7.將塊讀入buffer cache中時，如何找到一個可以使用的buffer呢？下面步驟進行一步步解析。
8.首先在輔助LRU的最尾端向前查詢可用buffer,TCH<2的塊可以被重用。
9.如果輔助LRU最尾端的塊是TCH<2的塊，則將直接使用此塊，並將其移動到主LRU的冷端頭。
同時也會根據此塊的DBA進行HASH，查詢相應的HASH BUCKET，將此塊加入到對應的HASH CHAIN上，並對BH中的相應資訊進行修改（如對應X$BH中的LRU_FLAG，NXT_HASH、BA等欄位的具體值）--此過程也需要相應的CBC LATCH /buffer pin鎖的獲取釋放等。
再把資料塊的值返回前臺程式，此時物理讀就完成了。
10.如果輔助LRU最尾端的塊是TCH>=2的塊，則首先將此塊移動到主LRU的熱端頭，同時TCH清零；然後在在輔助LRU上繼續向前查詢，直到找到可用的塊---TCH<2。之後的過程和步驟9中的就一樣了。（SMON每三秒時，伺服器程式掃描空閒BUFFER時；都會把輔助LRU中TCH大於等於2的移到主的熱端頭）
11.如果在輔助LRU上搜尋完畢扔未找到可以使用的塊，則將從主LRU的冷端尾開始搜尋。
12.如果主LRU最尾端的塊是TCH<2的塊，則將直接使用此塊，並將其移動到主LRU的冷端頭，TCH為1。如是TCH>=2的塊，則將其移動到熱端頭，TCH清零。如果是髒塊，則將其移動到主LRUW上。依此規則向前搜尋查詢可用塊。（SMON每3秒，從主LRU冷端查詢TCH小於2的非髒塊到輔助LRU確保輔助LRU中有可用BUFFER）
13.如果從主LRU最尾端向前搜尋了40%（隱含引數_db_block_max_scan_pct，）還未找到可用塊，則將觸發DBWR寫LRUW上的髒塊--（CKPTQ佇列的寫不涉及LRUW，只有DBWR會寫LRUW上髒塊，並且寫的是LRUW上的全部髒塊-每三秒醒來也要全部寫出LRUM上所有塊才會休眠。寫LRUW上髒塊的步驟是：DBWR程式寫時或者SMON程式每三秒醒來時（LRUW程式不像輔助LRUW那樣，非DBWR程式也允許訪問），會將主LRUW上的一部分髒塊移動到輔助LRUW，然後在輔助LRUW上排序、寫入磁碟；然後再從主LRUW上移動下一批，直到寫出完畢再次進入睡眠。並且在DBWR寫出過程中，會產生free buffer waits），寫出後的buffer將重新掛載到輔助LRU上並變為可用。

關於大表全表掃描及_small_table_threshold   引數的說明：

大小表的界限是：_small_table_threshold，此引數中的VALUE 是資料塊個數。
大表的全表掃描只使用輔助LRU，其塊的TCH為1。這樣做不對主LRU上的塊進行衝擊，同時也方便大表中塊的重用。此時如有其它使用者語句需要從輔助LRU上查詢可用buffer，直接可以使用，節約時間。
小表的全表掃描和普通資料塊一樣來查詢可用buffer.


P_NAME                                   P_DESCRIPTION                                      P_VALUE                        ISDEFAULT ISMODIFIED ISADJ
---------------------------------------- -------------------------------------------------- ------------------------------ --------- ---------- -----
_small_table_threshold                   lower threshold level of table size for direct rea89                             TRUE      FALSE    FALSE
                                         ds
這裡的89是BLOCK數量，表所使用的BLOCK的數量--不是直接的MB或者KB。。
_small_table_threshold的值在資料庫啟動的時候自動配置成BUFFER數量的2%。--可以修改buffer cache大小並重啟資料庫驗證。
SYS@ bys3>select count(*)*0.02 from x$bh;   ---從X$BH中獲取BUFFER的數量
COUNT(*)*0.02
-------------
        88.98

關於直接路徑讀的說明： ---來自百度

直接路徑讀（direct path read）通常發生在Oracle直接讀資料到程式PGA時，這個讀取不需要經過SGA。直接路徑讀等待事件的3個引數分別是file number（指絕對檔案號）、first dba、block cnt數量。在Oracle 10g/11g中，這個等待事件被歸於User I/O一類。db file sequential read、db file scattered read、direct path read 是常見的集中資料讀方式。

在資料倉儲環境大量的direct path read是正常的。在OLTP中，大量direct path read意味應用有問題導致大量磁碟排序讀取操作。

最為常見的是第一種情況。在DSS系統中，存在大量的direct path read是很正常的，但是在OLTP系統中，通常顯著的直接路徑讀（direct path read）都意味著系統應用存在問題，從而導致大量的磁碟排序讀取操作。直接路徑寫（direct paht write）通常發生在Oracle直接從PGA寫資料到資料檔案或臨時檔案，這個寫操作可以繞過SGA。直接路徑寫等待事件的3個引數分別是：file number（指絕對檔案號）、first dba和block cnt數量，在Oracle 10g/11g中，這個等待事件同direct path read一樣被歸於User I/O一類。這類寫入操作通常在以下情況被使用：·直接路徑載入；·並行DML操作；·磁碟排序；·對未快取的“LOB”段的寫入，隨後會記錄為direct path write(lob)等待。最為常見的直接路徑寫，多數因為磁碟排序導致。對於這一寫入等待，我們應該找到I/O操作最為頻繁的資料檔案（如果有過多的排序操作，很有可能就是臨時檔案），分散負載，加快其寫入操作。

直接路徑插入時，不產生表塊的回滾資訊，而是依賴高水位點實現回滾
但是，如果表有索引，將會產生索引的回滾資訊，而且索引的塊會被讀進buffer cache
Oracle官方文件建議，如果使用直接路徑插入，向表中傳送大量資料，可先將表上的索引刪掉，插入結束後，再重新建立索引

在Oracle 11g版本中序列的全表掃描可能使用直接路徑讀取(direct path read)的方式取代之前版本中一直使用的DB FILE SCATTERED READ, 顯然direct path read具備更多的優勢：

1. 減少了對latch爭用

2.物理IO的大小不再取決於buffer_cache中所存在的塊；
試想某個8個塊的extent中1,3,5,7號塊在快取記憶體中，而2,4,6,8塊沒有被快取，傳統的方式在讀取該extent時將會是對2,4,6,8塊進行4次db file sequential read,其效率往往要比單次讀取這個區間的所有8個塊還要低得多，
而direct path read則可以完全避免這類問題，儘可能地單次讀入更多的物理塊。

當然直接路徑讀取也會引入一些缺點：
1.在直接路徑讀取某段前需要對該物件進行一次段級的檢查點(A segment checkpoint).
2.可能導致重複的延遲塊清除操作
http://www.oracledatabase12g.com/archives/direct-read-impact-on-delayed-block-read.html