Re: understanding rollback segments

Hi,

...SORRY.....SORRY.....SORRY.....SORRY.....SORRY.....SORRY..... The reason no-one could explain the strange behaviour of my rollback segments was that there was a bug in my PL/SQL block. But read on... there's still some interesting unanswered questions.

First, the bug: recall that the approach was to execute a PL/SQL block which selected rows from one table and inserted them into another using the following logic to perform a COMMIT after every 1000 rows:

           cnt := cnt + 1 ;
           if cnt > 1000 then
              commit;
              cnt := 0;
           end if;

My problem was that the rollback segment continued to grow, whereas I expected the commits to release the space in the segments for reuse and couldn't understand why this wasn't happening.

The bug was that I hadn't initialised cnt at the start of the block, so it assumed the initial value NULL ( instead of 0 ). But NULL + 1 = NULL, so my value of cnt never reached 1000, so I wasn't doing any commits, so the rollback segment had to extend to hold the uncommitted data.

Now that I've fixed the bug, the PL/SQL works as expected and I can copy large tables without needing large rollback segments. Other solutions would be to use the SQL*Plus COPY command, which does exactly what my PL/SQL block was intended, or as andrew.nelson_at_astramerck.com said, in Oracle 7.3 you can:

     CREATE TABLE FOO AS SELECT * FROM BAR UNRECOVERABLE which is by far the fastest way to copy a table, since it doesn't create any rollback data at all.

Of course, if I had been inserting into or updating the same table I was selecting from, I would now be vulnerable to the "snapshot too old" error message, since the commits would be releasing the rollback data for overwriting which the select requires for maintaining a consistent image. The only solutions are to do one commit and live with needing a large rollback segment, or to redesign the PL/SQL block along the lines that Tim Witort <trw_@_medicalert.org> suggested, ie. repeatedly close the select cursor.

Now the outstanding problem:

Kelly Young <young_at_maricopa.edu> said:

> When you do a commit, Oracle assigns you another rollback segment for
> your next transaction. So you are continually going from one rollback
> segment to another.

This is true: Oracle tries to balance the load across rollback segments, but there's more to it than that. I believe that Oracle assigns each transaction to its own EXTENT of a rollback segment ( wrapping on to other extents for particularly large transactions ). The extents of a RBS are considered as a circular-linked list. In my case, with only a single RBS ( to simplify things ) with two extents, each commit would cause my PL/SQL block to switch to the other extent.

However, what happens if there are other independent transactions also using the RBS?

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.

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