閃回區報警引發的效能問題分析(r11筆記第11天)

自從有了Zabbix+Orabbix,很多監控都有了一種可控的方式，當然對於報警處理來說，報警是表象，很可能透過表象暴露出來的是一些更深層次的問題。這不又來一個，不看不知道，一看讓自己著實嚇了一跳。

首先是一個報警資訊，可以看到是閃回區超過了報警的閾值，為了儘可能提前發現問題，我把閾值設定為了70%，和Oracle預設的80%有一些差別。

ZABBIX-監控系統:
------------------------------------
報警內容: archive_area_usage
------------------------------------
報警級別: PROBLEM
------------------------------------
監控專案: archive_area_usage：ARCHIVEDLOG-->73.5-->
------------------------------------
報警時間:2016.12.13-06:53:15這個問題可以使用指令碼，而且之前確實已經用指令碼分析過了。感興趣移步這裡。閃回區空間不足引發的SQL問題分析（r10筆記第32天）

不過我們換一種全新的解讀方式，就是透過圖表來看，基本也能夠定位出問題的方向。

這是一個當天抓取到的閃回區使用率的圖表，可以看到閃回區在早上的時間段使用率攀升。

那麼這個問題該怎麼進一步解讀呢，我們可以看看是否是一個週期性的問題，下面是一週內的閃回區使用對比圖，那麼從我這邊得到的訊息是近期也沒有其它的應用變更，這個圖表看起來就不大正常了，似乎沒有什麼特定的規律可循。

而且透過上面的圖表很可能會得出一個錯誤的結論，怎麼理解呢，我們得到一個月的閃回區使用情況，就會發現這種規律來。閃回區的變化其實還是有一定的規律可循的。

好了，到了這裡我就不秀圖了，開始甩開膀子查問題了。

首先得到的是一個近期的歸檔情況，可以看到確實是週期性的，而且歸檔的頻率在問題發生時極高，按照這個頻率，每個日誌成員1G的大小，這得切換近300次。這個量級確實太驚人了。

如此多的歸檔，充分說明存在大量的資料變化，而這類操作update的可能性最高，而且具有周期性，那麼狠可能是JOB相關。當然這個問題肯定會使得DB time升高。

所以我們繼續拿指令碼得到下面的資料，就是檢視問題時間段內的快照資料，看看哪些SQL佔用了大量的DB time

   SNAP_ID SQL_ID        EXECUTIONS_DELTA ELAPSED_TI PER_TOTAL
---------- ------------- ---------------- ---------- ----------
     78491 75ubgcf0pdrkr                0 1802s      40%
     78491 882jz57wm9cj7                0 1802s      40%
     78491 0cn4gzcn4j0fb                1 161s       3%
     78491 cq4a88vu6232h                1 155s       3%
     78491 79zffzf7unqdm                1 129s       2%

可以看到前2個語句執行時間有些長了。這是一個半小時的快照，兩個語句竟然一直在執行。

當然拿到語句也全然不費功夫，就是下面的語句。

第1個是可以看出是一個儲存過程。

SQL_FULLTEXT
----------------------------------------------------------------------------------------------------
BEGIN proc_update_cardinfo(); END;

第二個可以看出是一個update語句，兩者簡單關聯一下，發現還是有點聯絡。SQL_FULLTEXT
----------------------------------------------------------------------------------------------------
UPDATE CARDINFO A SET A.MAX_LEVEL = NVL((SELECT USER_CLASS FROM ROLE_CLASS_INFO B WHERE A.GROUPID =
B.GROUP_ID AND B.CN_GUID = A.ROLE_GUID), A.MAX_LEVEL) WHERE DRAWED = 'Y'

印證起來就很容易了，很快定位出這個update語句就是出自這個儲存過程。

這個語句除了看起來稍微有些臃腫外，暫時沒有發現其它的問題。實際上這兩個表資料量分別在億級，千萬級，如此一來就很容易出現效能問題了，當然我就引申出一個方法，那就是不要拘泥於這一個語句來做最佳化而是縱觀這個需求全盤來考慮。

我在紙上看著儲存過程的實現方式畫了一下流程圖，發現比我想象的要複雜的多。表cardinfo上存在多個觸發器，任何的DML都會有一套資料的維護規則，而且這個表的資料變更源頭是來自另外一個資料庫，透過DB Link的方式去取得增量資料，然後在這個基礎上結合當前庫的千萬級大表做更新。

大體的邏輯如下，我使用虛擬碼表示

begin
cursor cur is select * from test@db_link where xxxxxx;
for i in cur loop
merge into cardinfo
using(xxxxxx)
on (xxx.id=xx.id)
when matched then update
when not matched then insert
commit
end loop;
update cardinfo set col2=(xxxx) where drawed='Y';
update cardinfo set col3=(xxxx) where drawed='Y';
end;

當然我感覺到有一些潛在問題，但是還是需要確認，於是和開發同事進行了溝通，首先得和他們說明這個問題，第二我來幫他們看看是否有改進空間，第三他們需要告訴我一些基本的邏輯，所以這個過程開發同學其實還是蠻配合的。

大體的業務邏輯瞭解了下，再看這個實現也能夠基本理解了。當然業務的內容說太細不合適，說太少又不理解，我就簡單舉一個例子，就好比在玩遊戲的時候會有一些獎券之類的東西，你如果抽到了就可以使用，也可以忽略，如果使用那就是一個啟用的過程，啟用之後這些獎券可能會相應提升你的等級，這個儲存過程就是做這個事情的。

這個update語句的執行計劃資訊如下：

可以看出這是一個看起來最佳化空間很大的問題，需要動用大量的最佳化技巧。目前已經在處理的過程中，對於效能問題的驗證和測試，都會有一些值得借鑑的地方。容我好好測試一下，分享出來。