本文中根塊，枝塊，葉塊，表塊分別是索引根塊，索引枝塊，索引葉塊，數據表塊的簡稱。 此外本文大多數觀點來自於大師呂海波《Oracle內核技術揭秘》一書，本博文為個人感想。 首先需要明確4點關於CBC LATCH和BUFFER PIN的知識點： 1. 對於根塊和枝塊，CBC LATCH都是以S模式獲取的 ...
本文中根塊，枝塊，葉塊，表塊分別是索引根塊，索引枝塊，索引葉塊，數據表塊的簡稱。 此外本文大多數觀點來自於大師呂海波《Oracle內核技術揭秘》一書，本博文為個人感想。 首先需要明確4點關於CBC LATCH和BUFFER PIN的知識點： 1. 對於根塊和枝塊，CBC LATCH都是以S模式獲取的，無BUFFER PIN 2. 對於葉塊，CBC LATCH是以X模式獲取的，BUFFER PIN是以S模式獲取的 3. 對於表塊，CBC LATCH是以X模式獲取的，SELECT語句的BUFFER PIN是以S模式獲取的，DML語句的BUFFER PIN是以X模式獲取的 4. 以上三點基本正確，但有一個特殊情況，INDEX UNIQUE SCAN的情況下是這樣的： 1）對於根塊、枝塊、葉塊，CBC LATCH都是以S模式獲取的，且都無BUFFER PIN 2）對於表塊，SELECT語句的CBC LATCH是以S模式獲取的，無BUFFER PIN；DML語句的CBC LATCH是以X模式獲取的，BUFFER PIN是以X模式獲取的 現在我們考慮一種最複雜的情況：不走索引唯一掃描但走索引的情況。 此時將CBC LATCH和BUFFER PIN的獲取方式進行分類總結： A 根塊和枝塊：以S模式獲取CBC LATCH-->>讀取索引數據-->>釋放CBC LATCH B 葉塊：以X模式獲取CBC LATCH-->>以S模式獲取BUFFER PIN，釋放CBC LATCH，讀取索引數據-->>以X模式獲取CBC LATCH，釋放BUFFER PIN-->>釋放CBC LATCH C 表塊（SELECT）：以X模式獲取CBC LATCH-->>以S模式獲取BUFFER PIN，釋放CBC LATCH，讀取數據-->>以X模式獲取CBC LATCH，釋放BUFFER PIN-->>釋放CBC LATCH D 表塊(DML)：以X模式獲取CBC LATCH-->>以X模式獲取BUFFER PIN，釋放CBC LATCH，修改數據-->>以X模式獲取CBC LATCH，釋放BUFFER PIN-->>釋放CBC LATCH 那麼對於SELECT和DML語句我們就可以使用以上組合來顯示出其獲取內部鎖和閂的過程： 1.SELECT語句的流程是：ABC 2.DML語句的流程是：ABD 現在我們模擬所有情況下的內部閂鎖爭用：（我們假設最差的情況，SELECT和DML語句都走同樣的索引，查同樣的數據） 一：SELECT語句的併發，即ABC併發ABC A階段全是S模式不會有爭用發生，BC階段獲取X模式CBC LATCH的時間極短（只為修改BUFFER PIN的狀態），BC階段的BUFFER PIN是S模式，也不會引發爭用。（10G之前會） 因此SELECT併發不會引發BUFFER BUSY WAITS 二：SELECT語句和DML語句的併發，即ABC併發ABD A階段全是S模式不會有爭用發生，B階段獲取X模式CBC LATCH的時間極短（只為修改BUFFER PIN的狀態），也不會發生爭用，那麼C和D階段： 如果C在前那麼D貌似會發生等待，但其實會進行構造CR塊的操作，併在獨占CBC LATCH的保護下將當前BUFFER HEADER(簡稱BH)修改為CR塊，克隆出來的塊的BH修改為XCUR的當前塊，不會發生等待。但是如果構造完CR塊完此時又有一個C或D併發過來，由於克隆塊是XCUR塊上邊被加了X模式的BUFFER PIN，那麼就會發生等待，這裡就是BUFFER BUSY WAITS啦。 如果D在前，那麼同樣的BUFFER BUSY WAITS出現，所以說只要在D之後併發了C或者D，都會造成BUFFER BUSY WAITS。 之前在此處有些疑問，為何後來的C併發不會發生一致性讀呢，後來想想D已經加了X模式的BUFFER PIN，一致性讀是需要讀取表塊的ITL槽信息的，這裡C連塊的BUFFER PIN都獲取不到，談何一致性讀。 真正的一致性讀應當指涉及UNDO的操作，因為既然DML發現了BUFFER PIN上的S模式的鎖證明這個塊正在被讀，根本無需一致性讀。 三：DML語句的併發 這裡就很顯然了，ABD的AB階段不會發生明顯爭用，D階段一個會話獲取了X模式的BUFFER PIN之後，另一個必然要等待，而且此時如果再來一個SELECT語句，也會被阻塞，按照事務鎖的機制，SELECT是不會被DML阻塞的，但是這裡並不是事務鎖阻塞而是BUFFER PIN的等待。 總結： 我們知道在資料庫中無論是讀取還是修改一個塊都是極其迅速的，因此即便是以上的併發情況下，由於每個塊的BUFFER PIN鎖持有時間都極短，因此單個塊即便發生BUFFER BUSY WAITS時間也基本可以忽略不計。 除非更改大量的塊導致多個塊發生BUFFER BUSY WAITS等待，因此如果發生了BUFFER BUSY WAITS，只和DML有關，由於DML也會導致索引更新，因此出現BUFFER BUSY WAITS儘量優化DML語句。 而如果發生了CBC LATCH的等待，那就更明顯了，一定是併發過多，甚至和DML無關，只要是個SQL併發太多都會導致CBC LATCH的等待，因為表塊和葉塊都是以獨占模式獲取的CBC LATCH，解決辦法要麼是減小併發，要麼全部優化為走INDEX UNIQUE SCAN，或者通過修改隱含參數_db_block_hash_latches和_db_block_hash_buckets增大CBC LATCH和HASH BUCKET的數量。 但是需要明確的是： BUFFER BUSY WAITS和CBC LATCH的等待其本質都是併發量過大引起，並不推薦以上使用更改隱含參數的方式解決CBC LATCH的爭用。對於BUFFER PIN的爭用DML語句的優化空間也很小，只能嘗試創建唯一索引，因此應當考慮從業務角度降低併發、分散數據。
