關於ORA-01555的問題分析

今天開發的同事發給我一個問題，在執行某一個Job的時候丟擲了ORA錯誤，希望我們看看從資料庫層面能不能發現什麼。
錯誤日誌如下：

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
	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
	421773076	1	12.01	db file sequential read	12.01	INDEX - RANGE SCAN	7.05	SELECT RE.L3_NET_START_TIME, R...
	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
	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
	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的大小，以滿足系統的需求，使得系統的負載更有張力。