Mechanism of consistent reads
- From: "Yavor Ivanov" <Yavor_Ivanov@xxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 16 Jan 2007 16:13:29 +0200
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.
--
Regards,
Yavor Ivanov
Senior Database Expert
Stemo Ltd
--
//www.freelists.org/webpage/oracle-l
Other related posts:
