資料庫中大量對錶smon_scn_time的delete操作

charsi發表於2016-05-15
資料庫delete
前段時間遇到一個問題，資料庫中對錶smon_scn_time有大量的delete操作，消耗了很高的CPU和記憶體資源。

問題現象：
從AWR報告中很明確會看到下面這個語句的資源消耗較高：
delete from smon_scn_time where thread=0 and time_mp = (select min(time_mp) from smon_scn_time where thread=0);
注意，上面這條語句是10.2.0.1 －10.2.0.4版本中的。
在10.2.0.5版本中的語句是下面的，有點不一樣
delete from smon_scn_time where thread=0 and scn= (select min(scn) from smon_scn_time where thread=0);

問題原因分析：
參考官方文件：
High Executions Of Statement "delete from smon_scn_time..." (文件 ID 375401.1)
smon程式會定期清理smon_scn_time表中的資料，以便釋放空間。正常情況下smon程式會每5分鐘檢查一次，如果這個表中的記錄數正常不會超過144000條。每次smon程式會清理一條。如果一次清理不能釋放足夠的空間，smon會執行多次知道釋放足夠空間為止。

上面這條語句的含義是查詢scn（或者time_mp）最小的那條記錄，然後把這條記錄從smon_scn_time表中刪除，正常情況下，每次肯定會有一條記錄被刪除。而在這個問題中，我們手工執行該條delete SQL，會發現沒有記錄被刪掉。很明顯有問題。

按照官方文件提示的執行
analyze table smon_scn_time validate structure cascade;

並沒有報出提示的錯誤，而是報資源忙。

但如果使用hint跳過索引去查詢和執行的話，這條記錄是存在的。
也就是說，確實如mos所說，表和索引的資料發生了不一致。


解決方法：
索引和表中的資料不一致，只能重建索引。參考mos提供的方法：
drop index smon_scn_time_scn_idx;
drop index smon_scn_time_tim_idx;
create unique index smon_scn_time_scn_idx on smon_scn_time(scn);
create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp);
analyze table smon_scn_time validate structure cascade;

但是也會碰到一個比較頭疼的問題，刪除和建立索引的時候，一致提示資源忙。

參考LOCK ON SYS.SMON_SCN_TIME (文件 ID 747745.1)設定12500事件也不行。

正好處理的時間是檢修時間，可以重啟資料庫。因此重啟資料庫，在open資料庫的時候，第一時間執行drop和create索引。重啟了幾次資料庫，做這同樣的操作才最終把問題處理完成。

同時也有個奇怪的現象。如果在資料庫mount的狀態下設定12500事件的話，當資料庫open的時候，表smon_scn_time就會找不到。只能重新啟動資料庫才行。不知道這個是10g中在mount狀態時desc檢視的話，就會產生物件在buffer中狀態異常的bug，還是因為12500事件的原因。

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

相關文章