Re: Solution for 01555

"vlad" <bulk_at_sfatcu.com> wrote:
> I've been reading up on Oracle lately and find their multi-version
> concurrency model very intruiging. One of the downsides of this model,
> though, seems to be the dreaded "01555 snapshot too old" error. It seems
> to me that this something Oracle should have handled this problem
> internally so that users never get that error.

That's what I thought when I first discovered 1555's. But as I don't run into them nearly as much anymore, I haven't thought a lot more about it.

> I would propose something like this:
>
> Let's say we are about to overwrite some undo data.
> 1) Get the SCN of block whose data we're about to overwrite in the
> rollback segment.
> 2) Compare to SCN of oldest active transaction.

Here you need the oldest active transaction in a very broad sense, including transactions having only queries. I don't think the database at large is even aware of a transaction until it actually changes something and writes undo, so Oracle would have to add functionality so that all transactions register themselves somehow at the point they begin. And then deregister themselves when they end. And PMON or something would have to determine if the other end of the transaction disappeared, and deregister on it's behalf. (Of course, it has to do something like that already, but this would magnify the impact).

(I think in the case of a read-committed transaction, the transaction itself wouldn't have to register, but every query within it would.)

This registering and deregistering could have a big performace impact.

Just figuring out the oldest active SCN every time might also have a big performance impact, so splitting it like this might improve performance:

1. get a segment known to be good to overwrite based on a cached last known oldest active SCN.
2. If there are none, re-compute the oldest active SCN, cache it, and try again with this.

> 3) If smaller, OK to overwrite. Otherwise, cannot overwrite. Must expand
> rollback segment and continue writing there.

What happens if you cannot extend the rollback segment? I'd rather have a rogue long running query get tossed, than bring all changes to a halt. (Unless the query was mine and all the changes were someone else's :)).

> I would think that data needed for an active transaction's read
> consistent view should be just as holy as undo data that is need to
> rollback an uncommitted transaction. Don't touch it until you make sure
> no user will ever need it. Why couldn't something like this be done to
> avoid all the grief about "snapshot too old"?

I haven't yet identified a reason it couldn't be done, but at the least it would certainly be a lot of work and impose quite a bit of additional overhead.

And then it would become a Galaxy Edition, and no one would be able to afford it.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB