This is the final article in our Block Internals article series. It covers a couple of other issues you could have with long-running transactions. This article also covers how direct path loading works. Direct path loading doesn’t create a problem per se but could cause the dreaded Ora-01555 Snap Shot Too Old.
This article is really about how Ora-01555 happens and what you can do about it.
We have been talking about how Oracle manages transactions at the block level; i.e., the Interested Transaction List (ITL). This mechanism handles the row-level locking and I have already discussed what happens if there aren’t enough entries in this table.
Ora-01555 Snap Shot Too Old can be caused by one of three events:
Basically, anytime Oracle cannot get a read-consistent view of your requested datablock, you get this Ora-01555 error and your only course of action is to restart your work. Of course, you will get a new SCN (transaction ID) and if the error was because of a direct path load/unrecoverable transaction…you won’t have the issue now.
DML transactions are assigned an Undo tablespace segment. We used to call these rollback segments. They basically work the same, except the rules for cycling out the old data have changed to provide the point-in-time recovery mechanism introduced in Oracle9 I think…
When a DML transaction starts, it is assigned one of these segments. Sometimes they can auto-grow but then Oracle can auto-shrink them. To avoid the problem, I always used static-sized ones as this shrinking happened whenever Oracle wanted to do it without checking to see if the rollback segment was still in use, causing an Ora-01555 error.
Long-running DML can fill rollback segments/undo tablespace segments, causing an Ora-01555 error. With today’s Oracle databases, these segments are much larger but you, the DBA, still might have to allocate a private rollback segment and assign these long transactions to it. Sorry…the syntax is not at my fingertips but look up the UPDATE statement; there is a way to assign a specific SQL to use a specific rollback segment. This will help and this is what we used to do in yesteryear.
Be careful with the truncate command. It is quick because it just clears out the transaction table and resets the table to an empty state. If there was a transaction using this table, it will continue (as it was already running) until it needs a read-consistent view. Since the ITL info is wiped out, you get Ora-01555. Remember, anytime Oracle RDBMS cannot get a read-consistent view of the database, you get this error.
Code with shorter transactions in mind. Use private and large RBS for long updates. Manage when unrecoverable transactions occur (do not truncate or do direct path loads while users are on the system).
Direct path loading has been around for a while now. It works in conjunction with the high water mark…loading data blocks external to the Oracle kernel (i.e., unrecoverable), usually in a new extent as well. When the operation is complete, the data dictionary is adjusted to reflect the new data. Since there is no recovery information, there is no entry in V$Transaction, so if a reader hits this new data, their SCN will not match and there are no pointers to any rollback information, so the Ora-01555 error will be issued.
New empty blocks are allocated. These blocks get the direct path data; no recovery, no before image, no journaling. This is one reason it is very fast.
When the data has been loaded, the blocks are added to the table, and the data dictionary is adjusted to reflect the new size of the object.
It doesn’t really copy anything but there may be temporary segments that the SMON process will then clean up.
When the high water mark is moved, the direct path operation is over. If any currently running transaction asks for a read-consistent view of the data as it reads thru this new data, the undo information/ITL entries simply do not exist, because of how direct path works…so…you get the Ora-01555 error. You start again and now your SCN is higher than that of the direct path load (that had completed anyway) and you now use all of Oracle’s data block utilization, previously discussed in prior articles.
I hope you have found this series of articles useful in your knowledge of the Oracle RDBMS. I find it easier to code for certain performance issues/large transaction issues when you understand how the base technology works.
So…when designing…keep commit point processing in mind. The database likes frequent commit points. You don’t run into filling the undo segments, you don’t generally have readers and writers bumping into each other with very poor performance issues; it’s better when the database wants to check point for its recovery mechanisms.
Oracle ACE Director