ORA-01555 解決處理方案

ORA-01555 caused by SQL statement below (Query Duration=49186 sec, SCN: 0x0a0b.b7167aa2):(然後跟個SQL）

官方解釋：

⑴in 9i/10g, no other good solution for the ora-01555
you should increase the undo_retentions in 9i and increase the undo space size
in 10g, oracle auto tune the undo_retentions thus, you have only choise to increase the undo space size. 
⑵
ORA-01555 snapshot too old: rollback segment number string with name "string" too small
Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.
Action: If in Automatic Undo Management mode, increase the setting of undo_retention. Otherwise, use larger rollback segments.

下面是個人解決意見：（希望有經驗的能指出我認識不對的地方，謝謝）

1、問題分析：
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO

undo_retention integer 10800（秒）

undo_suppress_errors boolean FALSE

undo_tablespace string UNDOTBS1

（1）檢視undo_retention 引數值為多少，預設為900秒，從本文件最開始的引數中也可以看到，此引數值為10800,好多高手也都建議將其改為10800,即3個小時。（顯然，日誌中的那條SQL執行時間幾乎全部大於這個時間）

（2）根據undo_tablespace 對應的undo表空間名，執行：
select sum(bytes)/(1024*1024) as free_space,tablespace_name 
from dba_free_space
group by tablespace_name;檢視其中的undo表空間名剩餘空間有多少，（這個最好不定時多執行幾次檢視，因為我們不知道什麼時候檢視才是undo剩餘最小的時候）。

執行：select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;檢視其中的undo表空間總大小是多少

註釋：當然啦，如果直接用Toad等工具直接看的話也可以的！

2、解決方案建議：
（1）對於上面查到的undo_retention的值，按理說我們需要增大到它到大於執行執行時間最長的事務所需的時間，也就是要大於alert日誌中 顯示的這條SQL的最大執行時間－67288秒，但這值實在太大了，增大undo_retention的值並不是沒有代價的，這會帶來UNDO過分擴充套件， 難以回收。為效能考慮，先試行將undo_retention的值增大為21600,即6個小時，這樣影響不會太大，然後看效果如何。
ALTER SYSTEM SET undo_retention=21600 SCOPE=BOTH;


（2）對於上面查到的undo_tablespace的表空間剩餘大小，如果確實空間不足，可適當增大。
而且由於上一步增大了undo_retention的時間，此處增大undo表空間可以說是必然的，除非undo表空間真的剩餘很大。

一般的撤銷表空間的大小有個計算公式,一般為了以防萬一，在這個基礎上增加10%~20%
undo_tablesapce_size=ur*ups*overhead
ur:以秒為單位的撤銷保留（undo_retention）
ups:每秒使用的撤銷塊
overhead:撤銷的後設資料(db_block_size)

 

以上只是從資料庫上考慮解決，其實最最實際的還是最佳化sql，最佳化sql才是王道！