Redo log sequence
In a single Oracle instance, the redo log views everything as a sequence with one change after the other. Only in a RAC setup can you have multiple instances of the same database doing things simultaneously. Outside of recovery we are not too fussed with the redo log.
Concurrent Activity
Individual Oracle sessions can make changes to separate data blocks concurrently, though you are limited by the number of CPU cores (because only the CPU can actually process instructions to read or change a block). You can even configure multiple DBWR processes so that you can write multiple blocks to disk at the same time. Again, you might be throttled by the hardware of spindles and disk heads.
System Change Number
Bridging the gap between the redo log and the 'parallel' activity of the database is the SCN. Every commit increments the SCN, plus it gets incremented every three seconds even without a commit. The LGWR also writes at least every three seconds. Coincidence ?
Flashback Query
If your transaction performs three inserts then all three inserts may be under the same SCN or they may be under different SCNs depending on the activity in other sessions, and the time taken between those inserts.
Since you can't match an SCN to points in a transaction, Oracle sensibly decided to keep things simple and return the COMMITTED state of the data as it was at the SCN. That was the key for my Challenge question earlier this month.
In some ways, you can view a flashback query as a very lightweight autonomous transaction that only permits reads.
Undo sequencing
Within a transaction there is a sequence of changes, and you can 'navigate' that sequence using SAVEPOINT and ROLLBACK. This navigation walks back through UNDO to reverse the sequence of operations within a transaction. SAVEPOINT doesn't affect the SCN, so flashback can't be used to look at a table as it was when a SAVEPOINT was issued. All you can do is rollback to a savepoint, and once you've done that you can't rollforward again.
In theory you could walk back through the undo blocks to see everything the transaction did. If you could do that, you'd also be able to tell what records are locked by the transaction. But I haven't heard of any tools that support this in practice. There's no SQL to access undo directly, and the undo blocks are not necessarily written to disk as you'd find in a redo log, but nor are they guaranteed to still be in the SGA (though they would be in one or the other).
2 comments:
Just a couple of add-ons:
Redo log sequence:
They are not strictly sequential. Piggyback commits - effective since release 6 although the release 7 guys say only in 7!- allow multiple changes per redo log entry. This allows multiple transactions to share the same redo log block and commit entry.
Undo sequencing: you cannot walk back the sequence of changes even in theory. The undo does not keep a sequence of changes, it keeps only the original block before ANY change of active transactions. ALl the Undo serves for is to re-position data as it was at the start of the transaction. You only need one copy of the original block for that. However as you noted, SAVEPOINT creates another copy of the block, halfway through a transaction.
Thanks for the extra.
The redo log bit sounds like a more efficient usage. Mostly my simplification is sufficient.
I've corrected my thoughts about UNDO. Again it is more efficient not to retain intermediate changes. I need to work out statement level rollback though as a large update may fail and need to rollback the statement's changes but not go all the way back to the transaction start or a formally declared savepoint.
So at the start of the transaction, a row value is 'RED'. First statement updates it to 'BLUE' and the undo only holds the 'RED'.
Second statement updates it to 'GREEN'. 'RED' is in UNDO in case that second statement fails.
If it succeeds and a third statement sets it to YELLOW, then GREEN is in undo at least until the third statement succeeds, RED is in there as the original value and BLUE doesn't exist (except in the redo log).
Post a Comment