Oracle Scratchpad

September 2, 2011


Filed under: Infrastructure,Oracle,undo — Jonathan Lewis @ 5:15 pm BST Sep 2,2011

A question that comes up from time to time on the OTN forums is the one about how much undo do you generate when you insert or update a row. It’s not a question that has an immediate answer – it depends on what you’re actually doing, the amount of change you introduce, and the number of indexes affected. However, there are a few guidelines that will point you in the right direction – and the key is to keep an eye on the used_urec and used_ublk colummns from v$transactions.

Consider the example of inserting data: when you insert a row, you typically generate one undo record (used_urec) for the row, but when you insert many rows using an array insert Oracle has an optimisation mechanism on undo record creation that allows it to create one used_urec to cover all the changes you have made simultaneously to an individual block – so used_urec could be much smaller than the number of rows processed.

However, if you have indexes in place and are doing normal index maintenance on import, then each table row would require each index to be updated, so you would go back to one used_urec per table row plus one used_urec per index maintained per table row.

So, when you look at the “big picture” there’s no obvious correlation between rows inserted and undo generated — until you look at the fine detail of exactly what you’re doing, and whether any optimisations apply. (The details of the optimisation strategies available vary with the chosen insert mechanisms and with version of Oracle)

Just for reference, here’s a link to a little note I wrote some months ago about monitoring undo.


  1. Doesn’t the 11g “compress for OLTP” introduce increased UNDO too?

    Comment by Wilton Businessman — September 2, 2011 @ 5:29 pm BST Sep 2,2011 | Reply

    • That’s a good question – I’d have to test it, but the first question is “incease it relative to what?”

      Logically it shouldn’t make any difference to (at least some of) the possible insert operations because undo and redo are logical change descriptions, so the physical location shouldn’t make any difference.

      There might be some cases where inserting a row causes heap block compression and introduces some new tokens (that’s just thinking aloud, at the moment) and that would make that could make that row insert more expensive.

      Comment by Jonathan Lewis — September 2, 2011 @ 5:43 pm BST Sep 2,2011 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: