RE: Undo Usage and Read consistency - ORA-1555 Correction
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 14 Jul 2009 15:47:52 -0400
I fail to see how a commit helps this situation. Usually it's excessive
commits (like periodic commits in a long-running operation) that
contribute to this problem, by freeing up undo blocks to be re-used and
overwritten.
Paul Baumgartel
CREDIT SUISSE
Information Technology
Prime Services Databases Americas
One Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel@xxxxxxxxxxxxxxxxx
www.credit-suisse.com
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ric Van Dyke
Sent: Tuesday, July 14, 2009 3:28 PM
To: andrew.kerber@xxxxxxxxx; nancy_iles@xxxxxxxxxxx
Cc: cicciuxdba@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Undo Usage and Read consistency - ORA-1555 Correction
That should be: UNDO not REDO. The REDO has nothing to do with
ORA-1555s
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ric Van Dyke
Sent: Tuesday, July 14, 2009 3:16 PM
To: andrew.kerber@xxxxxxxxx; nancy_iles@xxxxxxxxxxx
Cc: cicciuxdba@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Undo Usage and Read consistency - ORA-1555
That would only if you can do the COMMIT in the process that is holding
the REDO.
Doing a COMMIT in the session that is doing the SELECT will have no
impact on 1555's. To solve this is an application design or usage
issue. It's very likely that a user terminating in another session could
be the cause.
-----------------------
Ric Van Dyke
Hotsos Enterprises
-----------------------
Hotsos Symposium
March 7 - 11, 2010
Be there.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Andrew Kerber
Sent: Tuesday, July 14, 2009 11:53 AM
To: nancy_iles@xxxxxxxxxxx
Cc: cicciuxdba@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Undo Usage and Read consistency - ORA-1555
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@xxxxxxxxxxx>
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@xxxxxxxxx
> To: oracle-l@xxxxxxxxxxxxx
>
> 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(r) has ever-growing storage! Don't worry about storage limits.
http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tut
orial_Storage_062009--
//www.freelists.org/webpage/oracle-l
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
===============================================================================
Please access the attached hyperlink for an important electronic
communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
===============================================================================
Other related posts:
