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