Re: ORA-01555 seems bogus
Date: Sun, 07 Feb 2010 07:58:54 +0200
Message-ID: <4B6E569E.8080101_at_gmail.com>
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)
> * 10.2.0.2
> * 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. | www.acxiom.com
> Service Desk: 888-243-4566, https://servicedesk.acxiom.com, GSCA_at_DNB.com
>
>
> ***************************************************************************
> The information contained in this communication is confidential, is
> intended only for the use of the recipient named above, and may be legally
> privileged.
>
> 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.
> ****************************************************************************
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Feb 06 2010 - 23:58:54 CST