為什麼忘記commit也會造成select查詢的性能問題

今天遇到一個很有意思的問題，一個開發人員回報在測試伺服器ORACLE資料庫執行的一條簡單SQL語句非常緩慢，他寫的一個SQL沒有傳回任何資料，但是耗費了幾分鐘的時間。讓我檢查分析一下原因，分析解決過後，發現事情的真相有點讓人哭笑不得，但是也是非常有意思的。我們先簡單構造一下類似的案例，當然隻是簡單模拟。

假設一個同僚A，建立了一個表并初始化了資料（實際環境資料量較大，有1G多的資料），但是他忘記送出了。我們簡單模拟如下：

<a href="http://images2015.cnblogs.com/blog/73542/201609/73542-20160902235300011-1308615099.png"></a>

另外一個同僚B對這個表做一些簡單查詢操作，但是他不知道同僚A的沒有送出INSERT語句，如下所示，查詢時間用了大概5秒多（這個因為構造的資料量不是非常大的緣故。實際場景耗費了幾分鐘）

<a href="http://images2015.cnblogs.com/blog/73542/201609/73542-20160902235301293-1074256195.png"></a>

當時是在SQL Developer工具裡面分析SQL的執行計劃，并沒有注意到redo size非常大的情況。剛開始懷疑是統計資訊不準确導緻，手工收集了一下該表的統計資訊，執行的時間和執行計劃依然如此，沒有任何變化。 如果我們使用SQL*Plus，檢視執行計劃，就會看到redo size異常大，你就會有所察覺（見後面分析）

因為ORACLE裡面的寫不阻塞讀，是以不可能是因為SQL阻塞的緣故，然後我想檢視這個表到底有多少記錄，結果亮瞎了我的眼睛，記錄數為0，但是空間用掉了852 個資料塊

<a href="http://images2015.cnblogs.com/blog/73542/201609/73542-20160902235302808-939297397.png"></a>

于是我使用Tom大師的show_space腳本檢查、确認該表的空間使用情況，如下所示，該表确實使用852個資料塊。

<a href="http://images2015.cnblogs.com/blog/73542/201609/73542-20160902235304105-1719825706.png"></a>

分析到這裡，那麼肯定是遇到了插入資料操作,卻沒有送出的緣故。用下面腳本檢查發現一個會話ID為883的對這個表有一個ROW級排他鎖，而且會話還有一個事務排他鎖，那麼可以肯定這個會話執行了DML操作，但是沒有送出。

<a href="http://images2015.cnblogs.com/blog/73542/201609/73542-20160902235305465-1703568529.png"></a>

我們在會話裡面送出後，然後重新執行這個SQL，你會發現執行計劃裡面redo size為0，這是因為redo size表示DML生成的redo log的大小，其實從上面的執行計劃分析redo size異常，就應該了解到一個七七八八了，因為一個正常的SELECT查詢是不會在redo log裡面生成相關資訊的。那麼肯定是遇到了DML操作，但是沒有送出。

<a href="http://images2015.cnblogs.com/blog/73542/201609/73542-20160902235306840-1812876669.png"></a>

分析到這裡，我們已經知道事情的前因後果了，解決也很容易，找到那個會話的資訊，然後定位到哪個同僚，讓其送出即可解決。但是，為什麼沒有送出與送出過後的差距那麼大呢？是什麼原因呢？ 我們可以在這個案例，送出前與送出後跟蹤執行的SQL語句，如下所示。

送出前上面SQL生成的跟蹤檔案為scm2_ora_8444.trc，我們使用TKPROF格式化如下： tkprof scm2_ora_8444.trc out_uncommit.txt 如下所示

<a href="http://images2015.cnblogs.com/blog/73542/201609/73542-20160902235308324-214253082.png"></a>

送出後，在另外一個會話執行上面的SQL，然後格式化跟蹤檔案如下所示：

<a href="http://images2015.cnblogs.com/blog/73542/201609/73542-20160902235309746-144495775.png"></a>

我們發現送出前與送出後兩者的實體讀、一緻性讀有較大差别（尤其是一緻性讀相差3倍多）。這個主要是因為ORACLE的一緻性讀需要構造cr塊，産生了大量的邏輯讀的緣故。相關理論與概念如下：

為什麼要一緻性讀,為了保持資料的一緻性。如果一個事務需要修改資料塊中資料，會先在復原段中儲存一份修改前資料和SCN的資料塊，然後再更新Buffer Cache中的資料塊的資料及其SCN，并辨別其為“髒”資料。

當其他程序讀取資料塊時，會先比較資料塊上的SCN和程序自己的SCN。如果資料塊上的SCN小于等于程序本身的SCN，則直接讀取資料塊上的資料；

如果資料塊上的SCN大于程序本身的SCN，則會從復原段中找出修改前的資料塊讀取資料。通常，普通查詢都是一緻性讀。

一緻性讀什麼時候需要cr塊呢，那就是select語句在發現所查詢的時間點對應的scn，與資料塊目前所的scn不一緻的時候。構造cr塊的時候，首先去data buffer中去找包含資料庫前鏡像的undo塊，如果有直接取出建構CR塊，這時候是邏輯讀，産生邏輯IO；但是data buffer将undo資訊寫出後，就沒有需要的undo資訊，就會去undo段找所需要的前鏡像的undo資訊，這時候從磁盤上讀出block到buffer中，這時候産生實體讀(實體IO)