ORA-01555問題分析及解決

ORA-01555問題分析及解決

今天開發的同事發給我一個問題，在運行某一個Job的時候拋出了ORA-01555錯誤，希望我們看看從資料庫層面能不能發現什麼。

錯誤記錄檔如下：

Function: EntitySQLCursor::query

Line number: 113

Time: Thu Jul  2 22:52:46 2015

Message text: (PE1-000143) Internal IO Framework Database Error, message ORA-01555: snapshot too old: rollback segment number 22 with name "_SYSSMU22_234950861$" too small, code 1555.

看這個錯誤，似乎是Oracle分配的復原段太小導致的。對於這個問題，因為已經過去了一段時間，所以能夠合理分析的一種途徑就是使用ash.

根據錯誤資訊中的時間戳記，基本定位在了22:52~22:53這一分鐘之內，抓取了一個ash報告。

因為資訊針對性更強，可以很清晰的看到在那一分鐘之內資料庫層面有一些查詢和dml的語句在運行，有些走了全表掃描，有些走了索引掃描。

Top SQL with Top Events

SQL ID	Planhash	Sampled # of Executions	% Activity	Event	% Event	Top Row Source	% RwSrc	SQL Text
fzn01wc5pg2dg	1199754052	2	15.67	CPU + Wait for CPU	11.75	TABLE ACCESS - FULL	11.75	SELECT /*+ ALL_ROWS USE_NL ("A...
				db file sequential read	2.61	TABLE ACCESS - FULL	2.61
				direct path read	1.31	TABLE ACCESS - FULL	1.31
5q2mguqdcrq4a	421773076	1	12.01	db file sequential read	12.01	INDEX - RANGE SCAN	7.05	SELECT RE.L3_NET_START_TIME, R...
a793wrq0q27c5	201265388	1	10.70	db file sequential read	8.09	DELETE	8.09	delete from RATED_EVENT WHERE ...
				CPU + Wait for CPU	1.57	DELETE	1.57
				direct path read temp	1.04	DELETE	1.04
496x3fkydc1xj	84305990	1	9.92	db file sequential read	8.62	INDEX - RANGE SCAN	8.62	** SQL Text Not Available **
				CPU + Wait for CPU	1.31	INDEX - RANGE SCAN	1.31
dm1d93bw2jdzc	2843169790	27	8.09	db file sequential read	4.70	INDEX - RANGE SCAN	2.09	select sk.rowid , sk.subscribe...
				CPU + Wait for CPU	3.39	SELECT STATEMENT	2.35

需要重點關注的是全表掃描的語句和DML語句。

先來看看全表掃描的語句。

SELECT /*+ ALL_ROWS USE_NL ("AC1_CONTROL_HIST") FULL ("AC1_CONTROL_HIST") */ ....  from  "AC1_CONTROL_HIST" WHERE "CUR_PGM_NAME"='RGD' AND "IDENTIFIER"=:1

語句輸出欄位較多，但是相關的表只有一個，這個表從表名可以看出是一個曆史表，資料量相比也是相當大的，一查看統計資訊，資料量都在億級以上。

這麼大的表，使用了hint，指定全表掃描，相比是某些地方需要吧，帶著疑問查看了索引的資訊，而其中的主鍵索引就是IDENTIFIER欄位開始的。

所以從這個角度來看，這個問題是一個很明顯的問題，因為使用Hint不當導致了，本該走索引掃描的查詢結果走了極為消耗資源的全表掃描。

當然了，哲學中有句話是 存在即合理，可能在早期的時候資料量不大，處於某種需要，可能需要全表掃描，或者這部分邏輯是直接從某個地方參考而來，而其中的hint都忘了變更，導致了這樣的問題。

出了問題，找問題的理由也是多種多樣。當然最終這個問題還是發生了，能夠及時發現修複才是更重要的。

對於這個問題的分析暫時告一段落，但是還有dml對於undo的影響也不容小視，可供參考的就是前面表格中的delete語句了。

對於這個語句，delete涉及的表也是很大的一個分區表，資料量億級以上。在基於索引掃描的前提下，做了根據時間戳記進行資料清理的操作。對於這種操作，我們可以反過來考慮一下，目前delete的邏輯是對的，在排除了ac1_control_hist全表掃描影響的前提下，delete操作還是會消耗大量的undo資源。這個時候也需要同時考慮目前的undo大小是否完全滿足系統的要求。目前的庫裡undo的大小在17G左右，幾個大分區表都在百G以上，如果刪除所限定的時間戳記大一些，undo的消耗就會更大，所以也需要考量undo的大小，根據目前的情況，可以考慮適當增大undo空間。

所以這個問題的分析結果就是兩個建議，第一個就是對於本該索引掃描的語句走了全表掃描進行改進，規範hint的使用。另外一方面是建議適當調大undo的大小，以滿足系統的需求，使得系統的負載更有張力。