<form id="tznrh"><form id="tznrh"><th id="tznrh"></th></form></form>


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


            基本開銷部分

            除了時間本身,我們還要關注其它三個指標,即大數據刪除的過程中:生成的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/

            | 譯者簡介

            王顯偉·沃趣科技高級數據庫工程師

            沃趣科技高級數據庫工程師,專注ORACLE數據庫八年,精通Oracle體系結構、豐富的數據庫故障診斷、性能調優、OWI、數據庫備份恢復及數據遷移經驗。







            沃趣科技,讓客戶用上更好的數據庫技術!
            三分快3