Re: Rollback Segments !!!

On Tue, 5 Jan 1999, Sybrand Bakker wrote:
>
> Not necessarily, however most likely, every new transaction will start
> in a different extent of the rollback segment. As soon as an extent is
> filled a new extent will be allocated. A transaction will never
> overwrite read consistency data,as soon as this happen the process
> issuing the select (read consistency information is only generated
> with selects) will receive the famous ora-1555 snapshot too old error
> message. This means your rollback segment is too small.

On the contrary, transactions constantly overwrite data that could potentially be used for read-consistency in another query. The database has no way of knowing which blocks a query might need *in the future*. The only untouchable blocks in undo are those for which a commit has not yet been issued, thus freeing them.

The ORA-1555 is not necessarily the result of the rollback segment being too small (despite the error text). You can get this message if a query takes so long to complete that the necessary data in undo has been overwritten. You can also get it if someone else's large batch transactions use "set transaction use rollback segment...;" In that case, blocks required to complete a consistent read may be available in all segments except for the one supporting a huge transaction. In such a situation, the transaction is trouncing a small but necessary number of blocks required by the first person's query.

Another common cause of ORA-1555 is if OPTIMAL is set too low, and the segment is constantly growing and shrinking. The shrinks will blow away lots of freed blocks, and causing any session that needed them to raise ORA-1555. --
Jeremiah Wilton http://www.wolfenet.com/~jeremiah Received on Wed Jan 06 1999 - 10:12:42 CST