Undo Usage and Read consistency - ORA-1555
- From: Guillermo Alan Bort <cicciuxdba@xxxxxxxxx>
- To: oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 14 Jul 2009 10:24:00 -0300
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
Other related posts:
