RE: Undo Usage and Read consistency - ORA-1555

From: Randy Johnson <oraclelist_at_sbcglobal.net>
Date: Tue, 14 Jul 2009 15:28:59 -0500
Message-ID: <001701ca04c1$b86cae90$29460bb0$_at_net>



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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael McMullen
Sent: Tuesday, July 14, 2009 12:35 PM
To: oracle-l_at_freelists.org
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nancy Iles
Sent: Tuesday, July 14, 2009 11:36 AM
To: cicciuxdba_at_gmail.com; oracle-l_at_freelists.org 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

--
http://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
 

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jul 14 2009 - 15:28:59 CDT

Original text of this message