Re: Undo Usage and Read consistency - ORA-1555
Date: Tue, 14 Jul 2009 08:45:14 -0500
Message-ID: <ad3aa4c90907140645q1413fa9cn333a0c093beaabfa_at_mail.gmail.com>
No, select does not always use undo. You can only get an ora-1555 from a select if your select has to read the undo area for data that has been modified since the start of the select, or since the start of the transaction that includes the select.
On Tue, Jul 14, 2009 at 8:24 AM, Guillermo Alan Bort <cicciuxdba_at_gmail.com>wrote:
> 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
>
-- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.' -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jul 14 2009 - 08:45:14 CDT