Re: ORA-01555 seems bogus

  • From: Harel Safra <harel.safra@xxxxxxxxx>
  • To: Dave.Herring@xxxxxxxxxx
  • Date: Sun, 07 Feb 2010 07:58:54 +0200

Are you sure the application always fetches all the rows from the cursor immediately?

For example, if the application:
opens the cursor, fetches 10 rows, waits an hour, fetches another 10 rows, waits another hour,... You could get the 1555 error even though a full scan of all the table is sqlplus takes only 1 second.
Harel Safra

On 07/02/2010 05:10, Herring Dave - dherri wrote:
Folks, I've got a curious situation where we're getting regular ORA-01555 
errors yet they don't seem correct.  First a few things on the set up:

* 4-node RAC (+ 4-node RAC DG)
* RHEL 8 x 64GB servers

The query returned in the error is doing a full scan of a 3,100 row table, 
which takes less than 1 sec to complete.  I've tried the same query repeatedly 
and it always takes less than one second, even when trying it while ORA-01555 
errors are being returned saying that query is failing elsewhere.  The query is 
run 10,000+ times per day, always run from the same node.

The ORA-01555 error lists this query as having a duration of around 12,000 
seconds each time, which from what I can tell is impossible.  Again, it's a 
3,100 row table, no parallelism, 1 index (which isn't used by this query).  I 
checked LAST_ANALYZED and the table hasn't been analyzed in 4 days.  I checked 
DBA_TAB_MODIFICATIONS and it's had around 200 DML's since it was analyzed, but 
the last of which was 2 days ago.

So is Oracle returning the wrong query?  Is the ORA-01555 bogus?  Checking 
GV$UNDOSTAT, SSOLDERRCNT matches the # of ORA-01555 we're seeing (87 today, all 
the same query).  All instances have the same UNDO_RETENTION (3600) and 
similarly sized undo tablespaces.

Any clues how to catch the trouble making queries?

Dave Herring  | DBA, Global Technology Services
A c x i o m  C o r p o r a t i o n
630-944-4762 office | 630-430-5988 cell | 630-944-4989 fax
1501 Opus Pl | Downers Grove, IL, 60515 | U.S.A. |
Service Desk: 888-243-4566,, GSCA@xxxxxxx

The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally

If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank You.



Other related posts: