ORA-01555 caused by SQL statement below Query Duration=0 sec

sky13and23

Oracle Linux Server release 5.6  64位 +  10.2.0.5.0 - 64bit RAC
后台每天都有类型报错

ORA-01555 caused by SQL statement below (SQL ID: cfwqmf2cdc6tu, Query Duration=0 sec, SCN: 0x0b8f.04e17e1b):
Wed Mar 14 07:57:07 CST 2012
SELECT "A1"."RUNDATE","A1"."PLATENO","A1"."RIDETIMES","A1"."TOTALEMPTYMILES","A1"."MAXEMPTYMILES","A1"."MAXPRICE","A1"."MAXPRICETIMES","A1"."TOTALRIDEMILES","A1"."MAXRIDEMILES","A1"."MINRIDEMILES","A1"."TOTALDUES","A1"."MAXDUES","A1"."RUNTIME" FROM "TAXI_CD_DAILYSTAT" "A1" WHERE "A1"."RUNDATE">=:1 AND "A1"."RUNDATE"<TRUNC(:2)-1

Wed Mar 14 03:26:52 CST 2012
ORA-01555 caused by SQL statement below (SQL ID: gb9tn3srcs2qd, Query Duration=0 sec, SCN: 0x0b8e.75f0211d):
Wed Mar 14 03:26:52 CST 2012
SELECT "A1"."ROUTECODERUN","A1"."STOPCODE","A1"."UPCOUNT","A1"."CREATETIME" FROM "BUS_PASSENGERINFO" "A1" WHERE "A1"."CREATETIME">=:1 AND "A1"."UPCOUNT">'0' AND "A1"."CREATETIME"<TRUNC(:2)
Wed Mar 14 03:48:15 CST 2012


我已经修改
undo_retention                       integer     7200

undo表空间足够大
UNDOTBS1 96000MB
UNDOTBS2 96000MB

奇怪的是为什么ORA-01555 报错都Query Duration=0 sec ，这个返回查询时间很难判断如何设置undo_retention  ，请问此类情况如何解决，谢谢

Maclean Liu(刘相兵

ORA-01555 Query Duration=0 sec在Metalink上相关的BUG 非常多，



RELATED BUGS:
-------------
Bug 8738025 - ORA-1555 INTERMITTENT DESPITE AUTO UNDO MANAGEMENT AND UNDO
RETENTION SET
Bug 8873328 - GETIING ORA-1555 ERROR INTERMITTANTLY
Bug 9631403 - ORA-1555 "QUERY DURATION=0 SEC" IN AN OEM REPOSITORY DATABASE
Bug 8669806 - ORA-1555 ERRORS IN DATABASE
Bug 9654701 - ORA-1555: SNAPSHOT TOO OLD: ROLLBACK SEGMENT NUMBER 9 WITH
NAME "???" TOO SMALL

该问题出现的主要几种可能性：

1. 纯粹的Bug
2. 11g Active Data Guard ADG 中出现ORA-01555 Query Duration=0 sec
3. LOB 相关的查询
4. 查询周期过长导致的 Query Duration溢出 ，从而显示为0


首先建议你确认这个 查询Query Select 实际运行了多久, 可以通过 scn_to_timestamp(ORA-1555报错的SCN)转换为时间戳，然后和ALERT.LOG中的报错时间如 Wed Mar 14 03:26:52 CST 2012做对比。

其次确认你的查询中没有LOB 对象。


如果没有更多可用的信息， 建议你设置 1555 event的ERRORSTACK DUMP 等待下一次发生 ORA-1555错误 收集足够的诊断信息：

alter system set events '10442 trace name context forever, level 1';
alter system set events '1555 trace name errorstack level 10';