Re: ORA-01555 seems bogus
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- Date: Mon, 8 Feb 2010 10:58:18 -0800 (PST)
Hey Andrew,
Can I ask you why you would adjust your undo retention? The system will
automatically tune the undo retention for you when a process requires a longer
one- it's part of the smon's job to ensure this...
If you look at tuned_undoretention in v$undostat, anything that requires a
larger retention time than the one set, should allocate a longer retention time
automatically. I am having a difficult time understanding how adjusting the
retention time would correct a problem with the space allocated in the undo
tablespace...
Please elaborate... thanks!
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, Andrew Kerber <andrew.kerber@xxxxxxxxx> wrote:
From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
Subject: Re: ORA-01555 seems bogus
To: Richard.Goulet@xxxxxxxxxxx
Cc: "Kellyn Pedersen" <kjped1313@xxxxxxxxx>, Dave.Herring@xxxxxxxxxx,
oracle-l@xxxxxxxxxxxxx
Date: Monday, February 8, 2010, 11:08 AM
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 [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:
