閃回區空間不足引發的SQL問題分析

有一天上班的時候，收到一封報警郵件。
ZABBIX-監控系統:
------------------------------------
報警內容: archive_area_usage
------------------------------------
報警級別: PROBLEM
------------------------------------
監控專案: archive_area_usage：ARCHIVED LOG-->70.25-->
------------------------------------
報警時間:2016.09.20-08:52:47

可以看出是閃回區快滿了，當然我設定了閾值70%，比Oracle預設的80%要更低一些，希望儘可能早的發現這些潛在的問題。
碰到這個問題，讓我有些奇怪。
現在伺服器端都有預設的crontab來設定定期刪除過期的歸檔，怎麼閃回區還會這麼快就滿了呢。這類問題的原因相對來說複雜一些，如果說從資料庫層面來看，如果在10gR2的版本中，可能出現這種情況，那就是有些命令的相容性問題導致，如果是系統層面可能就是就是儲存路徑失效，比如nfs掛載點失效等導致。
目前這個資料庫是11gR2，儲存都是本地磁碟。
我們來看看crontab的設定，可以看出是每個小時會執行，觸發的頻率較高，如果每天觸發一次，如果存在這個問題可能還能理解，為什麼在這種頻率下刪除歸檔依舊閃回區空間不足？
$ crontab -l
*/50 * * * *  . $HOME/.bash_profile;$HOME/dbadmin/scripts/rman_trun_arch.sh
我們來看看指令碼的內容。我貼出關鍵的部分。
可以看出歸檔的刪除過期歸檔，保留時間是10個小時之內，其實已經算是很短的了。保留近半天的歸檔而已。
rman target / <<EOF
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog until time "sysdate-10/24";
exit
EOF
如此頻率下怎麼還會有這類問題。看看當前閃回區的情況。

可以看到已經存在300多個歸檔。
這問題確實有意思了，有大量的歸檔，有頻繁的刪除策略，但是閃回區還報錯。
我們來換個姿勢看這個問題，就是檢視歸檔頻率。

這個指令碼的強大的之處就在於可以檢視近2周的歸檔頻率，通過這種方式就可以看出這個問題其實是一個週期性的。在週二會定期出現，只是之前沒有引起重視而已。
可以看到每個小時的歸檔頻率極高，按照這種情況，6個小時就會積累300多個歸檔，一個歸檔日誌成員是1G來算，那麼這個歸檔量就很大了。
一個統計庫怎麼這麼忙，這是一個問題，我們來看看資料庫的負載情況。


可以看到在早間的時候資料庫的負載還是有很大的提升。
那麼這個時間段內是否有SQL引起的如此的變化，比如一個AWR報告，比如一個指令碼就能夠定位。
當然抓到罪魁禍首是關鍵，我是使用指令碼來做，抓到了下面的語句。發現了不少負載高的查詢語句。

進一步定位，發現都有千絲萬縷的關鍵，那就是其中一個儲存過程呼叫，會呼叫裡面的一些SQL語句。
最終發現SQL語句是這樣的形式
SQL_FULLTEXT
----------------------------------------------------------------------------------------------------
UPDATE TESTINFO 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'

看這個語句其實邏輯也不復雜，但是如果檢視資料量就會發現這個工作量真是太大了，兩個表都是億級的資料量。

按照過濾條件，資料量2億，過濾得到4千萬，都不是小數目，所以全表看來也是一種方案。
SQL> select DRAWED,count(*)from test.testinfo group by DRAWED;
D   COUNT(*)
- ----------
Y   43807108
N  216762221
Elapsed: 00:00:36.17  
但是顯然這裡還是存在一些需要確認的地方，這個語句本該不需執行，至少不應該在統計層面來保證資料的業務邏輯一致性，應該在OLTP系統中就應該保證，所以我的努力方向就是取消這個JOB，這種優化才是最有效的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-2125524/，如需轉載，請註明出處，否則將追究法律責任。