Re: ORA-01555 seems bogus
- From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- To: Richard.Goulet@xxxxxxxxxxx
- Date: Mon, 8 Feb 2010 09:11:45 -0600
What this usually means is that the query is being run while a long running,
totally unrelated, transaction is also running, and the unrelated
transaction made a change to the table some time ago, and has not yet
commited, so your very short query must read undo to get the state of the
table prior to the start of the long running, unrelated transaction. The
trick is to locate the transaction, and get it to commit or rollback more
frequently. Having run into this a couple of times, I can safely say that
it is a real pain in the @$$ to figure out which transaction is actually
causing the problem.
On Mon, Feb 8, 2010 at 9:05 AM, Goulet, Richard
<Richard.Goulet@xxxxxxxxxxx>wrote:
> Dave,
>
> An ORA-01555 is never bogus, but very misleading as you've found
> out. OK, so the query in question is part of a package. Is the package
> called from some application that's doing a "commit across a cursor", or is
> the package doing it internally? Lots of places to look and undo is the
> very last.
>
>
> Dick Goulet
> Senior Oracle DBA/NA Team Lead
> PAREXEL International
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Herring Dave - dherri
> Sent: Saturday, February 06, 2010 10:10 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: ORA-01555 seems bogus
>
> 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@xxxxxxx
>
>
> ***************************************************************************
> 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.
>
> ****************************************************************************
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
Other related posts:
