Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

RE: Mechanism of consistent reads

From: Polarski, Bernard <>
Date: Tue, 16 Jan 2007 15:38:31 +0100
Message-ID: <25D4919915CCF742A88EE3366D6D913D1169E48F@mailserver1>

Nice subject:

I always had a problem with the children latch behavior. One problem in your schema is that the latch covering the bucket may be locked itself in exclusive mode. In which case, problems start. Oracle does not wait for this natural children latch to become available, it is taking another latch to store the reference to the blocks copied into the memory.

Meantime, the first children is available again (the one locked) , how does Oracle known for all subsequent requests that a copy of the block exists but is 'displaced' into another list covered by a latch which is not the natural latch of the block.


As far as I known in RAC, there is a similar problem. But this is solved by each nodes maintaining a table that hold for each DBA the node responsible for block. This is normally given by the hash function. But the driven table behind the hash function, may be altered by the node themselves and a copy of the alteration is distributed through the interconnects to all others nodes. That is to say that the hash function that returns the relationship between nodes and DBA is in fact dependant of a fixed table and it may change by the time, so that the hash function may vary even without a node crash.

Bernard Polarski
Oracle DBA

-----Original Message-----
From: Yavor Ivanov [] Sent: dinsdag 16 januari 2007 15:13
Subject: Mechanism of consistent reads

	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:38:31 CST

Original text of this message