Transaction table rollbacks
|You mention the idea of getting a consistent read of the RBS header block: I still haven't quite got that one cracked. Would I be correct in thinking that if the transaction slot of the rollback segment has the wrong wrap number, then Oracle goes to the indicated undo block, and that contains rollback information for the slot, so the slot is made read-consistent by stages?|
|The consistent read on the rollback segment header block is similar to that for a data block, but differs in the chain of undo used. The transaction control header in the rollback segment header block has a control UBA that is the head of the chain of changes to the header block itself. This is the chain that is followed to rollback the header block. There is another important control structure called the control SCN. It is the commit SCN of the most recently reused transaction slot's previous transaction. If the control SCN is newer than the snapshot SCN for the consistent read, then ORA-1555 can be returned immediately without attempting the roll back, because it would be bound to fail. (That ORA-01555 actually reflects the failure of the consistent read against the undo header block to get back past the snapshot SCN.) The control SCN is also used as the commit SCN for a cleanout of a block changed by a transaction that committed in antiquity. This is marked in the ITL with a U flag. The U stands for "upper bound commit", meaning that the SCN is an upper bound for the time at which it committed. Note that the U flag in the ITL is also set for a fast commit, but the two cases can be distinguished by the presence or absence of row-level locks.|
|What does the delayed_logging_block_cleanouts parameter do?|
We need a bit of terminology to discuss this.
A block cleanout is an operation to release the row-level locks that were held by a transaction.
Row-level locks consist of a pointer in the row header to an entry in the interested transaction list (ITL) in the variable header of the block.
In a cleanout, the row level locks may be cleared and the commit SCN of the transaction is recorded in the ITL.
The term commit cleanout refers to cleanouts of just the ITL entry performed by the transaction itself when it commits.
The term delayed block cleanout refers to cleanouts performed later by another transaction.
As a transaction modifies blocks, the buffer header addresses are recorded in a commit cleanout data structure, which can hold pointers to up to 10% of the buffers in the cache. When the transaction commits, it traverses its commit cleanout data structure in reverse order and cleans out its ITL entries. The commit cleanout of a block can fail if the block has already been written to disk, or for several other reasons. If a commit cleanout fails for any reason, then the block is written to disk with the ITL and row-level locks not yet cleaned out. This is where delayed block cleanout comes in.
Delayed block cleanout is performed by the next reader of the block, even if it is just a query. The block is read into the cache as a current mode block and cleaned out. If a query is being performed, a consistent read mode clone of the block is made using another block buffer, and if necessary rolled back to the snapshot SCN for the query. The consistent read mode buffer is used to satisfy the query. However, any changes are made to the current mode buffer.
Because block cleanout actually makes changes to the current mode buffer, it must generate redo. If delayed_logging_block_cleanouts is TRUE, which is the default in 7.3 and 8.0, the logging of this redo is delayed until another change is made to the current buffer. The cleanout itself does not mark the buffer as dirty or log the redo for its changes. The next change to the buffer marks the buffer as dirty and logs redo for the cleanout as well as its own changes.
If the parameter is FALSE, the cleanout logs its own redo and marks the buffer as dirty, even if the block was only being used for a query. Because the buffer is dirty, DBWn must write the cleaned out block back to disk. This increases DBWn's workload, but prevents the cleanout from having to be repeated by later queries. The cost of a cleanout is low, relative to a write, but not trivial, because it takes a buffer lock on the buffer containing the rollback segment header block for the old transaction, and that can be a "hot" block.
Delayed logging block cleanouts save pings against data blocks in parallel server databases, at the expense of some extra pings against rollback segment header blocks. So, in general for OPS databases the parameter should be left to default to TRUE. However, if you have data that is queried intensively, but seldom changed, then it may be beneficial to temporarily set the parameter to FALSE and force a serial full table scan after each change. The full scan has to be serial, because parallel scans do direct reads, so the cleanouts are done in private buffers in the PGAs of the parallel query slaves, where the cleaned out blocks cannot be written back to disk by DBWn.
This parameter was introduced in release 7.3 in an attempt to reduce pings in OPS databases. It has however been removed from release 8.1, having been obviated by cache fusion.
Delayed block cleanout
|Why would delayed block cleanout be needed on a table that is only ever queried and never updated?|
|Every data block that is needed for the query will have been changed by at least one transaction at some point in the history of the database. If the row-level locks required for the last (possibly ancient) transaction to modify the block were not able to be cleaned out at the time, and if delayed_logging_block_cleanouts is TRUE (which is the default) then all subsequent queries will need to perform delayed block cleanout until another transaction modifies the block and cleans out the old row-level locks permanently.|
|I got an ORA-1555 (snapshot too old) error while doing DROP USER CASCADE on all users except SYS and SYSTEM. Why?|
|It is either that each DROP USER CASCADE command involves a lot of recursive transactions, and once a rollback segment extent has been reused, the query fails because the rollback segment header block can no longer be rolled back for read consistency. Or it may be merely because of repeated changes to blocks in the C_USER# cluster in combination with rollback segment extent reuse. These options are explained in more detail in our tip on this issue.|
Cleanouts in 8.1
|I was reading an Oracle document that explained that in delayed logging block cleanout a fast cleanout is done. The transaction is marked as committed and the commit SCN is recorded in the ITL, but the lock bytes on the individual rows are not cleared, and no redo is generated. Any idea why this has been discontinued in 8.1?|
|The confusion is that they use the term "delayed logging block cleanout" in association with (fast) commit cleanouts. These two features just happen to have been introduced together (in 7.3), but have nothing else in common. What you have written describes how commit cleanouts work. This is still the way it works in 8.1. The opposite to a commit cleanout is a delayed block cleanout. This is a cleanout done by the next reader of the block. Delayed block cleanouts do clear the lock bytes and generate redo (as well as marking the transaction as committed, and recording the commit SCN in the ITL). The delayed logging block cleanout feature delayed the logging of the redo for the cleanout until it could be logged in combination with the redo for another change to the block. It is this delayed-logging form of delayed block cleanout that no longer occurs. The redo for a delayed block cleanout is logged immediately, and the block is marked as dirty so that it will be written back to disk.|
|If fast commits do not clean out the lock bytes on changed rows, and only mark the transaction as committed, then when do they get cleaned out?|
|Yes, the lock bytes remain dirty indefinitely until the block is fetched again in current mode and changed. The lock bytes can safely be ignored by consistent read gets because the (last) transaction is known to have been committed.|
|Copyright © Ixora Pty Ltd||