RE: Undo Usage and Read consistency - ORA-1555
From: Nancy Iles <nancy_iles_at_hotmail.com>
Date: Tue, 14 Jul 2009 10:35:44 -0500
Message-ID: <SNT121-W951F316AC122A4A03F627F8230_at_phx.gbl>
> 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 Received on Tue Jul 14 2009 - 10:35:44 CDT
Date: Tue, 14 Jul 2009 10:35:44 -0500
Message-ID: <SNT121-W951F316AC122A4A03F627F8230_at_phx.gbl>
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 Received on Tue Jul 14 2009 - 10:35:44 CDT