Re: [Q] Rollback segments; Small vs. large extents.

Doug Surplus <102621.3152_at_compuserve.com> wrote in article <32c2b3ef.176977810_at_news.sedona.net>...
> the first statement in a transaction. Read only transactions can be
> set no using a rollback segment at all with 'SET TRANSACTION READ

        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Woah!!!

Doug, this is incorrect. People are often confused by this, so I will try to explain a bit.

You cannot turn of rollback segment usage by using read-only transactions. It's that simple. Rollback segments are used to enforce read consistency at the STATEMENT level. That is, during execution of a SQL statement, the database guarantees that the statement sees the table as it was before the statement was executed. Other users may modify the table without waiting (unlike Sybase, SQL Server, where the are blocked). The rollback segment holds that information for the statement. A READ ONLY transaction does the same thing, but for an entire transaction. That's all. It also works for distributed transactions, which is pretty cool in my book.

You can avoid rollback segment usage by using a DISCRETE TRANSACTION. There are some rules that go with this and you do assume a bit of responsibility, but you can avoid some overhead in special situations. The main restriction is that you can only modify a block one time per transaction. There is a pretty good discussion, with examples, of discrete transactions in the Application Developers Guide, Appendix A.

With respect to rollback segments, you can force a transaction to use a particular RBS with the SET TRANSACTION USE ROLLLBACK SEGMENT rbs_name; statement, but you cannot prevent another transaction by another user from using that same rbs. Therefore, your rbs has to be big enough to hold your transaction plus any others that might be assigned to it. Rollback segments are assigned in a round-robin manner.

Also, please note that most experts recommend that all rollback segments be the same size. This helps for performance. This suggests another reason that it is less that ideal to mix transaction processing and batch processing applications. A database ought to be one or the other if possible. Of course, this is not always workable, so we end up creating one or two huge, mother-of-all-rollback segments. But, please don't create an assortment of sizes, willy-nilly, in an attempt to cover all bases. Create the bulk of them at a size that will handle normal transactions for your users, all identical. Then, create one or two that are large enough to handle the huge reports or batch things or whatever.

Hope this helps.

Roger
snowden_at_synergy.net Received on Thu Jan 09 1997 - 00:00:00 CST