1. When should you create more rollback segments?
The number of rollback segments needed to prevent contention between processes can be determined with the help of the monitor rollback display and with the use of the v$waitstat table. The rollback monitor column “header waits/sec” gives an indication of the current rollback segment contention. Waits are a definite indication of contention. The following v$waitstat query will display number of waits since instance startup:
SELECT * FROM V$WAITSTAT;
Any non-zero value for ‘undo header’ in the CLASS column of “v$waitstat” indicates contention for rollback segment header blocks.
You may want to add a rollback segment when you know you have a large transaction.
2. Is PCTINCREASE for rollback segments always 0?
True.
3. What DML creates the most undo?
Delete and update will probably create the most undo. INSERT STATEMENT creates the least undo because oracle only needs to store the rowid.
The number of bytes required for storage of undo, or information that is needed in case of rollback, depends on two things: 1) the type of transaction being performed (insert, update, delete, etc.) and 2) the actual data being processed. In general, inserting a given record into a table generates less undo than deleting the same record, because while rolling back an insert only requires deleting the row, rolling back a delete requires reinserting that row. (It would take less bytes to store a rowid than to store information to reconstruct the actual row itself.)
Additionally, the undo generated for an update on a fraction of a row is not proportionate to the amount generated for the entire row, due to additional overhead required.
4. If you have a table with an index and you insert a row, does Oracle create undo for the index as well?
Yes. When inserting into a table with an index, there is considerably more undo created than when inserting into a table without an index
Here is an example:
Update emp set empno=15; when no one else is on the system.
In v$rollstat, these numbers were compared:
1,214,058 writes (no indexes)
4,688,328 writes (one index)
Update can cause a key to change, meaning Oracle is deleting from one part of the index and inserting into potentially an entirely different part of the index. If the block has to split then more undo is generated.
5. How does Oracle determine which rollback segment to use?
The rules are:
(i) always assign to rbs which has least number of active txns
(ii) if two or more rbs have the same “least number of active txns” , then assign to the one which is after the last one used. This ensures that undo is kept for a longer time.
6. Can a transaction span several rollback segments?
No. A transaction can span several extents but must remain in the same rollback segment throughout the duration of the transaction.
7. What do you record in the data block when DML occurs?
Here are the generic steps:
a. Pin the current version of the block in database buffer cache.
b. Write the change to the block in the buffer cache. This is the real change that was poited by DML.
c. Write the undo information to a redo record in the redo buffers.
d. Write the undo information to a block in the rollback segment.
e. Write the change to a redo record in the redo buffers.
f. LGWR writes out redo buffers at some later time. DBWR writes out the changed blocks at some later time. LGWR always writes out redo records before DBWR can write out the changed block.
g. When you commit, Oracle updates transaction table and record the SCN for that transaction.