大資料量刪除的思考（三）

在本系列的上一部分中，我們研究了在大資料刪除表和索引和案例。在這一部分中，我們將繼續討論不同案例所隱含的工作量，然後考慮刪除策略，或者需要制定減少工作量的策略。

基本開銷部分

除了時間本身，我們還要關注其它三個指標，即大資料刪除的過程中：生成的undo和生成的redo以及發生的I/ O量。這些因素並不是相互獨立的，這在優化刪除的一般性討論中引入了很多複雜性，並且刪除操作本身可能會影響同時執行的其他會話，並且同時其他會話也可能影響大資料刪除。

 Redo  ： 對每個資料塊的更改都會生成redo，描述如何修改塊以及幾十個位元組的開銷：如果表上有四個索引，那麼可以認為是刪除的每一行更改5個塊（一個表塊，四個索引葉塊）。

 Undo  ：每次刪除行時，通過在undo段中寫入如何重新插入該行的說明以及如何“撤銷刪除”索引條目來“保留”該行。每個undo條目都有幾十個位元組的開銷，每個undo條目都是對資料塊的更改，因此生成更多redo，結合兩個重做源，單行刪除和四個受影響的索引可以產生大約1KB的重做，加上刪除的行和索引條目的大小。

  I / O  ：將資料塊讀入緩衝區快取中以刪除一行，同時必須讀入緩衝區快取每個受刪除行影響的索引葉塊。在某個階段，oracle必須構造出已更改的資料（和索引）塊，其中包括一直在更改的undo塊，這個時不得不面對的等待，但幸運的是，寫入可能會在後臺處理，並且可能不會影響刪除的速率; 但另一方面，對於一個非常大的刪除，我們可能會發現生成的undo量是如此之大，並且表和索引的讀取如此隨機，以至於資料庫不斷的重新整理buffer cache進而導致寫入極為緩慢。

  併發 (1)   ：即使只有一個只讀會話，undo和redo日誌也不會停止產生日誌，如果其他會話必須檢查已修改（但尚未提交）的塊，那麼必須使用undo塊來檢查提交時間並生成已更改的塊的讀取一致版本，以便可以看到我們要刪除的行。這有兩個影響，第一其他會話最終可能會物理讀取undo塊（增加I / O負載），然後強制資料庫重新整理buffer寫入磁碟，以使buffer可用於它們要建立的讀取一致性副本（進一步增加I/ O負載），第二，如果另一個會話必須從磁碟讀取我們已經更改的塊，那麼它將要做的第一件事就是準備應用 ”延遲塊 清除”對於它將在那裡找到的未提交的更改（即將的刪除的資料）, 即使發現它不需要進行清除oracle仍然會生成60個位元組的undo，並且每次從盤中讀取這樣的塊，讀取會話將生成另外60個位元組，直到我們最終提交併且下一個讀取塊的會話執行“適當的”延遲塊清除。大資料的刪除執行執行時間越長速度越慢，這些併發效應可能是其中一個重要原因。

  併發 (2)   ：那麼併發性問題可能會更嚴重——甚至忽略了另一個會話可能正在鎖定我們要刪除的行的可能性，並讓會話等待TX鎖。即使沒有其他會話更改我們嘗試刪除的任何行，它們也可能更改與要刪除的行共享塊的行，並且我們必須建立這些塊的讀取一致性副本，以檢查開始刪除時當前是否存在資料，以及當我們開始的時候就在那裡的資料還沒有消失——我們需要做一個“寫一致性”刪除：最好這意味著 我們可以做大量的工作檢查，最壞的情況下，這意味著我們可能會發現我們的會話發現一個它無法處理的不一致，然後回滾並重新啟動自動刪除。後者可能有點罕見，前者是導致大型刪除在執行過程中變得越來越慢的兩個關鍵原因之一。

一些有趣的資料

在上一部分中，我們提供了一些程式碼來建立一個包含四個索引的表，以作為一些思想實驗的基礎，現在使用該資料來生成一些效能資料。在我這樣做之前，值得一提的是優化器如何計算刪除的成本，其實非常簡單：成本是等效“selectrowid from ...”查詢的成本，用於標識要刪除的行的rowid。優化器不允許訪問表的成本刪除行，也不允許維護索引的成本。

在一個簡單的刪除案例，如 “deletefrom t1 where client_ref < ‘F’” 這意味著oracle將會在三個執行計劃中選擇一個，可能是全表掃描t1和索引掃描client_ref或者採用索引快速全掃描client_ref，實際上，直達12c優化器才會選擇indexfast full scan，這一點可以說是oracle的一個bug，直到12.1.0.2生版本才被修復。

下面我們看看兩個刪除語句的幾個數字：第一個將刪除date_open超過五年前的所有行（即最舊的50％的資料），第二個將刪除帶有引用的客戶端以字母A到E開頭的程式碼（小於20％的資料）。

delete from t1
where date_open < add_months(sysdate, -60);
5000000 rows deleted.

我們假設六種不同的場景，當沒有索引時，從表中刪除，第二個場景只有主鍵索引，第三個場景中有主鍵和client_ref索引，在所有這三種情況下，刪除將遵循完整的表掃描。

最後三個場景將包含所有四個索引（主鍵，date_open，date_closed和client_ref）; 第一個方案將使用表掃描，第二個將使用索引快速全掃描的date_open索引-在預設情況下出現的，事實上，與12C的路徑-最後將使用索引範圍掃描的date_open索引。

從v $ sesstat檢視我們可以看到redo條目的數量，redo大小和undo更改向量大小，每次刪除的實際執行時間，這裡需要注意的是，執行時間對於一般情況來說並不是一個好的指標，因為假如使用的是固態磁碟，所以任何I/ O都會非常快，而且執行時可以與訪問和修改的塊數相比，緩衝區快取的大小受到的影響最大。

另外一點需要明確說明 - 在每次測試之後都使用drop table purge方式並重新建立表，因此每次的測試結果與先前的測試是無關的，具體測試結果如下：

從前三個結果可以看出，隨著索引數量的增量，redo和undo以及時間都是隨索引數量增加的。

從後面三種測試結果可以看出，tablescan或索引快速全掃描刪除的情況 - 儘管資源使用有微小的變化，兩者之間的時間差異很小。最後，當我們通過date_open上的索引驅動刪除時，我們得到了一個非凡的變化，重做條目的數量急劇下降 - 幾乎回到“無索引”刪除 - GB中的撤消和重做大小以及刪除時間半，發生了什麼變化？

在這裡Oracle使用一種完全不同的策略，對於tablescan / index快速完整掃描，Oracle從表中刪除一行，然後依次更新每個索引，然後再繼續刪除下一行。但在這裡，對於索引範圍掃描/完全掃描，Oracle刪除錶行只記錄需要更新的每個索引的rowid和鍵值 - 然後繼續到下一行而不更新任何索引。當表刪除完成時，Oracle按索引按鍵排序它所累積的所有資料，並使用批量更新索引進行延遲維護。由於批量更新導致每個block一個undo記錄和一個redo更改向量更新（而不是每行更新一次）redo條目的數量可以顯著下降，。在我們的示例中，我們先是儲存了每個索引22,000個塊（22,000個撤消/重做條目）然後進行集中更新，而不是每個索引500萬行。

但是有一對重要的統計資料僅出現在範圍掃描的刪除中：

        sorts(disk)                              4

        sorts(rows)                     20,017,391

排序的20M行是5M行x4個索引。隨著記憶體大小調整排序溢位到磁碟，因此，可以選擇執行大型刪除的機制，並選擇何時使用可能會顯著影響工作負載出現位置以及影響程度的資源。

事實上存在一個變化，我的第一個例子顯示了索引驅動方法的巨大好處，並不意味著通過索引驅動是最好的事情。您仍然需要考慮資料表架構。在的第二個例子 -包含所有四個索引並顯示三個可能的執行計劃（ablescan,index range scan, index fast full scan），在這種情況下，對索引的資料再次執行刪除操作，資料在表中均勻分佈，而不是按順序排列在表的開頭，刪除的資料行大約在1.9M：

delete from t1 where client_ref < 'F'

我們再次看到通過適當的索引驅動刪除導致redo條目的最小數量和最小undo大小; 然而Tablescan儘管產生了大量更多的undo和redo，但是可以在不到一半的時間內執行; 索引快速全掃描，儘管生成幾乎相同的undo和redo資料量，但速度比索引範圍掃描慢近3.4倍。

要理解為什麼這個刪除的最佳（以時間為中心）策略與前一個刪除的最佳策略相反，我們需要找到時間花在哪裡，如果我們想要更多細節，可以通過（v $ segstat / v $segment_statistics）進行統計查詢：

Rangescan:       1,275M db file sequential read(80 seconds)
Fast fullscan:   2,860M db file sequential read (369 seconds)
Tablescan:          95K db file sequential read, 1600 db file scattered read (total 18seconds)

檢查物理I / O發生的位置（哪些段）有助於我們瞭解發生了什麼，當我們通過索引範圍掃描時，我們可以非常有效地延遲維護4個索引，但是client_ref值範圍的資料模式意味著我們不斷跳過表，不斷重新整理記憶體然後重新啟動，讀取我們需要更新的塊。 在1.275M單塊讀取中，1.19M是表中的塊 -表中只有204,000塊; 我們已經讀了五次每個塊（對於從'A'到'E'的每個字母有效一次），當我們做一個快速全掃描上的索引 ，我們有同樣的問題加上我們在同一時間更新索引一行和其他三個指標跳來跳去，在一個相當隨機的方式，這意味著我們最終會有更少的緩衝區來保持表的快取，並在表上執行更多的重新讀取，以及重新讀取索引塊。 快速全掃描方法的2.86M讀取包括來自表的1.75M讀取，來自主鍵的274K讀取和來自兩個基於日期的索引的430K讀取，而我們正在使用的client_ref索引僅讀取25,000個讀取驅動索引快速全掃描。

當我們執行tablescan時，我們只讀取一次每個表塊，沒有跳轉和重新讀取，並順利地遍歷兩個索引（主鍵和date_open），同時在date_closed索引上稍微隨機跳轉並關注client_ref索引的一小部分（5/26），因此可以很方便地保持快取記憶體。 索引上的隨機讀取次數分別為22K，26K，40K和4.5K。

我們所看到的只是我們對索引和舊問題的思考方式的另一個微小變化： “為什麼Oracle不使用我的索引？ ”。 每當我們檢視一個索引時，我們會問兩個問題： “我們將訪問多少資料？ ”，“資料的分散程度如何？ ”使用索引或使用表掃描來驅動刪除之間的選擇實際上只是另一個變體。 這是優化器在決定如何從表中選擇資料時必須做出的選擇。

方案的選擇

最終我們傾向於有兩個選擇，我們是通過索引範圍掃描還是通過tablescan刪除。 （對於更復雜的刪除，選擇可能會消失，簡單的索引範圍掃描，具有微小的變化，似乎是唯一的特殊情況）。 需要依次考慮兩個問題：

第一： 如果我使用tablescan，我該如何繞過這個索引，從這個索引的快取中獲得多少好處。 此索引是否對隨機I/O構成威脅，因此在執行刪除操作時需要考慮刪除（或禁用）它。 如果刪除這個索引（可能還有其他一些索引），那麼在刪除時，其餘的索引將保持快取狀態。

第二： 如果使用這個索引來驅動刪除，該如何在表中跳轉，將從延遲索引維護功能中獲得多少好處，有多少表的隨機I/O和表塊快取命中失敗。 隨機表I/O帶來的威脅是可以忽略的，因為redo日誌記錄、redo日誌歸檔等的減少。 要做多少工作來排序相關的索引項，這些索引項最終將被刪除。

正如我們經常看到的那樣，做出最佳選擇並不總是那麼容易，但最終可以做出選擇。 作為一個非常初步的指導原則 -如果您可以通過其後面具有良好聚簇資料的索引來驅動您的刪除，那麼這可能是索引路徑將比tablescan路徑更好的選擇。 但是，與所有指南一樣，還有其他需要考慮的陷阱 - 我們將在以後的文章中尋找這些陷阱。

有一個特別重要但簡單的細節你應該記住，當你升級到12c時，優化器有一個簡單刪的第三種訪問路徑選擇 index fast full scan，這條路徑可能比目前發生的任何事情都要糟糕，但優化器會選擇它，因為它是 “select rowid from table…”.的成本最低的訪問路徑。  

原作者:  Jonathan Lewis

原文地址： https://www.red-gate.com/simple-talk/sql/oracle/massive-deletes-part-3/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2658985/，如需轉載，請註明出處，否則將追究法律責任。