Re: ORA-01555: snapshot too old: rollback segment number 7 with name "R05" too small

"Daryl Peh" <daryl-bc_peh_at_hp.com> wrote:

>Hi, I am currently running 5 stored procedures concurrently every night
>and have been getting this error message a few times a week.
>Sometimes it happens, sometimes it doesn't.
>Can anyone help explain what this error message is about.
>It can't be the rollback segment is too small as the DBA confirm
>that the rollback segment is actually very big.
>And none of my stored procedures is updating the same table.

Hi Daryl,

to get this error it is not necessary that your procedures update the same data (if they did, they would actually be executed in serial because of concurrent lock attempts). It is sufficient that one procedure updates data and another procedure is reading the data in a very long running query.

The reason for this behaviour is as follows: Oracle provides read consistency on the statement level (you could also have it on the transaction level with session paramater 'serializable' for transaction isolation btw), i.e. a query sees data always as it was at the point in time when the query started.

Now consider the following example:

At point t1, procedure p1 starts a transaction by updating some data. At point t2, procedure p2 starts a long running query (eg a simple select cursor loop on a big result set).

At point t3, p1 is still updating data or doing something else such that p1's transaction is still going on. p2 still reads. This is not a problem: although both may be operating on the same data, p2 will read its "private copy" of this data from the rollback segments where the 'before image' of the data from p1's transaction has been saved, because p1's changes are not yet commited (i.e. although p1's changes are already in the block buffer cache or may even have been written to the data files already, they do not have a new SCN yet), so this is just what the isolation level 'read commited' provides.

Now at point t4, p1 commits. The consequences of this commit are that the log buffer is flushed immediately, the transaction entry for p1 gets a new SCN (=System Change Number or System Commit Number) and also = (this
is where the problem starts) p1's rollback segment entries are marked as inactive by Oracle. p2 still reads data, and it still reads from the rollback segments (did I mention p2 was indeed a *very* long running query ;-)).

Now at point t5 other transactions may be active on the database, or p1 could start a new transaction, which means that new rollback entries will be made. As long as there is enough space in the rollback segs, this is ok. However when no more space is left, Oracle will not extend the rollback segment if it is possible to reuse extents that are inactive. Now the data that provides the read consistent image to p2 is overwritten by new rollback entries (ie the rollback segment wraps around) and the next fetch from p2 will raise 'ORA-1555'.

Sorry if this has gotten a bit lengthy, but as you see it's not quite trivial. When I first hit that problem I also felt like hitting a wall, but after rereading the docs several times, I started to understand the term 'multiple version read consistency' for the first time (well at least I think I understand it; hope what I wrote makes sense to you ;-). You should check Oracle Server Concepts Guide, Ch1: Data concurrency and consistency).

What you can do to get around this problem:

• First thing to check is whether you have any PL/SQL code with a commit statement inside a cursor loop. This is considered bad programming practice, because it will cause such problems much more frequently: the procedure will be likely to overwrite its own read consistent image for the cursor in the rollback segments because of the commit. (Actually, if the cursor is 'for update', Oracle will not allow this at all, but will immediately raise a 'Fetch out of sequence' error. So you could try to commit outside of the loop; or if you really need to commit more frequently, close the cursor and reopen it at the appropriate record.
• Try to make the long running queries run in a shorter amount of time (ok, this may be difficult sometimes).
• Create a particular, very big rollback segment that is dedicated to batch jobs doing updates and assign it to update transactions with the 'set transaction use rollback segment rb_large' command; this will decrease the likelihood for wrap-arounds.
• Try to schedule update jobs and jobs that perform mostly large queries some time apart.

HTH, best regards and a happy new year, Peter

--

Peter Schneider                     --- eMail ---
Systems Developer RDBMS             Private: pschneider_at_knuut.de
Unternehmensberatung Hoffmann       Job:     pschneider_at_ubhoff.com
Web Site: http://www.ubhoff.com              pschneid@debis.com