søndag den 17. februar 2008

Undo the redo or redo the undo!

Very interesting chapter 9 in Tom Kytes book. I knew about the undo and redo feature, but there is definately some issues that are very confusing about this stuff. See my previous post for an example of that. In actuality, my question was about redo for undo in regards for a temporary table, this I got an answer on after a lot of debate during the nerds about recovery issues with redo and undo. This answer also had a challenge, but unfortunately I accidentally deleted the mail so I don't have the answer nor the challenge. However, I have requested a copy from the writer.

Another interesting issue was Delayed Block Cleanout causing an ORA-01555: Snapshot too old.
I was send to look at this excellent metalink note: 40689.1

It really spells out the issue.

EDIT:
I got the mail, the challenge was to describe why Delete generates more redo than an insert in a global temporary table. This is very nicely explained in this chapter. In short the reason is that GTT does not generate redo, the only redo that is made is the undo blocks that when they change will be added to the redo. So GTT only generates undo. Important fact to remember here!
So the asnwer is: Since an Insert hardly creates any undo, but lots of redo, the GTT has very little redo for this operation. (mostly stuff like adding a new extent to the segment).
Since a Delete has lots of Undo, and little redo, then a GTT will have lots of redo (as a result of the undo amount), in fact the redo-size for both permanent and GTT is very much alike due to the fact that the delete is using mostly undo, and hardly any redo ( besides the undo-redo) and thus must be similar in redo-size.

Ingen kommentarer: