（題圖來自 VP , Sally Piao的攝影佳作，感謝攝影師授權）
編輯手記:在理解技術細節時，我們不僅應該讀懂概念，還要能夠通過測試驗證細節，理解那些『功夫在詩外』的部分，例如全表掃描和單塊讀。
開發人員在進行新系統上線前的數據校驗測試時，發現一條手工執行的 SQL 執行了超過1小時還沒有返回結果。SQL 很簡單：
下面是這條 SQL 的真實的執行計劃：
很顯然，在這個表上建 和 的復合索引，這條 SQL 就能很快執行完（實際上最后也建了索引）。但是這里我們要探討的是，為什么這么一條簡單的 SQL 語句，執行了超過1小時還沒有結果。 這張表的大小約為 12GB ，以系統的 IO 能力，正常情況下不會執行這么長的時間。簡單地看了一下，系統的 CPU 以及 IO 壓力都不高。假設單進程全表掃描表，每秒掃描 50MB 大小（這實際上是一個很保守的掃描速度了），那么只需要245秒就可以完成掃描。
下面來診斷一下 SQL 為什么會這么不正常地慢。看看會話的等待（以下會用到 大牛 Tanel Poder的腳本）：
明明是全表掃描的 SQL ，為什么99%以上的等待時間是 db file read ，即單塊讀？！多執行幾次 腳本，得到的結果是一致的（注意這里的數據，特別是平均等待時間并不一定是準確的值，這里重點關注的是等待時間的分布）。
那么 SQL 執行計劃為全表掃描（或索引快速全掃描）的時候，在運行時會有哪些情況實際上是單塊讀？我目前能想到的有：
那么在這條 SQL 語句產生的大量單塊讀，又是屬于什么情況呢？我們來看看單塊讀更細節的情況：
多次執行同樣的 SQL ，發現絕大部分的單塊讀發生在3、353-355這四個文件上，我們來看看這4個文件是什么：
原來是 UNDO 表空間。那么另一個疑問就會來了，為什么在 UNDO 上產生了如此之多的單塊讀？首先要肯定的是，這條簡單的查詢語句，是進行的一致性讀。那么在進行一致性讀的過程中，會有兩個動作會涉及到讀 UNDO 塊，延遲塊清除和構建 CR 塊。下面我們用另一個腳本來查看會話當時的狀況：
上面的結果是5秒左右的會話采樣數據。再一次提醒，涉及到時間，特別要精確到毫秒的，不一定很精確，我們主要是看數據之間的對比。從上面的數據來看，會話請求了382次 IO 請求，單塊讀和多塊讀一共耗時4219.17ms（4.17s+49.17ms），平均每次 IO 耗時 11ms。這個單次 IO 速度對這套系統的要求來說相對較慢，但也不是慢得很離譜。 data reads - undo 這個統計值表示進行一致性讀時，回滾的 UNDO 記錄條數。
比這個統計值可以很明顯地看出，這條 SQL 在執行時，為了得到一致性讀，產生了大量的 UNDO 記錄回滾。那么很顯然，在這條 SQL 語句開始執行的時候，表上有很大的事務還沒有提交。當然還有另一種可能是 SQL 在執行之后有新的很大的事務（不過這種可能性較小一些，因為那樣的話這條 SQL 可能比較快就執行完了）。
詢問發測試的人員，稱沒有什么大事務運行過，耳聽為虛，眼見為實：
這張表目前沒有事務，但是曾經 了超過1.6億條記錄。最后一次 DML 的時間正是這條執行很慢的 SQL 開始運行之后的時間（這里不能說明最后一次事務量很大，也不能說明最后一次修改對 SQL 造成了很大影響，但是這里證明了這張表最近的確是修改過索引可以避免全表掃描，并不是像測試人員說的那樣沒有修改過）。
實際上對于這張表要做的操作，我之前是類似的表上是有看過的。這張表的總行數有上億條，而這張表由于進行數據的人工處理，需要 掉絕大部分的行， 時使用并行處理。那么這個問題到，從時間順序上來講，應該如下：
在表上有很大的事務，但是還沒有提交。
問題 SQL 開始執行查詢。
事務提交。
在檢查 SQL 性能問題時，表上已經沒有事務。
由于 量很大，那么 UNDO 占用的空間也很大，但是可能由于其他活動的影響，很多 UNDO 塊已經刷出內存，這樣在問題 SQL 執行時，大量的塊需要將塊回滾到之前的狀態（雖然事務開始于查詢 SQL ，但是是在查詢 SQL 開始之后才提交的，一致性讀的 SCN 比較是根據 SQL 開始的 SCN 與事務提交 SCN 比較的，而不是跟事務的開始 SCN 比較），這樣需要訪問到大量的 UNDO 塊，但是 UNDO 塊很多已經不在內存中，就不得不從磁盤讀入。
對于大事務，特別是更新或 數千萬記錄的大事務，在生產系統上盡量避免單條 SQL 一次性做。這造成的影響特別大，比如：
那么，現在我們可以知道，全表掃描過程還會產生單塊讀的情況有，讀 UNDO 塊。
對于這條 SQL ，要解決其速度慢的問題，有兩種方案：
在表上建個索引，如果類似的 SQL 還要多次執行，這是最佳方案。
取消 SQL ，重新執行。因為已經沒有事務在運行，重新執行只是會產生事務清除，但不會回滾 UNDO 記錄來構建一致性讀塊。
繼續回到問題，從統計數據來看：
問題到這兒，產生了一個疑問，就是單塊讀較多（超過70），因此可以推測，平均每個 undo 塊只回滾了不到2條的 undo 記錄，同時同一數據塊上各行對應的 undo 記錄很分散，分散到了多個 undo 塊中，通常應該是聚集在同一個塊或相鄰塊中，這一點非常奇怪，不過現在已經沒有這個環境（undo 塊已經被其他事務重用），不能繼續深入地分析這個問題，就留著一個疑問，歡迎探討（一個可能的解釋是塊是由多個并發事務修改的，對于這個案例，不會是這種情況，因為在數據塊的 dump 中沒有過多 ITL，另外更不太可能是一個塊更新了多次，因為表實在很大，在短時間內不可能在表上發生很多次這樣的大事務）。
在最后，我特別要提到索引可以避免全表掃描，在生產系統上，特別是 OLTP 類型的系統上，盡量避免大事務。
如何加入"云和恩墨大講堂"微信群