Tuesday, March 14, 2006

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.

3 Comments:

Anonymous Anonymous said...

Nice Eric. We all appreciate you delving into this as I thought the 2X redo was a surprising result from the use of LOG ERRORS. Makes a lot more sense now. I'm sure Tom will want to incorporate this info when he gives this talk again.

3/15/2006 9:53 AM  
Blogger Eric S. Emrick said...

Hey Michael. I too found it to be very interesting. The redo size incurred resembles that of the SELECT FOR UPDATE followed by the "complementary" multi-row UPDATE when all rows in a table are being updated (via an FTS). First, the SELECT FOR UPDATE will lock the rows (creating redo records for the lock byte changes) and the subsequent UPDATE will update the row data.

3/15/2006 2:01 PM  
Anonymous Anonymous said...

Nice post. If you don't mind my asking, what kind of log file were you looking in? Was there a particular event you set to cause this information to be emitted?

Also, some related trivia ( trivial until it happens on your own system ) - in rare cases it's possible via user-thumbs to cause an Oracle form to issue a statement like this:

select [columns]
from [table] W
for update of [column(s)]
nowait;

The W appears in this type of statement to be a truncated where clause that is treated like a table alias.

This is happening on one of my systems from time to time - I have seen 5gb of redo generated by a 25 million row select, which is about 220 bytes per lock.

It's easy enough to recreate. Rolling back is an adventure, it takes longer than the locking.

Thanks.

8/25/2007 2:18 AM  

Post a Comment

<< Home