Re: rollback segment too small

Njål A. Ekern <n.a.ekern_at_usit.uio.no> wrote in article <35988E33.7C4A_at_usit.uio.no>...
> I think that explains it. I read somewhere that you may erronously
> receive a snapshot_too_old-message when your job is doing many small
> updates and commits. You have big rollbacksegments, try committing as
> few times as possible. Start to test commit after every 1000. If that
> goes well then try 10000. If that returns ORA-1555 then it's a genuine
> one, go back to commit after every 1000.

No, it's not erroneous, it's how rollback segments work. The original poster is updating the same records that he is updating. The select needs to return a read-consistent view of the data, and so has to rebuild the original data content from the pre-images stored in the rollback segments. If you perform frequent commits, the pre-images in the rollback segments are released for overwriting more frequently, thereby increasing the likelihood of a "snapshot too old message" ( which means that a required pre-image had been overwritten ). Your solution of reducing

the commit frequency is correct because it decreases the likelihood of "snapshot too old", but then you need bigger rollback segments to hold all the uncommitted pre-images.

The best solution is to restructure the processing completely to avoid the need to have a long-running select which selects the same records which are being updated.

Dave.
--
To reply, please remove the no-spam bit from my email address Received on Tue Jun 30 1998 - 03:31:07 CDT