ORA-01555 ultra-long Query Duration time

ORA-01555 ultra-long Query Duration time

Symptom:
The Alert Log reports a ORA-01555 error, but what is incredible is the ultra-long 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

Analysis:
My parameter UNDO_RETENTION = 900,
Typically, the ORA-01555 means that our query exceeds 900 seconds, and it is very likely that a row in the query result set will be updated and submitted before 900 seconds.
(There is another possibility in the noguarantee undo tablespace: UNDO space is insufficient, and UNDO information that has not expired is overwritten ).
There are several solutions to this situation:
1. Optimize the SQL query so that it can be executed more quickly;
2. Increase the UNDO_RETENTION size and set the UNDO tablespace to the GUARANTEE mode to protect the non-expired transaction slots. However, this method requires a larger UNDO space.
However, for our Query Duration phenomenon, it is likely that we encountered a BUG.
Query MOS and find that the phenomenon is consistent with the Note-ORA-1555 Errors with Unrealistic Query Duration (Doc ID 1574399.1) description.
Solution:
Oracle recommends installing one-off patch 10415967
This BUG is fixed in 11GR2 and 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 already ded in 10.2.0.5 also (but not in 11gR1 ).

ORA-01172, ORA-01151 error handling

ORA-00600 [2662] troubleshooting

Troubleshooting for ORA-01078 and LRM-00109

Notes on ORA-00471 Processing Methods

ORA-00314, redolog corruption, or missing Handling Methods

Solution to ORA-00257 archive logs being too large to store