Re: why aren't ORA-1555 errors MORE frequent?

"Richard Kuhler" <noone_at_nowhere.com> wrote in message news:U5gC9.15580$%k2.4376213_at_twister.socal.rr.com...
> I was reading Oracle's NOTE 40689.1 about "delayed block cleanout" (also
> available in the asktom.oracle.com article "snapshot too old error"). I
> feel like I'm missing something because I'm left wondering why ORA-1555
> errors aren't far MORE frequent than they are. For instance, what keeps
> ORA-1555 happening in this simple scenario ...
>
>
> 1. A process updates blocks in a table, commits and terminates (delayed
> block cleanout is used)
>
> 2. The blocks in the table are not read again for a long enough time
> that the pertinent Rollback Segment Headers are overwritten
>
> 3. A new process queries those blocks
>
>
> Since the Rollback Segment Header entries that the modified blocks point
> to have been overwritten, there's no way for the query to know whether
> the changes were committed before or after the query started, right? So
> Oracle must return ORA-1555, right?
>
>

Hi Richard,

Note quite.

When the query starts, it takes a snapshot of the current SCN (lets say 1234). It can only look at data that is consistent from this point of time.

When the query comes across a block that has not been cleaned out, quite correct, Oracle needs to determine if the changes that were made have been committed or not. Therefore, using the RBS documented in the block header, it goes to determine this via the transaction slots in the header of the RBS. If it can't find the details in the header (because as you said it has been overwritten ages ago), it attempts to reconstruct the header in order to find the appropriate slot (note these changes are recorded in the RBS itself).

If after rolling back the header as far as it possible (ie. all header changes currently recorded in the RBS itself) it still can't find the appropriate header, it might appear we have a problem. However in this example we don't. Why, because Oracle takes note of the latest SCN that it could reconstruct in the header (lets say 1100). Therefore, we know *for sure* as the query SCN snapshot is 1234, all changes made to the block *must* have been committed prior to 1100. We don't know when the change was committed, but it must have been committed prior to 1100.

Therefore the query is hunky dory.

However, if the query started again at 1234 but there are heaps and heaps of cleanouts occurring on this table during the running of the query resulting in heaps and heaps of changes to the RBS then it could be these *current* cleanouts that are causing the problems. Now when we eventually visit the RBS, all the header details can only be restored to 1240, due to the cleanout activity. Therefore we can't be sure of the consistency of the block (it may or may not have been changed during this time). In this case, a 1555 would result.

So it's the cleanouts that occur *during the running of the query itself* that can cause issues.

Make sense ?

Cheers

Richard
> Thanks,
> Richard Kuhler
>
Received on Mon Nov 18 2002 - 21:42:11 CST