insert導致的效能問題大排查(r11筆記第26天)

今天開發的同學小視窗訊息給我，向我諮詢一個ORA錯誤的問題。

錯誤程式碼是ORA-30036，使用oerr ora 30036檢視，由於是undo空間無法擴充套件導致。

這是一個統計業務的資料庫，而且平時的負載其實並不高，確實有一些奇怪。首先排除了大事務導致的原因，檢視資料庫日誌，和開發同學溝通，沒有發現相關的錯誤資訊。

所以第一感覺這是一個偶然發生的情況，不過開發的這位同學貌似碰到了問題，他說從應用端丟擲了ORA-30036的錯誤。

java.sql.BatchUpdateException:ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'?
問題剛剛發生，問題確鑿，說明插入資料出了問題。但是比較奇怪的是，我在環境中簡單模擬了一下，卻沒有碰到這類問題。把資料量提升到百萬還是可以成功。

和開發的同學做了確認，他發過來了執行失敗的語句，這是一個看起來很簡單的語句,當然我做了簡單的脫敏。

insert into user_hh_max(id, stat_time, appkey) select seq_user_hh.nextval id, :1 stat_time, :2 appkey from dual;

 這樣一個看似非常簡答的INSERT看起來無論如何也不會導致很嚴重的效能問題，這一點我是深信不疑。除了序列的自增外，其它的地方我是真沒看出來有什麼效能隱患。

帶著疑問，我看了下最近的資料庫負載，都在正常的範圍之內。檢視歸檔的切換頻率，發現問題看起來不是那麼簡單。

下面的圖示，橫軸是小時，縱軸是日期，這樣就能夠看到每個小時的歸檔切換情況，發現近些天來歸檔的切換頻率比以前有了極大的提高。簡單來說，以前基本上是一個小時2~3G的歸檔量，現在一下子變成了20~30G,而且還有增加的趨勢。

得到了這樣一個報告，讓我對原本看起來不痛不癢的問題變得嚴峻起來，而且應用端確實有些統計出現了問題，希望我幫忙能先修復一下，這種情況下，我先擴容了Undo空間，然後靜下心來分析這個奇怪的問題。

帶著疑問我得到了一個AWR報告。

檢視profile的部分，資訊如下：

	每秒	每個事務	每次呼叫
DB Time(s):	1.3	0.1	0.00
DB CPU(s):	1.1	0.1	0.00
Redo size:	8,826,942.0	730,730.2
Logical reads:	77,599.7	6,424.0
Block changes:	56,339.2	4,664.0
Physical reads:	33.6	2.8
Physical writes:	564.9	46.8
User calls:	1,686.8	139.6
Parses:	1.5	0.1
Hard parses:	0.4	0.0
W/A MB processed:	0.7	0.1
Logons:	0.0	0.0
Executes:	1,891.1
Rollbacks:	0.0	0.0
Transactions:	12.1

透過這部分內容，可以很明確看到每秒鐘產生了8M左右的redo，在我的經歷中，這是一個很頻繁的資料變化，但是檢視TPS不高，邏輯讀很高。所以我的精力就馬上集中在了SQL部分，看看有哪些DML的操作會導致如此高的消耗。

檢視SQL部分的報告，得到了下面的一個表格。

這裡的insert執行了500多萬次，聽起來其實也不高。我就在想單單是500萬的insert肯定不會造成每小時20~30G的日誌量。那麼還有什麼地方會導致問題呢。看著下面的語句，有一些update還有一連串的merge語句，自己還一度懷疑是否又是merge導致的效能問題，但是根據資料來分析，影響實在是太小了。

所以面對這樣一種情況就很糾結，問題發生但是又很難定位出問題來。

我耐著性子又看了看報告。發現了這樣一小段內容。

這部分內容就很奇怪了，完全不大符合邏輯，insert執行了500多萬次，但是影響的行數是4000多萬行。

檢視其它的指標也沒有找到明顯效能問題。

這個問題該怎麼繼續往下查呢。

我想到了一種方法，既然產生了如此多的歸檔，那就看看到底redo裡面是些什麼內容不就一目瞭然了。使用了多少commit都能看得清清楚楚。

關於Logminer提取redo日誌的資訊，可以參考

Oracle閃回原理-Logminer解讀redo（r11筆記第17天）

使用裡面提供的兩個指令碼，很快就讀出了redo的內容，正是insert語句。

我看到了大量的insert,但簡單統計insert的數目，看起來這個量級和AWR報告中嚴重不符。

我檢視了這個表的資料量，不到100萬，而且對應的資料塊也沒有爆發式增長，這個現象真是奇怪。

此時我陷入了深思，這個問題該怎麼解釋，是AWR報告的bug嗎？

因為這個表的資料量不大，我做了如下的測試，寫了一個指令碼，每隔2秒鐘統計一下這個表的資料量，然後幾分鐘後，拿著得到的資料，得到了下面的一張圖。

如下是這張表的資料量變化圖，可以看到基本上是在1分鐘內，會插入100萬資料，然後馬上清理掉，繼續插入，如此反反覆覆。

毫無疑問，這個邏輯如此看來是有明顯的問題的，經過反覆確認，讓開發的同學去看看這個邏輯的實現，是否哪裡出了問題，很快就得到了反饋，他們發現了問題根源，及時從邏輯上做了調整。

從下面歸檔的切換情況可以看出問題有了立竿見影的效果。

所以由此一來，AWR的顯示的資料有些地方就能夠理解了。當然你也可以認為是報告的資料誤導在先。