Misconception: “Snapshot too old” can be avoided by
using “set transaction use rollback segment.”
Jeremiah Wilton
It is often asserted that if you get the ubiquitous “ORA-01555: Snapshot too old: Rollback segment with name <RBS> too small,” you are “running out of rollback segment.” This misconception is partially the fault of the misleading error text. The rollback segment mentioned is not necessarily “too small.” This error is actually a failure to obtain read consistency.
When you perform a query, all results must be returned by Oracle consistent with the point in time that the query began. If other sessions change data between the time you begin your query and when you read that data, your session must obtain a “consistent read” of the data block using the data in the rollback segment that was generated when the other session changed the data. Since rollback segments are reused cyclically, and since Oracle can’t guess what you might need to read in the future, there is no way for Oracle to protect or preserve all the rollback entries your query might need over its lifetime. ORA-01555 occurs when the rollback entries you need for your query have been overwritten through cyclical reuse, or rollback segment shrinkage.
So, since ORA-01555 has to do with reading from the database, and transactions have to do with writing to the database, telling your session to use a particular rollback segment for a transaction that you are not even using will have no effect on the success of the query. You will notice that each time ORA-01555 is returned, it usually specifies a different rollback segment. That is because you have no control over which rollback segments the other sessions in the database have used, and therefore no control over where your select may need to obtain data to construct a consistent read.
The best way to avoid ORA-01555 is to tune the query to complete faster, so that the likelihood both of needing to generate CR blocks and of not having necessary rollback entries to do so, are both reduced. There are number of ways to accomplish this, including creating indexes, optimizing for full table scans (db_file_multiblock_read_count), and parallel query.
Alternatively, you can increase the size and/or number of rollback segments, which will make reuse of a section of rollback segment less frequent. You can also hunt down the sessions that are imposing a high rate of change on the database, and therefore rapidly causing rollback segments to be reused. These sessions can be tracked down by looking at v$sesstat for the largest user of the “redo size” statistic per period of time connected.
Finally, it is important to make sure that rollback entries are not being obliterated through unnecessary shrinks. Looking at v$rollstat can reveal the number of times a rollback segment has shrunken since instance startup. If they are shrinking much at all, then the ‘optimal’ size of the rollback segment is probably too low, and should be increased, taking into account available space in the tablespace and appropriate rollback segment size based on your understanding of the system’s workload. If you are frequently manually shrinking rollback segments to free up space in the tablespace, then you yourself may be the cause of the ORA-01555s.
In Oracle 9i, internally managed undo (rollback) is available as a new feature. This feature allows a minimum retention time to be specified for undo entries, so that DBAs can set a service level agreement on consistent read capability with users. The only problem with this model is that one job using an extraordinarily large amount of undo within the retention period can use up all available space for undo and ruin the party for everyone.