RE: Undo Usage and Read consistency - ORA-1555
- From: "Randy Johnson" <oraclelist@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 14 Jul 2009 15:34:04 -0500
[I'd say if the solution was as easy as not changing data to begin with it
is probably not a viable one.]
Okay this didn't come out right. What I meant to say was it is unlikely that
the solution is as simple as changing/deleting fewer rows of data.
I'd like to hear from you guys on my proposed solution. Catch me if I missed
something or if you disagree.
-Randy
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Randy Johnson
Sent: Tuesday, July 14, 2009 3:29 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Undo Usage and Read consistency - ORA-1555
While I agree with your assessment of the cause (updates/deletes in another
session) I'd say if the solution was as easy as not changing data to begin
with it is probably not a viable one. I apologize in advance if I
misinterpreted what you meant to say. The ORA-1555 is telling you the before
image block needed to satisfy the query has been flushed from Undo in order
to make room for uncommitted undo blocks.
To your options are:
- Change less data
- This is a tough one. Change the SQL statement to change
fewer rows.
Or use a smaller block size so you end up with fewer rows
Per block.
- Can anyone think of another way to decrease the effected
rows?
- Commit less often (may not really help that much because
eventually you
are still going to have to commit and the same # of undo blocks are
involved).
- Make sure you have appropriate indexes for the SQL query so you
aren't
causing full table scans.
- Increase the undo_retention time and possibly the undo tablespace
size.
This will increase the time Oracle will attempt to keep committed
undo blocks
in the undo segments before flushing them. Of course this may
require more
tablespace storage, ergo a bigger tablespace.
Kerry Osborne has some pretty useful scripts for determining bind variable
peaking on his blog.
http://kerryosborne.oracle-guy.com/2009/03/bind-variable-peeking-drives-me-n
uts
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Michael McMullen
Sent: Tuesday, July 14, 2009 12:35 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Undo Usage and Read consistency - ORA-1555
I think it's important to note that you get an ora-1555 error as result of a
commit happening in another session/process. It's not your select causing
the error, it's just reporting why it can't give a read consistent view of
the data. So often (but not always) you have to work on the process that is
actually changing the data in order to really resolve the ora-1555 error.
Personally, I find the only thing worse than figuring out bind variable
peeking problems is figuring out ora-1555 issues.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Nancy Iles
Sent: Tuesday, July 14, 2009 11:36 AM
To: cicciuxdba@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Undo Usage and Read consistency - ORA-1555
We have sporadic ora-1555 on an exceptionally simple statement that occurs
frequently in the application. How can you analyze why this tiny, simple
statement is causing an ORA-1555? The statement is:
SELECT RESV_NAME_ID , RESORT FROM RESERVATION_NAME WHERE CONFIRMATION_NO =
:1
I believe that this causes a user session to hang. Our third party vendor
says that it is because a user terminates their session improperly and that
it is not an issue.
Any suggestions on how to analyze the cause and the system impact?
TIA,
Nancy Iles
Omni Hotels
--
//www.freelists.org/webpage/oracle-l
__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4243 (20090714) __________
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4243 (20090714) __________
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4243 (20090714) __________
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
--
//www.freelists.org/webpage/oracle-l
__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4243 (20090714) __________
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4243 (20090714) __________
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4243 (20090714) __________
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
