Oracle ORA-01555 ::snapshot too old Error Message

ORA-01555 ::snapshot too old Error Message

標籤: 
ORA-01555 Error Message
 

出現ORA-01555錯誤,通常有下列情況:
            SQL語句執行時間太長,或者UNDO表空間過小,或者事務量過大,或者過於頻繁的提交,導致執行SQL過程中進行一致性讀時,SQL執行後修改的前鏡像(即UNDO資料)在UNDO表空間中已經被覆蓋,不能構造一致性讀塊(CR blocks)。  這種情況最多。
  
解決的辦法:
(1)增加UNDO表空間大小
(2)增加undo_retention 時間,默認只有15分鐘
(3)優化出錯的SQL,減少查詢的時間,首選方法
(4)避免頻繁的提交


ORA-01555 caused by SQL statement below (SQL ID: 52ssczq4j3vp6, Query Duration=0 sec, SCN: 0x0b6b.3481606d):

Mon Nov  4 15:41:03 2013
SELECT "A1"."PROD_NAME",'IN',NVL( (SELECT SUM("A5"."INIT_IN_QTY") FROM "DWC5"."MPS_DAILY" "A5" WHERE "A5"."SHIFT_DATE">='20131101' AND "A5"."SHIFT_DATE"<='20131103' AND "A5"."PROD_NAME"="A1"."PROD_NAME" AND '20131101'<='20131103'),0),NVL( (SELECT SUM("A4"."IN_QTY") FROM "DWC5"."MPS_DAILY" "A4" WHERE "A4"."MONTH"='201311' AND "A4"."PROD_NAME"="A1"."PROD_NAME"),0),NVL( (SELECT SUM("A3"."IN_QTY") FROM "DWC5"."MPS_DAILY" "A3" WHERE "A3"."SHIFT_DATE">='20131101' AND "A3"."SHIFT_DATE"<='20131103' AND "A3"."PROD_NAME"="A1"."PROD_NAME" AND '20131101'<='20131103'),0),SUM(NVL("A1"."IN_SUBS_QTY",0)-NVL("A1"."CANCEL_IN_SUBS_QTY",0)) FROM "DWC5"."PRODUCT" "A2","DWC5"."DAILY_IN_OUT_SUM" "A1" WHERE "A1"."SHOP"='C5' AND "A1"."SHIFT_DATE">='20131101' AND "A1"."SHIFT_DATE"<='20131103' AND "A1"."CUT_TIME"='070000' AND ("A1"."LOT_TYPE"='P' OR "A1"."LOT_TYPE"='E') AND "A1"."PROD_NAME"="A2"."PROD_NAME" AND ("A2"."DAILYRPT_DISPFLAG_IO"='M' OR "A2"."DAILYRPT_DISPFLAG_IO"='A') AND '20131101'<='20131103' GROUP BY "A1"."PROD_NAME"
Mon Nov  4 15:47:10 2013
 
在上面,有出現ORA Error、SQL ID & SQL語法:
一、          透過EM查看,上面寫說UNDO TABLE的Retention值過大(10818秒),經查詢undo retention為10800秒(SQL> show parameter_retention),不夠18秒,因此跳出此錯誤,更改此undo retention值。
 
ORA-01555 Snapshot Too Old Error detected: Undo Tablespace UNDOTBS1, Current Undo Retention 10818.
 
SQL> show parameter undo
 
NAME                                 TYPE        VALUE
———————————— ———– ——————————
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_tablespace                      string      UNDOTBS1
 
設定undo_retention值為10818:
ALTER SYSTEM SET UNDO_RETENTION = 10818;
 
二、          請AP人員幫忙確認一下此SQL,是否有效率。
透過此SQL_ID,找詢相關資訊(時間若過的太久,也有可能查詢不到)
selectsql_id,first_load_time,parsing_schema_name,service,module,cpu_time,disk_reads,sql_text from v$sql WHERE sql_id=<52ssczq4j3vp6>;
 
透過parsing_schema_name,可以看出此User是誰,在到v$session Table查詢相關資訊(若此人的session不在了,當然也查不到了):
selectsid,username,schemaname,osuser,machine,terminal,sql_id,logon_time,service_name, event,module  from v$session WHERE sql_id=<52ssczq4j3vp6>;;
 
若很幸運的,兩個table都有查到資料,應該知道人是誰了吧!!
 
selectv.sql_id,v.first_load_time,v.module,v.cpu_time,v.disk_reads,
s.username,s.schemaname,s.osuser,s.machine,s.terminal,s.logon_time,s.service_name,s.event, v.sql_text
from v$sql v ,v$session s where v.sql_id=s.sql_id order byv.first_load_time desc;
 
 
v$sql說明:
v$sql除了 group by 不顯示,其他皆會顯示(共用sql,說白了就是shared pool 中的library cache);另外:一些長查詢,它是每5秒更新一條統計資訊,其他的查詢sql則是每次查詢執行完就會更新統計資訊了
 
undo_retention說明:
undo_retention: 該參數用來指定undo 記錄保存的最長時間,以秒為單位,是個動態參數,完全可以在實例運行時隨時修改通常默認是900 秒,也就是15 分鐘。
 
 
該參數用來指定undo 記錄保存的最長時間,以秒為單位,是個動態參數,完全可以在實例運行時隨時修改通常默認是900 秒,也就是15 分鐘。
一定要注意,undo_retention 只是指定undo 數據的過期時間,並不是說,undo 中的數據一定會在undo表空間中保存15 分鐘,比如說剛一個新事務開始的時候,如果undo 表空間已經被寫滿,則新事務的數據會自動覆蓋已提交事務的數據,而不管這些數據是否已過期,因此呢,這就又關聯回了第一點,當你創建一個自動管理的undo 表空間時,還要注意其空間大小,要盡可能保證undo 表空間有足夠的存儲空間。
同時還要注意,也並不是說,undo_retention 中指定的時間一過,已經提交事務中的數據就立刻無法訪問,它只是失效,只要不被別的事務覆蓋,它會仍然存在,並可隨時被flashback 特性引用。如果你的undo表空間足夠大,而數據庫又不是那麼繁忙,那麼其實undo_retention 參數的值並不會影響到你,哪怕你設置成1,只要沒有事務去覆蓋undo 數據,它就會持續有效。因此呢,這裏還是那句話,要注意undo 表空間的大小,保證其有足夠的存儲空間。
 
只有在一種情況下,undo 表空間能夠確保undo 中的數據在undo_retention 指定時間過期前一定有效,就是為undo 表空間指定Retention Guarantee,指定之後,oracle 對於undo 表空間中未過期的undo 數據不會覆蓋,
例如:
SQL> Alter tablespace undotbs1 retention guarantee;
 
相關資料:
Undo_retention說明:
 
http://rritw.com/a/shujuku/Oracle/20120918/225587.html