Summary about the deletion of delayed blocks, the snapshot is old, and the read is consistent.

Summary about the deletion of delayed blocks, the snapshot is old, and the read is consistent.

We hope to combine these three knowledge points to summarize the summary of the delayed part clearing, the snapshot being old, And the read consistency. You are welcome to discuss them together, I would be more grateful if some predecessors have pointed out the mistakes.

Blockcleanout does not mean writing dirty blocks to the disk. It simply means changing a block in the DB buffer from dirty to clean, indicating that the data in the block is clean and up-to-date, in essence, it is used to update a flag in the block header-ITL (Interested Transaction List) and block SCN.

What is delayed block cleanout?

When a transaction is committed, the modified parts of the transaction will be cleanout, but there are two ways to Clean out: fast commit cleanout and delayed blockcleanout.

1. fastcommit cleanout is a cleanout in the true sense. As a fast commit cleanout, Oracle uses the system scn of the transaction commit as the commitSCN, update the scn on the Transaction table slot of the ITL, block scn, and undo segment header on the block immediately. The three are consistent.

2. delayedblock cleanout delays the cleanout operation. For some reason, it only uses commit SCN to update the slot scn on the Transaction table of the undo segment header, but does not update the block, wait for the next time you use this block, and then use the slot scn on the Transaction table of the undo segment header (same as the commit SCN of the previous Transaction) to update the block scn and ITL. (Currently, when an operation such as SELECT, UPDATE, INSERT, or DELETE accesses to these blocks, you must clear the blocks after reading them)

Why do we need to execute delayed block cleanout?

This is for performance consideration. First, let's look at which blocks will be used for delayed block cleanout.

Premise: Oracle has a modifiedblock list structure (checkpoint queue mechanism ?), Used to record the modified blocks of each transaction. Each transaction can record about 10% of the modified blocks of buffercache on this list.

Transaction commit:

• If the modified block is less than 10%, oracle can locate those blocks based on the modified block list and execute fast commit cleanout.
  
• If the number of modified blocks exceeds 10%, the excess blocks are delayed block cleanout.
  
• Before a transaction is committed, it takes too long to write the transaction to the disk block for delayed block cleanout.
  

Here we can see that there are two reasons for not immediately cleanout, but in essence, we cannot find the corresponding block in the DB buffer immediately. The former is over 10% and is not recorded in the list, the latter has been written to the disk. If you read the database buffer again and modify the buffer, too much IO will affect the performance.

What is the relationship between snapshot and old snapshot?

Premise: This block has been used for other sessions (clean), or the block is being occupied (dirty ), ITL (itl, xid, flag, uda, scn \ fsc) will be recorded on the blocks ).

1. When a select statement is issued, ORACLE will record the SCN at this time point, and then find the required BLOCK in the buffer cache, or read it from the disk.

2. First, check the flag of the last transaction that modified the block. execute the transaction immediately if cleanout is required. If the execution is successful or you do not need to execute it, compare ITLSCN and select SCN. If itl scn> select SCN, the version of the block is newer than select, to perform read consistency, find the old version.

3. ORACLE will find the UNDO information in the uba in ITL to obtain the pre-image of the block, and then create a CR block in buffercache, at this time, ORALCE will also check the SCN of the ITL record in the constructed CR blocks. If the SCN is greater than the SCN at the select moment, it will repeat the pre-construction image until the desired blocks are found, in this way, ORACLE implements multiple versions. However, if the UNDO information required during the image construction process is overwritten, an error of the previous snapshot is reported. To put it simply, you can use recursive methods to find the version of the same SCN as your select. If you cannot find the version, the snapshot is too old.

For delayed clearing blocks, although the corresponding transaction has been committed, it is still in the dirty status. Previously, only the scn on the slot (slot) of the Transaction table of the undo segment header was updated during commit, but the itl and block scn of the block were not updated. When you access this block again, you must complete the remaining work, that is, cleanout immediately mentioned in the second step above -- Update the itl scn and blockscn of this block. As mentioned before, if the clean operation is successful, compare the itl scn and selectSCN to determine whether to execute read consistency. However, if undo is overwritten, The commit SCN cannot be obtained, and the cleanout cannot be executed. An error is returned or the snapshot is too old.