Hotsos 2006: Day 3 – Revisited

This is a short follow-up post to my Hotsos 2006: Day 3 post, thus the extremely creative name for this post :) In Tom Kyte’s presentation “Performance Impact of the LOG ERRORS Clause in 10gR2”, Tom’s testing revealed that a single statement multi-row update using the LOG ERRORS clause generates nearly 2x the redo when compared to other methods of updating the same data: row-by-row and array processing via anonymous PL/SQL blocks. As I stated in my previous post, I wanted to take a look at this to understand why 2x redo is generated to accomplish the same task.

I was able to reproduce Tom’s results using the examples taken directly from the presentation. The additional redo owed to the multi-row update statement with the LOG ERRORS clause results from the row locking “approach” taken by Oracle during the execution of the statement. In the row-by-row processing the rows were locked ("to lock" effectively means to update the lock byte in the row header) and changed in the same redo record. However, in the aforementioned multi-row update using the LOG ERRORS clause each row affected is given two redo records to accommodate the change. The first redo record is for locking the row and the second redo record is used to affect the column data and column length changes. A dump of the log file showed the following pattern for the multi-row update using the LOG ERRORS clause:

1. Change the lock byte of row header for row 0 to the index value for the ITL entry associated with the current transaction.

2. Change the column data and column length attributes to reflect the updated column data and width for row 0.

3. Change the lock byte of row header for row 1 to the index value for the ITL entry associated with the current transaction.

4. Change the column data and column length attributes to reflect the updated column data and width for row 1.
.
.

5. Change the lock byte of row header for row N to the index value for the ITL entry associated with the current transaction.

N. Change the column data and column length attributes to reflect the updated column data and width for row N.

A dump of the log file for the row-by-row update yielded the following redo pattern, "lock the row AND change the row":

1. Change the lock byte information, column data and column length attributes in a single redo record for row 0.

2. Change the lock byte information, column data and column length attributes in a single redo record for row 1.
.
.

N. Change the lock byte information, column data and column length attributes in a single redo record for row N.

What is rather interesting is that this "lock THEN change" method of “acquiring” locks is not necessarily specific to multi-row updates with the LOG ERRORS clause. I tested multi-row updates without the LOG ERRORS clause and the same redo pattern emerged; lock the row then change the row.

Moreover, suffixing the update statement in the row-by-row PL/SQL block with the LOG ERRORS clause also produced the, “lock the row THEN change the row” redo record sequence. It appears that Oracle’s code path is adjusted necessarily by the LOG ERRORS clause to affect this pattern for update statements, irrespective of the number of rows the update statement modifies. However, this code path is apparently available to other multi-row updates not using the LOG ERRORS clause.

So, how does this account for 2x redo? It has been my experience that on average redo records are around 250-300 bytes for small changes. Much of this space is owed to the administrative data such as SCNs, operation codes, flags etc. Even a small single byte change can generate a redo record of 200+ bytes. In each and every test case I ran the redo record size for the single lock byte update was 208 bytes. If each row updated via an update with the LOG ERRORS clause created two redo records, one for the lock byte update and another for the small data change, this explains why nearly 2x redo is generated.

One point of clarification, the notion that Oracle locks a row before it changes a row is very fundamental. Indeed, our applications rely on it! However, it does appear that Oracle catalogues the changes owed to acquiring the row locks differently based on the task at hand.