DBA FAQ 4 - Rollback Management

From: Scott Tiger <kml_at_cellar.org>
Date: 26 Mar 93 14:06:59 GMT
Message-ID: <cqV11B7w164w_at_cellar.org>


DBA FAQ
(Frequently Asked Questions for/from Database Administrators) by Kevin M. Loney, aka kml_at_cellar.org (username "Scott Tiger")

Third in a Series.

Part 3. Rollback Segment Management

This is several pages long, you may want to just print it out and read it offline.

Q: How can I best manage my rollback segments? How can I drop

        public rollback segments?  How can I tell how much rollback
        space a transaction takes?


Monitoring Rollback Segments



In DBA FAQ, Part 2, I gave this script that allows you to quickly see the status of your rollback segments:
        SELECT
                SUBSTR(DS.SEGMENT_NAME,1,22) R_SEGMENT,
                SUBSTR(DS.TABLESPACE_NAME,1,20) TABLESPACE,
                DS.BLOCKS,
                DS.EXTENTS,
                DRS.STATUS
        FROM DBA_SEGMENTS DS,DBA_ROLLBACK_SEGS DRS
        WHERE DS.SEGMENT_NAME = DRS.SEGMENT_NAME
        ORDER BY 1;


How do you use this information?

Well, for starters understand that fragmentation in rollback segments is not a bad thing. Each new transaction in a rollback segment will start in a new extent of that segment. I have found that the best setup for managing them is one in which every extent is the exact same size (PCTINCREASE 0). This allows the rollback segments in the tablespace to reuse dropped extents while limiting their maximum size.

In VMS, no Oracle object can take more than 121 extents. So if my rollback segments are created with an initial extent of 1 Meg, and a next 1M, PCTINCREASE 0, then anytime a transaction or group of transactions tries to exceed 121 Meg, the offending statement will fail. Thus, the script above will show you two important things: the number of extents, and the amount of space allocated.

Ideally:
1. each transaction will fit in one extent of a rollback segment. 2. all non-system rollback segments will be located outside of the

        SYSTEM tablespace
3. all rollback segments will be of equal size

NOTE: #2 seems hard to figure out at first. After all, you have to have a second rollback segment in SYSTEM before you can write data to non-SYSTEM rollback segments. However, once you have done that, you can create a tablespace called ROLLBACKS (eg) and create rollback segments there. Once you have done this, you no longer need the non-SYSTEM rollback segment in the SYSTEM tablespace. No other objects should be created in the ROLLBACKS tablespace.

NOTE: #3 is important because it means that any given transaction, when running, will have a good chance of running without dynamically extending the rollback segment it resides in (can you say "recursive calls"?), improving its performance and its chance of succeeding.

Monitoring Rollback Area used by Transactions



This topic has been covered in several Oracle technical bulletins and a few IOUG papers, so I'll present it very briefly. Any transaction's rollback area can be measured by monitoring the changes in the system statistics tables during its run. This means that a valid reading requires that nothing else be running during the time the test is taking place.

All you have to do is store the result of the following query immediately before and after running your test:

        SELECT SUM(WRITES) FROM V$ROLLSTAT; When the transaction has completed, and you have the beginning and ending values, the size of the rollback information generated can be calculated:

        ENDING_WRITES - BEGINNING_WRITES - 54 = ROLLBACK INFO GENERATED. NOTE: The "54" is the rollback overhead that corresponds to the amount of overhead required by a table insert operation. This assumes that you're storing these values in tables. If you're just tracking them on paper, then ignore this factor.

This will tell you how much rollback space you need to handle this transaction; knowing how many transactions will be running at once will give you an idea of how much space will be needed in your ROLLBACKS tablespace.

Dropping Public Rollback Segments



Private rollback segments are very easy to manage. Whatever is in init.ora is in use. Whatever isn't in init.ora can be dropped. Public rollback segments, however, are more trouble to deal with.

In order to drop a public rollback segment, you must first convince Oracle that it doesn't need it. To do this, rig init.ora so that Oracle starts up using just the SYSTEM rollback segment. Add the following lines to init.ora (if there aren't already entries for these variables):

        transactions = 20
        transactions_per_rollback_segment = 20

and start the database. You will now be able to drop the public rollback segments. Do yourself a favor and change them to private ones. When you're done your work, change your init.ora back to its earlier values.

In their paper "Managing Rollback Segments" (IOUW, 1992), Mark Ramacher and Harmeet Bharara give a script that can be used to identify all users with active transactions and the rollback segment each is using. The version that appears in that article, however, is wrong. Here is the corrected version:

        SELECT
                R.NAME ROLLBACK_SEG,
                L.PID   ORACLE_PID,
                P.SPID  SYSTEM_PID,
                NVL(P.USERNAME,'NO TRANSACTION') TRANSACTION,
                P.TERMINAL
        FROM V$LOCK L, V$PROCESS P, V$ROLLNAME R
        WHERE L.PID = P.PID (+)
        AND TRUNC(L.ID1(+)/65536)=R.USN
        AND L.TYPE(+) = 'TX'
        AND L.LMODE(+) = 6
        ORDER BY R.NAME;


Note that the column inside the Trunc command is id1, not idL.

Sample output:

ROLLBACK_SEG                   ORACLE_PID SYSTEM_PI TRANSACTION    TERMINAL
------------------------------ ---------- --------- -------------- --------
R_SEG1                                              NO TRANSACTION
R_SEG2                                              NO TRANSACTION
R_SEG3                                              NO TRANSACTION
R_SEG4                                 14 23C09442  SMITHJ         VTA3905:
R_SEG5                                 15 23C0A8D3  IDL1           VTA3992:
R_SEG6                                 22 23C07727  SCOTT          VTA3869:
SYSTEM                                              NO TRANSACTION

This shows that only three users are actively writing to the rollback segments, and there is no contention for the segment headers.

If there were more than one user using a rollback segment, there would be multiple records for that rollback segment. I'd recommend adding a break on the rollback_seg column to make this stand out.

MISCELLANY



Starting in Version 6.0.33, you can specify which rollback segment your transaction is to use via the <set transaction use rollback segment> command. Note, however, that this command does not guarantee that yours will be the only transaction in that rollback segment.

In Version 7, you will be able to specify an Optimum size for each rollback segment. One the rollback segment exceeds this size, it will shrink back to it as soon as its current transactions have completed.



DBA FAQ Information:
  Part 1 - Backup Strategies
  Part 2 - Monitoring the Database
  Part 3 - Rollback Segments


Kevin Loney (kml_at_cellar.org) regularly writes the User Help column for Oracle magazine. In his spare time, he is an Oracle DBA in southeast PA. Comments and questions are always welcome.
::::::::::     ::    ::        :::::Alself me to myduce introlow
 Scott/::: ::::::< ::::: . :::::::::kml%cellar_at_tredysvr.tredydev.unisys.com
::Tiger::::::::::    :: ::: ::::::::kml_at_cellar.org  Damn the electric fence!
Received on Fri Mar 26 1993 - 15:06:59 CET

Original text of this message