ORA-01555超長的Query Duration時間

ORA-01555超長的Query Duration時間

現象：
Alert日誌報ORA-01555錯誤，但讓人感到不可思議的是超長的Query Duration（1382909202 sec）。
Mon Oct 28 05:26:42 2013
ORA-01555 caused by SQL statement below (Query Duration=1382909202 sec, SCN: 0x0011.0514ee8f):
Mon Oct 28 05:26:42 2013
ANALYZE TABLE "SAPPRD"."COSP" ESTIMATE STATISTICS SAMPLE 1 PERCENT FOR TABLE FOR ALL COLUMNS SIZE 1 FOR ALL INDEXES
Mon Oct 28 05:26:45 2013
ARC0: Completed archiving log 23 thread 1 sequence 1075683
Mon Oct 28 05:30:37 2013
Completed checkpoint up to RBA [0x1069e4.2.10], SCN: 0x0011.058bf650
Mon Oct 28 05:31:05 2013

分析：
我的參數UNDO_RETENTION = 900，
通常來說，ORA-01555意味著我們的查詢超過了900秒，並且很有可能查詢結果集中的某一行在900秒前被更新並提交。
（在NOGUARANTEE UNDO資料表空間中也存在另一可能：UNDO空間不夠，未到期的UNDO資訊被覆蓋）。
對於這種情況的解決辦法有幾種：
1、最佳化查詢SQL，使其更快執行完成；
2、增加UNDO_RETENTION大小，並且設定UNDO資料表空間為GUARANTEE模式，保護未到期的事物槽，但這種方式需要更大的UNDO空間。
但對於我們這種Query Duration大得離譜的現象，很有可能是遇到了某個BUG。
查詢MOS，發現現象跟Note-ORA-1555 Errors with Unrealistic Query Duration (Doc ID 1574399.1)描述的一致。
解決方案：
Oracle建議安裝one-off patch 10415967
該BUG在11GR2和10.2.0.5中被修複。

This has been discussed in Bug:10415967 - ORA-1555 SHOWS UNREALISTIC QUERY DURATION (AS IF RUNNING SINCE 01.01.1970) and Bug 7264172 : TB:SH STATUS DISPLAY AS "UNKNOWN" ON V$ROLLSTAT
Bug 7264172 deals with two issues. The v$rollstat view shows status as 'Unknown' and bad query duration. Since the former requires a modification for fixed view, this cannot be provided as a one-off patch. Hence the latter part is tracked in Bug 10415967.
Unrealistic query duration is reported when the start time is not initialized within the cursors
Apply one-off patch 10415967 if available for your DBversion and platform.
To download the patch, please follow the steps below:
1) Go To MOS
2) Click on Patches & Updates
3) In the Patch Search Section, enter your patch number in the Patch Name or Number field.
4) Click Search
5) Click the patch that meets your DbVersion and platform
6) Read any applicable notes before downloading, then click the download button.
This issue is fixed in 11gR2 and above. The fix is included in 10.2.0.5 also (but not in 11gR1).

ORA-01172、ORA-01151錯誤處理

ORA-00600 [2662]錯誤解決

ORA-01078 和 LRM-00109 報錯解決方案

ORA-00471 處理方法筆記

ORA-00314,redolog 損壞，或丟失處理方法

ORA-00257 歸檔日誌過大導致無法儲存的解決辦法