Rollback Segments - Managing
-- 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.