Rollback 1555 Oracle

ORA-1555s : Causes and Prevention

Administration Tips

ORA-1555s : Causes and Prevention In order to understand what a "1555 - Snapshot Too Old" error message really means, and what you can do to stop them happening, you first have to know about Oracle's model for concurrent access to the same data, and then how rollback segments actually work.

Concurrency If I change Bob's salary from, say, $500 to $700, how do I stop you making a simultaneous change of his salary to $750? Easy: we take exclusive row-level locks on his record, and if I've got the lock, your update simply can't proceed until I release it (by committing or rolling back my transaction). That's one writer blocking another writer. What if all you want to do is to see what Bob's salary is? Does my write block your attempt to read the data? In some databases, it does -but not in Oracle. Oracle does not allow writers to block readers (nor readers to block writers, actually). You will therefore see Bob's salary in your report -but which one? The old $500, or the new $700? The answer is that you are not allowed to see anything which wasn't fully committed at the time you started to query the data. Since my update has not yet been committed, you may therefore only see a salary of $500 for Bob. Even if I committed the record at 10:02, and you started your long-running report at 10:00, you wouldn't be allowed to see the new salary -because it wasn't there at 10:00 when you started your query. This is what is known as read consistency. Oracle will allow you to read data, consistent at the time your query began. But how does Oracle allow you to see Bob at $500 when my update has actually changed the data in the Buffer Cache (and conceivably on disk as well, if DBWR has decided to flush in the meantime)? The mechanism it uses is the rollback segment. My update caused a before image of Bob's salary (in this case, $500) to be written into a rollback segment block. When your query is told that it cannot read Bob's current salary (i.e., $700) because it was changed after the time you started your report, your server process consults my rollback segment block to obtain the version of Bob's data as it was, consistent with the time of your query. Preparing a read-consistent image of the data in this way allows you to see all the data you requested, regardless of what I am doing to it at the same time. And preparing your read-consistent image of the data requires that you should have access to my rollback block.

Copyright © Howard Rogers 2001

10/18/2001

Page 1 of 5

ORA-1555s : Causes and Prevention

Administration Tips

How Rollback Segments Work Rollback Segments are comprised of a number of extents, just like any other segment would be. But what distinguishes them from other segments is that when all their current extents have been used up, instead of acquiring more extents as a table would day, they go back to the first extent and start over-writing the earlier data. They'll be prevented from re-using this earlier space if, anywhere within the earliest extents, there is rollback generated by a transaction that is still active (i.e., one that hasn't committed or rolled back yet). In that case, they are said to be 'blocked' from reusing their extents in the normal cyclical manner, and instead have to acquire new extents to allow new transactions to proceed. But unless there is such a blocking transaction present in the original extent, they really will just over-write the contents of those first blocks of the segment, obliterating the old, expired rollback as they do so.

The cause of 1555 Snapshot Too Old errors Now put the two preceding sections together. Your select for Bob's salary must be able to read my rollback block. But if I have committed my transaction, and there are no other active transactions in the same extent as my block, there is nothing to stop the rollback segment over-writing my rollback block with new rollback data generated by fresh transactions. I certainly don't need the data it contains -I've long-since finished my update. What we've forgotten about, though, is that your report still needs it. When your report comes to generate its required read-consistent image of Bob's salary, it may well discover that the rollback information it needs (i.e., from my old rollback block) has been over-written by newer transactions as they re-use rollback blocks. At that point, your report -because it simply cannot now work out what Bob's old salary used to be- will fail with the infamous ORA-1555 Snapshot Too Old error. It means a block which contained useful data for a report has been obliterated by subsequent transactions. It's somewhat unfortunate that Oracle appears to 'forget' that rollback might have other uses than just allowing me to roll back my update, but that's the way it is (at least, it is until you get to Oracle 9i, where you can instruct Oracle to retain a transaction's rollback for any specified period of time after that transaction has committed, in case it's needed by another User for a read-consistent image). To be fair to Oracle, however, this problem is highly unlikely to arise unless you are running large and lengthy reports against a database which is also undergoing many frequent, small transactions. In plain English, you'll encounter the problem when you try and make an OLTP database run the sort of reports that belong in a Data Warehouse or DSS Copyright © Howard Rogers 2001

10/18/2001

Page 2 of 5

ORA-1555s : Causes and Prevention

Administration Tips

system. Unless you must try and make a gazelle-like database perform as though it were a plodding elephant, the standard advice is: run big reports out of hours, when the OLTP stuff has died down a bit. That way you are unlikely to encounter the 1555 error.

Preventions and Cures However, if you are forced to run big reports in the middle of an OLTP day, there are a number of things you can do to try and prevent 1555s. Firstly, the problem really arises because the rollback segment has filled up all its extents, and has therefore returned to the first one and started re-using it. If instead it had lots of other extents to move into before needing to return to the first, the over-writing would take place later -and potentially, you'd be able to get your read-consistent image generated before my block got re-used. So adding more extents to a rollback segment will, at the very least, reduce the frequency with which you'll encounter 1555s. But actually, given that a proliferation of extents for any segment is not a particularly good idea, you can achieve the same effect with the same number of extents, provided each existing extent is bigger. In other words, you can prevent 1555s simply by dropping the rollback segment, and re-creating it with the same number of extents, just bigger ones. Bigger extents contain more blocks, hence it takes longer for the segment to fill them all up -and hence it takes longer before it has to return to the first extent and start re-using it. That extra time is hopefully all you'll need to be able to generate your read-consistent image. In short, size is everything. Make your rollback segments bigger, either with extra extents, or by using the same number of extents, only bigger ones, and 1555s might be a thing of the past. I emphasise the words "might be" there. There are no guarantees. However big your rollback segments are, it's possible that a sudden burst of OLTP-like transactional activity could waltz its way through your entire rollback segment at a rate of knots -and provided the extent in which my completed transaction has no other active transactions, we'll be back in the business of over-writing my old rollback block before you need it. But that last qualifier provides a hint as to how we might absolutely, 100% guarantee never to have another 1555: if we could somehow arrange for the usual "re-use of earlier rollback" to be prevented, then required rollback could not be over-written. Now re-read the "How Rollback Segments Work" section, and you'll see what I'm driving at: blocking transactions prevent the re-use of old rollback blocks. So a permanent cure would be to arrange for your report to first raise a simple transaction (say, 'insert into dummy values (1)', and to leave that transaction uncommitted. Your Copyright © Howard Rogers 2001

10/18/2001

Page 3 of 5

ORA-1555s : Causes and Prevention

Administration Tips

report can then chug along doing its stuff, taking all day if it really wants to: because that first dummy transaction remains 'active', the extent in which it was placed, and all others after it, can never be re-used. When your report has finished its thing, it can simply issue the command 'rollback' -which then frees the extents it was protecting for normal re-use. But that only happens once your report is safely finished. There's only one slight drawback to this approach. You can't be sure in which rollback segment crucial read-consistent data might have been placed by their parent transactions. If your report is to be guaranteed of success, you need to protect all possible rollback segments. That means you need to raise the 'insert into dummy' transaction once for each rollback segment. That's not necessarily a problem in itself: but what you need is a way of guaranteeing that each such dummy insert is placed into a separate rollback segment -and the only way to guarantee that a transaction uses a specific rollback segment is to use the 'set transaction use rollback segment BLAH;' command. And the problem with that is that it must be the very first line of the transaction. So? Well, this wouldn't work: SET TRANSACTION USE ROLLBACK SEGMENT

1;

INSERT INTO DUMMY...; SET TRANSACTION USE ROLLBACK SEGMENT

2;

INSERT INTO DUMMY...;

...AND

SO ON

This is just one transaction (started by the first insert), since transactions don't end until a commit or a rollback. That means the second 'set transaction' statement is blythely ignored. What all this boils down to is that for this trick to work, you need to create as many sessions as you have rollback segments, each of which simply issues the 'set transaction' command for a designated rollback segment, followed by an 'insert into dummy' command. That's not too bad if you have maybe 10 or 20 rollback segments. But if you have dozens and dozens of them, because of the high number of concurrent transactions you normally experience, this is going to be quite a painful technique -the idea of 50 or more sessions being created just to do a one-line insert is a bit suspect, frankly. However, if you need to guarantee no more 1555s, and you haven't upgraded to 9i, this is about the only way you can do it. I take no credit for the technique. Steve Adams devised it, and his web site has the requisite scripts needed to make it work (see: http://www.ixora.com.au/tips/admin/ora1555.htm for a rather more technical explanation of 1555s, and details of, and scripts to implement, his workaround). Copyright © Howard Rogers 2001

10/18/2001

Page 4 of 5

ORA-1555s : Causes and Prevention

Administration Tips

If Steve's technique sounds too awkward to implement, then the best you can do is try to minimise the chances of 1555s as already described: make your segments larger, and avoid using OPTIMAL (which is a bad idea anyway), as OPTIMAL is designed to cause rollback segments to shrink in size -and as we've already seen, size is the key factor in determining how often you'll encounter 1555s. But best of all, try and do large, long running activities during quiet times wherever possible. --------------------------------------------------Note: 1555s are also very occasionally caused by something called a 'delayed block cleanout' that can happen even when you are the only person on the database, and there's not the slightest chance you've overwritten earlier rollback. It's not something you're very likely to encounter, except if you are performing some very long transactions, and I've chosen not to discuss it here as a result. Suffice it to say that the size of rollback segments is central to this issue, just as it is to the 're-use of old rollback' described above. If you want more details, see http://home.clara.net/dwotton/dba/snapshot2.htm where the issue is explained in some depth.

Copyright © Howard Rogers 2001

10/18/2001

Page 5 of 5