RE: ORA-01555 seems bogus
- From: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
- To: "Kellyn Pedersen" <kjped1313@xxxxxxxxx>, <Dave.Herring@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 8 Feb 2010 12:49:39 -0500
Kelly,
OH SH&^, I think we crossed wires. What I meant by "undo is the
very last" is that the last thing you want to do is extend the undo
space because the lack of undo is not the problem. Case in point, an
SAP system that was always throwing ORA-01555 error on a specific report
that took all day to process. The UNDO space was 10 times the size of
the entire rest of the database because the BASIS Admin was always
adding undo space in response to the error, yet the error remained.
Dick Goulet
Senior Oracle DBA/NA Team Lead
PAREXEL International
________________________________
From: Kellyn Pedersen [mailto:kjped1313@xxxxxxxxx]
Sent: Monday, February 08, 2010 12:42 PM
To: Dave.Herring@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx; Goulet, Richard
Subject: RE: ORA-01555 seems bogus
Now Dick,
I agree that the overall culprit is often exactly what you have detailed
below, but I think it is important to see that what you *think* is being
used by your statement might be very different in reality when executed
in whole- as part of that one procedure, etc...
I think starting with undo is perfectly acceptable to see what the
database has allocated towards the process, then proceed back, into the
statement(s) in question to see where the logic is causing the issue or
what other processes might be hampering the one that's been identified
as the problem... :)
Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
www.dbakevlar.blogspot.com <http://www.dbakevlar.blogspot.com/>
"Go away before I replace you with a very small and efficient shell
script..."
--- On Mon, 2/8/10, Goulet, Richard <Richard.Goulet@xxxxxxxxxxx> wrote:
From: Goulet, Richard <Richard.Goulet@xxxxxxxxxxx>
Subject: RE: ORA-01555 seems bogus
To: Dave.Herring@xxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
Date: Monday, February 8, 2010, 8:05 AM
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
<http://us.mc320.mail.yahoo.com/mc/compose?to=oracle-l-bounce@freelists.
org> [mailto:oracle-l-bounce@xxxxxxxxxxxxx
<http://us.mc320.mail.yahoo.com/mc/compose?to=oracle-l-bounce@freelists.
org> ] On Behalf Of Herring Dave - dherri
Sent: Saturday, February 06, 2010 10:10 PM
To: oracle-l@xxxxxxxxxxxxx
<http://us.mc320.mail.yahoo.com/mc/compose?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
<https://servicedesk.acxiom.com/> , GSCA@xxxxxxx
<http://us.mc320.mail.yahoo.com/mc/compose?to=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
Other related posts:
