Re: Why Backup Temp and Rollback segs?

On Mon, 19 Oct 1998, M. Bhatti wrote:
>
> OK, slow down, slow down... I'm confused now. What happens in the event
> of a "snapshot too old" error? If the transaction has changed the data,
> how do you rollback to your original state in this case?

I'm not sure what you mean. ORA-1555s are encountered during a query. That query may incedentally be part of an update, but it only occurs because the query could not obtain a consistent read of data with respect to the point in time when the query was issued.

Here is a blurb that I wrote on this topic:

ORA-01555: snapshot too old: rollback segment number %s with name "%s" too small

Problem: You are running a query that (for whatever reason) takes a long time to complete. One or more of the tables you are querying is being changed by other users while you perform your query. After some time, you receive the ORA-01555, and your query fails.

Explanation:

When you perform a query, Oracle knows the point in time (called a system change number or SCN) at which your query began. In order to give you a consistent set of data, Oracle needs to show you the data the way it looked exactly when you began your query.

Meanwhile, people are changing the underlying data being selected by your query. These other transactions are actually changing the data on the datafiles. As a part of their transaction, they record the data in the rollback segments the way it looked before changing, in case they need to perform a rollback before they commit. This data comes in handy for you, because it lingers around in the rollback segments even after the other transactions have committed. While your query is scanning through a datafile selecting data, it might come upon a block of data which, according to the block header, is newer than the query you are performing. At this point it checks the ITL (interested transaction list) in that block's header, which tells the query where to look for the data in the rollback segments. Usually it finds it, and moves on.

The problem is that Oracle doesn't know what your long-running query might need in the future. In a very transactional environment, the rollback segments are changing constantly, and fairly rapidly. It is possible that the piece of old data that you need from the rollback segments will have already been overwritten by some other transaction before you get to it. When your query encounters this situation, it raises an ORA-01555 and fails. It is unable to provide you with the data you need from the point in time that you issued your query.

Solutions:

1. Avoid querying rapidly changing data over a long period of time. If you have a query that is going to run for a long time, and which selects a long time period of data (such as a month), Try segmenting your query into two parts: Everything but the past four days, and just the last four days. Remember that in a query that selects a whole month, you spend hours grinding through the fairly quiescent data from the first part of the month, and only much later get down to the newer stuff (which, incidentally, is changing heavily). Segmenting in this way could very well solve your problem.
2. Increase the number of rollback segments, in hopes that you will get lucky and nobody will overwrite the data you are planning to select somewhere down the road. This isn't a very good solution, because it probably won't work on a very transactional database.
3. Maintain a read-only snapshot database instance in which certain tables from the OLTP instance are refreshed at some interval. This would be useful, but implementation is a project by itself.
4. Tune the queries to complete faster, reducing the possibility that data will change out from under the query, and eventually become unavailable.

--
Jeremiah Wilton http://www.wolfenet.com/~jeremiah Received on Mon Oct 19 1998 - 12:46:15 CDT