Re: understanding rollback segments

I posed the question:

>Suppose my (only) RBS has three extents. Suppose also that an entirely
>independent long-running transaction is using extent #1. My PL/SQL
>block first starts using extent #2, then after the first commit,
>switches to extent #3. After the next commit, it should then switch to
>extent #1. But this is still in use by the other independent transaction,
>so it can't use it. I *think* it will therefore allocate a new extent,
>even though extent #2 is theoretically available. Presumably it will
>continue to allocate new extents, leaving a trail of unavailable, but
>empty extents, until the other transaction finishes with the first
>extent. So even with my carefully written PL/SQL block, I could still
>potentially blow out the rollback segment?
>
>Does anyone know for certain if this is the case? I'll try some
>experiments and try to find out.
>

ok - I've done the experiments.

I was wrong about each transaction having its own extent in the RBS: there CAN be more than one transaction sharing an extent.

But I was right about the way rollback segments grow: Oracle will cycle round the extents of a rollback segment until it hits one that is already in use (by some other transaction). At that point, it stops cycling and starts adding more extents. So even if you've got a program like my (correctly written) PL/SQL block, which is doing frequent commits it can still result in runaway rollback segments.

I proved this by creating a small rollback segment ( INITIAL 5K NEXT 5K ) and made it the only online RBS ( other than the system RBS ). I then ran my PL/SQL and monitored the RBS using

      select * from v$rollstat;

It quite happily stayed at 2 extents, whilst my PL/SQL copied data into my target table and commited it. I let it run for several minutes until I could see all was working well. Then I started another transaction ( a simple one record insert into another table ) which I didn't commit.

Immediately, the number of extents in the RBS started to grow. Then I committed this second transaction. Immediately, the number of extents stabilised. I repeated the exercise: each time I had some other uncommitted transaction, the rollback segment started to grow. As soon as my PL/SQL was the only transaction running, the rollback segment stopped growing.

It looks like there is no easy way of stopping the rollback segments from growing using just programming, unless you can be sure that yours is the only transaction on the entire database.

Dave. Received on Mon Apr 28 1997 - 00:00:00 CDT