RE: Undo Usage and Read consistency - ORA-1555
Date: Tue, 14 Jul 2009 09:03:40 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF014E0DF8_at_WIN02.hotsos.com>
To implement read consistence what Oracle does is compare the SCN of the time when a select starts with the SCN in the block header at the time it reads a block. If the SCN of he block is higher then the SCN of the query then Oracle must create a read consistent version of the block. At that point it will roll back the block to the SCN of the query. When this happens it uses UNDO. As long as the block's SCN is lower or equal to the query's SCN then no UNDO is used.
I think the confusion is because oracle now counts all reads for a query as consistent reads, and in the good old days that meant UNDO was used to create a read consistent version of the block and a current read means the block was read with out creating the read consistent version (no use of UNDO). However today Oracle counts all reads in a query as consistent reads even if they don't create a read consistent version of the block.
A large full table scan is more likely to have ORA-1555s only because you are reading more so there is more potential that you will hit a block that has been modified since your query started. But that's all, not because you have to use UNDO for every block touched.
One more thing, a query doesn't increment the SCN, it just looks at the current SCN when the query starts and uses that number as the point in time for all the blocks it looks at. Only DML type actions increment the SCN.
Ric Van Dyke
Hotsos Enterprises
Hotsos Symposium
March 7 - 11, 2010
Be there.
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Guillermo Alan Bort
Sent: Tuesday, July 14, 2009 9:24 AM
To: oracle-l-freelists
Subject: Undo Usage and Read consistency - ORA-1555
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
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jul 14 2009 - 09:03:40 CDT