Rollback Segment Configuration & Tips (文件 ID 69464.1)

ROLLBACK SEGMENT CONFIGURATION & TIPS
======================================

Good rollback segment configuration is crucial to a well tuned Oracle database.
The following should help you recognize and solve problems arising from 
inappropriate numbers or sizes of rollback segments. 

This bulletin will answer the following questions for Oracle databases prior to 
use of Automatic Undo Managment (AUM) with Oracle9i and Oracle10g:

     -What is a Rollback Segment?

     -How many concurrent transactions can a rollback segment handle?

     -How do you find out the max extents for a rollback segment?

     -How do you determine which user is using what rollback segment?

     -How does Oracle determine which rollback segment to use?

     -Why the ORA-1555 snapshot too old problem?

     -How many rollback segments you need to have?

     -How do you look for a rollback segment contention?

     -How do you decide the size of your rollback segments?

     -What are the guidelines on setting the OPTIMAL parameter for rollback
      segments?

     -How do you allocate a rollback segment for a transaction?

     -How do you bring rollback segments on line automatically?


What is a Rollback Segment ?
============================

A rollback segment is made up of multiple extents that consist of several 
'rollback entries' which keeps undo information for transactions. Rollback 
segments are needed for:

   1. Read-consistant database information.
   2. Database recovery.
   3. Rolling back uncommitted transactions.


How many transactions can a rollback segment handle concurrently ?
==================================================================

The maximum number of transactions allowed for a rollback segment is dependent 
on the 'transaction table slots' found in the header of the rollback segment. 
The first block of the rollback segment is called the segment header. The 
segment header contains a structure called a transaction table. The transaction
table contains slots (or rows) which keep track of all active transactions 
within that rollback segment. Each active transaction assigned to this rollback 
segment will also have a corresponding slot or row assigned to it in the
transaction table. Thereby, with a larger Oracle block size there is more room 
for a larger transaction table. In other words, more slots or rows can be 
inserted into the transaction table. The extent map is also kept in the 
segment header.

 * Before 7.3

For example, on Solaris:


TRANSACTIONS          BLOCKSIZE
------------          ---------
    32                    2K
    69                    4K
   142                    8K
 
 * For 7.3

    Also for Solaris :

TRANSACTIONS          BLOCKSIZE
------------          ---------
    31                    2K
    67                    4K
   140                    8K


How do you find out the maximum extents for a rollback segment?
===============================================================

Run the following query:

        sql> select SEGMENT_NAME, STATUS, MAX_EXTENTS
                from dba_rollback_segs;

This will tell you the MAX_EXTENTS that the rollback segment was created with; 
however, the first block of each segment contains an extent map for each extent. 
The maximum number of extents allowed is therefore a function of the database 
block size of each extent map entry. This is a common extent mapping
table:

DATA BLOCK SIZE     GREATEST MAXEXTENT VALUE
---------------     ------------------------
512                           25
1K                            57
2K                            121
4K                            249
8K                            505

Thereby, the MAX_EXTENTS value in "dba_rollback_segs" may not be accurate 
because the MAX_EXTENTS cannot exceed the GREATEST MAXEXTENT VALUE.


How to determine which user is using what rollback segment?
===========================================================

SELECT   r.name "RB NAME ", p.pid "ORACLE PID",
         p.spid "SYSTEM PID ", NVL (p.username, 'NO TRANSACTION') "OS USER", 
         p.terminal
FROM v$lock l, v$process p, v$rollname r, v$session s
WHERE    l.sid = s.sid(+)
AND      s.paddr = p.addr
AND      TRUNC (l.id1(+)/65536) = r.usn
AND      l.type(+) = 'TX'
AND      l.lmode(+) = 6
ORDER BY r.name;


How does Oracle determine which rollback segment to use?
========================================================

The rules are:

   1. Always assign to the rollback segment which has the least number of 
      active transactions.

   2. If two or more rollback segments have the same "least number of active 
      txns" , then assign to the one which is after the last one used. This 
      ensures that undo is kept for a longer time.


Why the ORA-1555 snapshot too old problem?
==========================================

  See Note.40689.1: CAUSES AND SOLUTIONS TO ORA-1555 "SNAPSHOT TOO OLD".
  See Note.45895.1: ORA-1555 "SNAPSHOT TOO OLD" in Very Large Databases (VLDB).


How MANY rollback segments do you need to have?
===============================================

Oracle7 keeps a transaction table in the header of every rollback segment. 
Every transaction must have update access to the transaction table for its 
rollback segment. You need enough rollback segments to prevent transactions 
contending for the transaction table.


How do you find out a transaction table contention?
===================================================

Any non-zero value for 'undo header' in the CLASS column of "v$waitstat" 
indicates contention for rollback segment header blocks.

Example:

SVRMGR> select * from v$waitstat;

CLASS                   COUNT         TIME
------------------   ----------    ----------
data block               0              0
sort block               0              0
save undo block          0              0
segment header           0              0
save undo header         0              0
free list                0              0
system undo header       0              0
system undo block        0              0
undo header              0              0
undo block               0              0


Note that 'undo header' value is zero, hence NO contention.
Another way to find out is by running the following query .. a non-zero 
value for the 'WAITS' column indicates a rollback segment contention.


SVRMGR> select name, waits
     2> from v$rollstat s, v$rollname n
     3> where s.usn=n.usn;


NAME                           WAITS
------------------------------ ----------
SYSTEM                            0
R01                               0
R02                               0
R03                               0


To calculate the number of rollback segments, you need to know how many 
transactions are likely to be active at any given time. This depends on what 
users are doing. Note that queries do not need transaction table access, so not 
all active users will have active transactions (OLTP applications tend to have 
many short transactions).

General recommendation for how many rollback segments:

  For OLTP : One rollback segment for every ten users.
  For BATCH jobs : One rollback segment for each concurrent job.


How do you decide what SIZE your rollback segments should be?
=============================================================

There are two issues that need to be considered when deciding if your segment 
is large enough. First, you want to make sure that the transactions will not 
cause the head of the rollback segment to wrap around too fast and catch the 
tail. This causes the segment to extend in size. Second, if you have long 
running queries that access data that frequently changes, you want to make sure 
that the rollback segment doesn't wrap around and prevent the construction of a 
read consistent view (look at "Why the ORA-1555 snapshot too old problem?" 
above).


Determining the proper rollback segment size:
---------------------------------------------

The size needed for a rollback segment depends directly on the transaction 
activity of your database. You need to be concerned about the activity during 
normal processing of the database, not with rare or semi-frequent large 
transactions. We will deal with these special cases separately.


Same size extents:
------------------

For sizing rollback segments extents, Oracle strongly recommend that each 
extent be of the same size.


INITIAL extent size:
--------------------

Choose the INITIAL storage parameter from the list 2KB, 4KB, 8KB, 16KB, 32KB ...
etc. This will insure that when you drop the extent you can reuse all the 
freed space without waste.


NEXT extent size:
-----------------

Use the same value for NEXT as INITIAL.


MINEXTENTS:
-----------

Set MINEXTENTS to 20, this will make it unlikely that the rollback segment 
needs to grab another extent because the extent that should move into is still 
being used by an active transaction.

To find out the size of the rollback segments needed to handle normal 
processing on the database you need to some testing. A good test is to start 
with small rollback segments and allow your application to force them to extend.
 Here are the steps to run such test:

   1. Create a rollback segment tablespace.

   2. Select a number of rollback segments to test and create them in the 
      tablespace.

   3. Create the rollback segments so that all extents are the same size. 
      Choose an extent size that you will suspect will need between 10 to 30 
      extents when the segments grow to full size.

   4. Each rollback segment should start with two extents before the test is 
      done. This is the minimum number of extents any rollback segment can have.

   5. Activate only the rollback segments that you are testing by making the 
      status "online". The only other segment that should be "online" is the 
      system rollback segment.

   6. Run transactions and load data typical of the application.

   7. Watch for rollback segment contention. How to find out?

   8. Watch for the maximum size a rollback extends to.

The maximum size any one of the rollback segments reaches during the test is 
the size you want to use when configuring. We will call this size the "minimum 
coverage size." If you see rollback contention, adjust the number of the 
rollback segments (increase) and rerun the test. Also, if the largest size 
requires fewer than 10 or more than 30, it is a good idea to lower or raise the 
extent size, respectively, and rerun the test.


Sizing rollback segments for STEADY AVERAGE transaction rate:
-------------------------------------------------------------

For databases where the transaction rate base has NO fluctuation, there is a 
straightforward way to configure the tablespace:

Create a tablespace that will fit your calculated number of  rollback 
segments with the "minimum coverage size" you have determined. Follow the 
guidelines above for INITIAL and NEXT extents.

As a safety net, allocate some additional space in the tablespace to allow 
segments to grow if necessary. If you select to do this, use the OPTIMAL 
feature to force the rollback segments to free up any additional space they 
allocate beyond their determined size requirement.


Sizing rollback segments for FREQUENT LARGE transaction rate:
-------------------------------------------------------------

A large transaction is one in which there is not enough space to create all 
rollback segments of the size necessary to handle its rollback information. 
Since we can't depend on the segment shrinking in time to allow repeated large 
transactions, OPTIMAL is not really an option for this environment. There are 
basically two options that you can choose from for your rollback segment 
tablespace.

A) Reduce the number of segments so that all are large enough to hold the 
   largest transactions. This option will introduce contention and will cause 
   some degradation in performance. It is a reasonable choice if performance is
   not extremely critical.

B) Build one or more large rollback segments and make sure that large 
   transactions use these segments. The SET TRANSACTION USE ROLLBACK SEGMENT 
   command is necessary to control the placement of these large transactions. 
   This option is difficult to implement if large transactions are being run 
   with adhoc queries and there is no systematic control of large transactions. 
   This option is recommended in an environment where the large transactions 
   are issued from a controlled environment. In other words, an application 
   which will set the transaction to the appropriate rollback segment.


Sizing rollback segments for INFREQUENT LARGE transaction rate:
---------------------------------------------------------------

Use the OPTIMAL feature to set up a flexible rollback segment scheme, one 
in which you are not concerned about which rollback segment the large 
transaction falls upon. The key is to leave enough free space in the rollback 
tablespace that the largest transaction's rollback information can fit entirely 
into it. To do this, create the rollback tablespace with the space needed for 
your calculated number of segments and their "minimum coverage size" plus this 
additional space. Set the OPTIMAL for each segment equal to the minimum 
coverage size.

What you will see is that the large transaction will randomly make one of the 
segments grow and consume the free space, but the segment will release the 
space before the next large transaction comes along. Note that you are 
sacrificing some performance for this flexibility.


What are the guidelines on setting the OPTIMAL parameter for rollback segments?
===============================================================================

When you create or alter a rollback segment, you can use the storage 
parameter OPTIMAL, which applies only to rollback segments, to specify the 
optimal size of the rollback segment in bytes. You should carefully assess the 
kind of transactions the system runs when setting the OPTIMAL parameter for 
each rollback segment.

For a system that executes long running transactions frequently, OPTIMAL 
should be large so that Oracle does not have to shrink and allocate extents 
frequently. Also, for a system that executes long queries on active database, 
OPTIMAL should be large to avoid "snapshot too old" ORA-1555 errors.

OPTIMAL should be smaller for a system that mainly executes short transactions 
and queries so that the rollback segments remain small enough to be cached in  
memory, thus improving system performance. You should not make OPTIMAL smaller 
than the "minimum coverage size". Otherwise, performance will suffer due to 
excessive segment resizing.


How do you allocate a rollback segment for a transaction?
=========================================================

Oracle assigns a transaction to a rollback segment using simple rules. You 
can issue a SET TRANSACTION statement with the USE ROLLBACK SEGMENT clause to 
choose a specific rollback segment for your transaction.

Example : SET TRANSACTION USE ROLLBACK SEGMENTS large_rs1;

To assign a transaction to a rollback segment explicitly, the rollback 
segment must be online and the SET TRANSACTION ... statement must be the first 
statement of the transaction.

After the transaction is committed, Oracle will automatically assign the 
next transaction to any available rollback segment rollback segment unless the 
new transaction is explicitly assigned to a specific rollback by the user.


How do you bring rollback segments on line automatically?
=========================================================

Private rollback segments could be brought online automatically at database 
startup only if they are listed in the init.ora initialization parameter 
rollback_segments.

The number of public rollback segments that will be brought online only at database 
startup will depend on the values of the initialization parameters transactions 
and transactions_per_rollback_segments.  The minum number of public rollback
segments acquired by the instance will be transactions/transactions_per_rollback_segments. 

Public rollback segments could also be brought online specifying them in the 
parameter rollback_segments.




Search Words:
=============

rbs, rollback, segments