-- Examples of drop/creation of rollback segments:
alter rollback segment RBS01 offline;
drop rollback segment RBS01;
alter rollback segment RBS02 offline;
drop rollback segment RBS02;
alter rollback segment RBS03 offline;
drop rollback segment RBS03;
alter rollback segment RBS04 offline;
drop rollback segment RBS04;
alter rollback segment rbsbig offline;
drop rollback segment rbsbig;
-- Examples of creation of rollback segments:
CREATE ROLLBACK SEGMENT RBSBIG TABLESPACE RBS
STORAGE ( INITIAL 50M NEXT 10M OPTIMAL 60M MINEXTENTS 2 MAXEXTENTS 2147483645);
ALTER ROLLBACK SEGMENT RBSBIG ONLINE;
CREATE ROLLBACK SEGMENT RBS01 TABLESPACE RBS
STORAGE ( INITIAL 4096K NEXT 4096K OPTIMAL 16384K MINEXTENTS 2 MAXEXTENTS 2147483645);
ALTER ROLLBACK SEGMENT RBS01 ONLINE;
CREATE ROLLBACK SEGMENT RBS02 TABLESPACE RBS
STORAGE ( INITIAL 4096K NEXT 4096K OPTIMAL 16384K MINEXTENTS 2 MAXEXTENTS 2147483645);
ALTER ROLLBACK SEGMENT RBS02 ONLINE;
CREATE ROLLBACK SEGMENT RBS03 TABLESPACE RBS
STORAGE ( INITIAL 4096K NEXT 4096K OPTIMAL 16384K MINEXTENTS 2 MAXEXTENTS 2147483645);
ALTER ROLLBACK SEGMENT RBS03 ONLINE;
CREATE ROLLBACK SEGMENT RBS04 TABLESPACE RBS
STORAGE ( INITIAL 4096K NEXT 4096K OPTIMAL 16384K MINEXTENTS 2 MAXEXTENTS 2147483645);
ALTER ROLLBACK SEGMENT RBS04 ONLINE;
-- Examples of drop/creation of rollback segments:
alter rollback segment RBS01 offline;
drop rollback segment RBS01;
alter rollback segment RBS02 offline;
drop rollback segment RBS02;
alter rollback segment RBS03 offline;
drop rollback segment RBS03;
alter rollback segment RBS04 offline;
drop rollback segment RBS04;
alter rollback segment rbsbig offline;
drop rollback segment rbsbig;
-- Dropping a rollback segment
-- 1. check status
select * from dba_rollback_segs;
-- 2. bring it offline
alter rollback segment rbsbig offline;
-- 3. check if offline
select * from dba_rollback_segs;
-- 4. drop it!
drop rollback segment rbsbig;
-- Creating a large rollback segments
create rollback segment rbsbig tablespace RBS
storage (minextents 2 initial 200M next 100M maxextents unlimited);
alter rollback segment rbsbig online;
-- Determine rollback segment contention
select name, gets, waits, ((gets-waits)*100)/gets hit_ratio
from v$rollstat s, v$rollname n
where s.usn=n.usn
;
-- Determine space used by the rollback segments
select * from dba_segments where segment_type = 'ROLLBACK';
select * from dba_rollback_segs;
-- Query v$rollstat
select * from v$rollstat;
SELECT usn "Rollback Table", GETS, WAITS , xacts "Active Transactions"
FROM V$ROLLSTAT;
SELECT
NAME, EXTENTS, XACTS,
HWMSIZE, RSSIZE, WAITS,
WRAPS, EXTENDS
FROM
V$ROLLNAME N,
V$ROLLSTAT S
WHERE
N.USN=S.USN;
CREATE OR REPLACE VIEW ROLLBACK1 AS
SELECT
NAME, EXTENTS, OPTSIZE,
SHRINKS, AVESHRINK, AVEACTIVE
FROM
V$ROLLNAME N,
V$ROLLSTAT S
WHERE
N.USN=S.USN;
Rollback segments are used by all kinds of transactions for rollback, transaction, read consistency, and recovery. Tuning
problems can occur when transactions experience contention for rollback segments.
The following statement reports the overall number of rollback segment waits:
SELECT class, count
FROM v$waitstat
WHERE class IN ('undo header', 'undo block');
CLASS COUNT
--------------------- ------
undo header 1129
undo block 5151
If the number of waits is greater than zero, you have had contention for your rollback segments.
Each rollback segment has a transaction table that controls the transactions accessing the rollback segment. Oracle
documentation says that the transaction table has approximately 30 slots in the rollback if your database has a 2-kilobyte
block size.
The following query lists the number of waits on a slot in the transaction tables:
SELECT segment_name "Rollback Name", usn "Rollback Table", GETS, WAITS , xacts "Active Transactions"
FROM V$ROLLSTAT, dba_rollback_segs
WHERE usn = segment_id ;
Rollback Table GETS WAITS Active Transactions
-------------- ---------- ---------- -------------------
0 463 0 0
1 9761 0 1
2 8713 2 0
3 8056 0 0
4 8669 0 0
5 8492 0 0
6 8755 1 0
7 9371 0 0
8 8300 1 0
The ideal is to have the waits zero; but in the real world, this is not always achievable. However, they should be as close
to zero as possible. At the very worst, the ratio of gets to waits should be around 99%.
We recommend to create n new rollback segments (where n equals the number of rollback segments that have experienced waits).
In the example above, you would add three rollback segments.
Large updates
Transactions performing large updates perform better with larger rollbacks because of the minimizing of dynamic extension.
Take advantage of the SET TRANSACTION statement:
SET TRANSACTION USE ROLLBACK SEGMENT
Example:
SET TRANSACTION USE ROLLBACK SEGMENT rbsbig;
Long-running jobs
Long-running overnight-style jobs use very large rollback segments. It is common to swap rollback segments from small to
large for overnight processing and back to small for daily processing. Like tables and indexes, rollback segments may
run out of space during processing and need to request additional extents. Dynamic extension can have a serious impact
on performance, particularly on the performance of long-running jobs. RDBMS Version 6.0.33 introduced the ability to
assign a transaction to an individual rollback segment. By assigning larger rollback segments to long-running update jobs,
you can improve performance. If your application rollback tablespace is not large enough to hold both the small and large
rollbacks simultaneously, you can assign alternative rollback segments for daily and overnight processing. You will need
to offline (or drop) all of the smaller OTLP rollback segments used for high-transaction daily usage and online a smaller
number of larger rollback segments for overnight processing. When the overnight jobs are completed, you can then offline
the larger rollback segments and reinstate the smaller segments. You assign a particular rollback segment to a transaction
with the statement:
SQL> SET TRANSACTION USE ROLLBACK SEGMENT segment_name
To be effective, this statement must be the first DML statement (of any kind) after a successful connect, DDL, commit,
or rollback statement. You must issue this statement after each subsequent commit or rollback. The diagram below illustrates
how the alternative assignment of rollback segments works.
-- Statistics
Oracle provides statistics which relate to the capability of rollbacks to shrink back to an optimal size. To make use
of these statistics it is suggested that the following two views be created to reduce the number of items contained in
the v$rollstat view:
View ROLLBACK1: View ROLLBACK2
CREATE OR REPLACE VIEW ROLLBACK2 AS
SELECT
NAME, EXTENTS, XACTS,
HWMSIZE, RSSIZE, WAITS,
WRAPS, EXTENDS
FROM
V$ROLLNAME N,
V$ROLLSTAT S
WHERE
N.USN=S.USN;
CREATE OR REPLACE VIEW ROLLBACK1 AS
SELECT
NAME, EXTENTS, OPTSIZE,
SHRINKS, AVESHRINK, AVEACTIVE
FROM
V$ROLLNAME N,
V$ROLLSTAT S
WHERE
N.USN=S.USN;
Once the views exist the following reports provide detailed information concerning the rollbacks and their various storage,
wrap and shrink statistics. The following reports and example outputs are formatted for VAX/VMS and to display to
a 132 wide screen. If you want to use them on a different machine, remove or modify the calls to the host command.
.If there are excessive WRAPS, the database rollback segment is probably too small, or, you need more rollback segments.
Excessive WRAPS will probably be accompanied by "Snapshot Too Old" errors.
.If you get excessive extends, the rollback segment is too small.
.Excessive WAITS show you need more rollback segments.
If the report shows a number of shrinks (meaning the rollback segment extended beyond the setting of OPTIMAL and then
shrank back to OPTIMAL) then the rollback segment extent size or OPTIMAL parameter may be set too small. Use the parameter
AVERAGE SHRINK to determine the amount of space to add to the initial and next extent values.
The new optimal size should be:
(OPTIMAL SIZE + AVERAGE SHRINK)
When a rollback segment is taken offline, its status is changed to PENDING OFFLINE and it is taken offline as soon as its
pending transactions complete. To determine if a rollback segment has outstanding transactions, the following select
statement is used.
SELECT name, xacts "ACTIVE TRANSACTIONS"
FROM v$rollname, v$rollstat
WHERE status = 'PENDING OFFLINE'
AND v$rollname.usn = v$rollstat.usn;
Recommendations
Ø For UNIX it is suggested that PUBLIC rollback segments not be used since multiple instance databases are not generally
allowed on most UNIX platforms.
Ø It is strongly suggested that the following Storage Clause guidelines be used:
INITIAL = NEXT
MINEXTENTS = 2 (default value)
MAXEXTENTS = a calculated maximum based on the size of the rollback segment tablespace, the size of rollback segments
extents and the number of rollback segments.
PCTINCREASE = 0 (default value)
Ø Rollback segments can be placed in any tablespace, but it is suggested that they be placed in a tablespace that only
contains other rollback segments.
This makes administration easier. It's also important because rollback segments are high-activity areas and should be
separated from data and index files.
Ø The extent size should be the average transaction size expected for the instance. The number of users per rollback
segments should be set such that there aren't excessive extents per rollback segment but each expected concurrent user
requires at least one extent. The sum of the number of extents in all non-system rollback segments should be equal to
or slightly more than the average number of concurrent transactions.
|