Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Mechanism of consistent reads

Mechanism of consistent reads

From: Yavor Ivanov <>
Date: Tue, 16 Jan 2007 16:13:29 +0200
Message-ID: <>

	Hello Fellow DBAs.
	I am trying to present a clear vision for the mechanism of the consistent reads. I got to the folowing (please correct me wherever i am wrong):

	Phase 1. The reading is initiated by the server process, responsible for executing the user query. The process finds DBA (database block address) of the block and hashes it with a function. The function returns address of a bucket. This bucket contains a linked list of blocks. The process walks through the list (after getting the bucket's latch in share mode), searching for the requested block.
	If the block is found, the process increases its "touch count indicator". This is done with no latching, so an increment may be lost, but it is not a big deal - we save some latching here.
	If the block is not found, it is read from the disk and put on the list. If there is not enough free memory, the least touched block is removed form the list to make room. The removed block cannot be dirty (if it is, then what - another one is removed, or DBWR is called to write it?)
	At this phase we have the block in the buffer cache, it is identified and copied in the process' memory (UGA)

	Phase 2. The SCN in the header of the block is compared with the SCN, taken when the query entered the execution stage (say SCN1). If the SCN is older (or equal?) to SCN1, the block is OK. If the SCN is newer, then the address of the transaction, which changed the block is determined from ITL found the block's header. Then the transaction's rollback (undo) segment is visited to get the "before image" of the block, copied there before the update. This is called "reconstructed block". The reconstructed block's SCN is compared again, and if it is (again) newer then SCN1, the reconstruction continues with the older transaction. This continues until the block is reconstructed down to SCN1 or older, or until ORA-1555 comes.


Yavor Ivanov
Senior Database Expert
Stemo Ltd
-- Received on Tue Jan 16 2007 - 08:13:29 CST

Original text of this message