Oracle - How does transaction, rollback segment and the undo_retention parameter work?

Question

I'm no DBA, and I'm having a bit of a hard time understanding Oracle's transaction management process.

From what I understood by reading some reliable-looking pages on the Internet (most notably this AskTom note -- but don't bother with the comments), when a transaction is commited, the new data is not reported on the actual data block yet, but stays logged on the rollback segment. When someone issues a SELECT on the data, or when UNDO_RETENTION seconds have passed -- whichever of these two events happens first --, the new data is then (and only then) written on the data blocks.

But someone in our company, supposedly in the know, recently told me the opposite : according to him, when a transaction is commited, the new data is immediately written on the data blocks, and the rollback segment/undo tablespace keeps the old data for a duration of UNDO_RETENTION seconds. This old data stays available during this time for access by queries launched on SCNs prior to the transaction.

So, what really happens inside Oracle, and can you provide references to back up your reply ?

We're using Oracle 9.2.0.8.

Thanks in advance.

Answer 1

Lots to cover here! The person in your company is essentially right, except that the changes are written to the data block in memory as they are made, even before the commit; and they are written out to disk entirely independently of when you commit (possibly before, possibly after, never as part of the commit operation).

1) UNDO_RETENTION has nothing to do with when your changes are written to the data block, either in memory or on disk. UNDO_RETENTION controls how long the data necessary to undo your change persists AFTER you commit the change. The purpose being that other queries or serializable transactions that started before your commit may still want that data. Reference: http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm#sthref1477

2) When you do an update, the data blocks in memory are modified. They may or may not be written out to disk (even before you commit, I believe); this is done by a background process. Also, redo information is written to the redo log buffer. Undo is generated and stored in an undo segment.

3) When you commit, Oracle makes sure that your redo information is written to disk, and marks the undo data as committed. But it does not write the changed data blocks in memory to disk, nor does it go back and mark each block as committed. This is to make the commit as quick as possible. Reference: http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/transact.htm#sthref628

4) The data blocks in memory will be marked as committed either when they are written out to disk by the background process, or the next time they are used (by a SELECT or any other operation). That's what the AskTom note is discussing. This is not about whether your changes to data are written to the block; it is about whether they are marked as committed in the block itself.