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).