Re: Undo Usage and Read consistency - ORA-1555

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Tue, 14 Jul 2009 10:53:14 -0500
Message-ID: <ad3aa4c90907140853q20965cc6yb5a8af85a62a9cae_at_mail.gmail.com>

When a short select causes a ora-1555, it usually means that that select is part of a larger transaction which began earlier than data is available in the undo. One simple method would be to issue a commit prior to doing the select, if that is possible. If it is not possible, you may need to increase the undo retention.

On Tue, Jul 14, 2009 at 10:35 AM, Nancy Iles <nancy_iles_at_hotmail.com> wrote:

>
> 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
> ________________________________
> > Date: Tue, 14 Jul 2009 10:24:00 -0300
> > Subject: Undo Usage and Read consistency - ORA-1555
> > From: cicciuxdba_at_gmail.com
> > To: oracle-l_at_freelists.org
> >
> > Dear DBAs,
> >
> > We got into a discussion about how read consistency is implemented in
> Oracle and was wondering what you know of this.
> >
> > The two sides are the following:
> > 1) Undo is used for any and all selects, so if you do a full scan on a
> large table it is bound to give ORA-1555 even if there is no transaction
> modifying the table
> >
> > 2) Undo is only used when a transaction modifies data (DML) and ONLY then
> ORA-1555 is possible, since it happens when the consistent version of the
> block stored in the UNDO by the transaction ages out.
> >
> > Documentation is unclear to this respect:
> >
> > From the concepts guide:
> >
> > To manage the multiversion consistency model, Oracle must create a
> > read-consistent set of data when a table is queried (read) and
> > simultaneously updated (written). When an update occurs, the original
> > data values changed by the update are recorded in the database undo
> > records. As long as this update remains part of an uncommitted
> > transaction, any user that later queries the modified data views the
> > original data values. Oracle uses current information in the system
> > global area and information in the undo records to construct a
> read-consistent view of a table's data for a query.
> >
> > Does this mean that every time I perform a select I get a copy of the
> data into de undo?
> >
> >
> > Alan Bort
> > Oracle Certified Professional
> _________________________________________________________________
> Hotmail® has ever-growing storage! Don’t worry about storage limits.
>
> http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage_062009--
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jul 14 2009 - 10:53:14 CDT