Re: ORA-01555 seems bogus
- From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- To: Richard.Goulet@xxxxxxxxxxx
- Date: Mon, 8 Feb 2010 12:08:35 -0600
You need to locate the long running transaction, and try and make it run in
a shorter time frame, failing that, you need to try adjusting the undo
retention. The issue isnt necessarily a shortage of undo, it is simply that
the transaction has run longer than undo_retention setting without doing a
commit, and this short query is trying to read the data as it was at the
start of the long running transaction.
On Mon, Feb 8, 2010 at 11:49 AM, Goulet, Richard <Richard.Goulet@xxxxxxxxxxx
> wrote:
> 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
>
> "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@xxxxxxxxxxxxx>[mailto:
> oracle-l-bounce@xxxxxxxxxxxxx<http://us.mc320.mail.yahoo.com/mc/compose?to=oracle-l-bounce@xxxxxxxxxxxxx>]
> 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,
> 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
>
>
>
>
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
Other related posts:
