| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Mechanism of consistent reads
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.
Mystery.
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 [mailto:Yavor_Ivanov_at_stemo.bg]
Sent: dinsdag 16 januari 2007 15:13
To: oracle-l_at_freelists.org
Subject: Mechanism of consistent reads
Hello Fellow DBAs. I am trying to present a clear vision for the mechanism of theconsistent 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.
-- Regards, Yavor Ivanov Senior Database Expert Stemo Ltd -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 16 2007 - 08:38:31 CST
 |
 |