閃回區空間不足引發的SQL問題分析
有一天上班的時候,收到一封報警郵件。
ZABBIX-監控系統:
------------------------------------
報警內容: archive_area_usage
------------------------------------
報警級別: PROBLEM
------------------------------------
監控專案: archive_area_usage:ARCHIVED
LOG-->70.25-->
------------------------------------
報警時間:2016.09.20-08:52:47
碰到這個問題,讓我有些奇怪。
現在伺服器端都有預設的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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle資料庫閃回區空間不足Oracle資料庫
- system表空間不足的問題分析
- system表空間不足的問題分析(二)
- 閃回區報警引發的效能問題分析(r11筆記第11天)筆記
- swap空間不足問題解決
- sysaux 表空間不足問題處理UX
- 歸檔目錄空間不足造成的問題
- rac使用預設閃回區歸檔空間滿
- 系統臨時表空間不足問題
- 歸檔放在閃回區帶來的問題
- 如何解決 Linux 中“磁碟空間不足”的問題Linux
- SQL Server 2000設定了資料庫的自動增長,硬碟空間也沒問題,但還是遭遇空間不足問題SQLServer資料庫硬碟
- 巧用閃回查詢來分析事務延遲的問題
- SQL Server Profiler(P)導致C盤空間不足SQLServer
- 閃回資料庫測試之一 :關閉閃回的表空間是否可以開啟資料庫
- 一個關於資料庫閃回區問題的處理資料庫
- db2解決load後系統空間不足問題DB2
- 歸檔日誌物理刪除後閃回恢復區空間未釋放
- ASM磁碟組空間不足ASM
- 週末又一次歸檔空間不足問題處理
- 清空 oracle 閃回區Oracle
- [Linux日記]解決Ubuntu升級出現/boot空間不足問題LinuxUbuntuboot
- sql server datediff函式引發的效能問題SQLServer函式
- SQL0286N.DB2表空間的pagesize問題SQLDB2
- Ubuntu空間不足,如何擴容Ubuntu
- imp中的indexfile引數解決imp指定表空間問題Index
- 閃回查詢(undo sql)SQL
- Oracle db_recovery_file_dest空間不足報ora-00257問題解決Oracle
- Oracle -- 閃回恢復區---實踐1---閃回庫Oracle
- Oracle閃回恢復區Oracle
- (f)--閃回恢復區---實踐2---閃回表(閃回DML部分資料會用到閃回查詢)
- 閃回區滿的處理 - flashback
- 優化案例--重建索引引發的sql效能問題優化索引SQL
- 執行時間在1秒以下的SQL同樣也會引發效能問題SQL
- boot分割槽剩餘空間不足boot
- UNDO表空間不足解決方法
- 使用mod對資料進行進行分組解決TEMP表空間不足的問題
- 閃回與歸檔引數的設定