Transcript Rollback Segments

Rollback Segments
Nilendu Misra (MAR’99)
[email protected]
Areas Covered
 Guidelines to Manage
 Creating/Altering/Dropping RBS
 Storage Parameters
 Monitoring & Tuning
What Is
 For every transaction taking place RBS keeps the
BEFORE IMAGE of the DATA
 READ CONSISTENCY
 ROLLBACK Transaction
 RECOVER Database
Read Consistency
For a query, only data blocks written on or before that SCN
are read. Blocks changed after that are read from RBS.
e.g.,when this query started the SCN noted was 10023
Number
 During creation SYSTEM RBS
 After creation create several RBS in a separate
tablespace
 Every RBS should be of same size
 Every EXTENT in a RBS is of same size
 Ideally number of RBS should be equal to
Maximum number of concurrent transactions
(WHY?)
Number (Cont.)
 Thumb Rule is to create N RBS
of users / 4
where N = no.
 One transaction can use ONE RBS
 Many transactions can acquire the same RBS
 TRANSACTION TABLE contains the address of
the row modified & status(Committed / Active)
Number (Cont.)
 CIRCULAR BUFFER. Should have at least 2
extents
 If the NEXT extent is not available, new extent is
allocated
 PCTINCREASE = 0
 OPTIMAL
 SET TRANSACTION USE ROLLBACK
SEGMENT R01 ;
Tips
 Should be minimum 20 extents
 ALTER ROLLBACK SEGMENT R01
OFFLINE;
 Sizing should be done W.R.T largest transaction
 Another Thumb Rule : SIZE = 10% of Largest
Table Size
 SYSTEM rollback segment cannot be taken
OFFLINE
Creation
CREATE [PUBLIC] ROLLBACK SEGMENT R5
TABLESPACE “RBS” STORAGE (INITIAL
1024K NEXT 1024K OPTIMAL 5M
MINEXTENTS 20 MAXEXTENTS 40);
Rules : (a) INITIAL = NEXT
(b) MINEXTENTS = 2 (DEFAULT)
(c) MAXEXTENTS = Calculated Value
(d) PCTINCREASE = 0 (DEFAULT)
(e) Unless running PARALLEL instances
don’t use PUBLIC
Alter Storage
ALTER PUBLIC ROLLBACK SEGMENT R05
STORAGE (NEXT 256K);
[Note : This could result in mismatched extent size]
ALTER ROLLBACK SEGMENT R05
SHRINK TO 1000K;
ALTER ROLLBACK SEGMENT R05 SHRINK
TO OPTIMAL;
Dropping
 A RBS must not be in USE
 After dropping MUST be removed from init.ora.
Otherwise database cannot be restarted.
[Note : RBS mentioned in the init.ora are taken
ONLINE while the database is restarted. Or by
default, when an instance starts, it acquires
TRANSACTIONS/TRANSACTIONS_PER_RO
LLBACK_SEGMENT]
 Drop rollback segments when the extents are too
fragmented on disk, or the segment needs to be
relocated in a different tablespace.
Display Info
SELECT SEGMENT_NAME,
TABLESPACE_NAME,STATUS
FROM SYS.DBA_ROLLBACK_SEGS;
STATUS
DESCRIPTION
IN USE
Online
AVAILABLE
Created,but not online
OFFLINE
Offline
INVALID
Dropped
NEEDS RECOVERY Corrupted
PARTLY AVAILABLE Unresolved transaction
data in a Distributed DB
Reiterate
IDEALLY
(1) Each Transaction should fit in
one extent of a RBS
(2) All user RBS should be outside
SYSTEM tablespace
(3) All RBS will have same size
ORA - 1555
SNAPSHOT TOO OLD
Occurs because the RBS is small.
So the old “Snapshot” of the old transaction gets
overwritten by other ‘undo’ information. When
some user queries the data it is not there in RBS.
Solution : Create Larger RBS
Sizing RBS
Before & After the largest transaction
issue :
SELECT SUM(WRITES) FROM V$ROLLSTAT;
(No other transaction should go on)
UNDO generated = (AFTER Value BEFORE Value)
View OPTIMAL
SELECT A.NAME,B.OPTSIZE FROM
V$ROLLNAME A, V$ROLLSTAT B
WHERE A.USN=B.USN;
V$ROLLSTAT COLUMNS
XACTS - No of active transactions
WRITES - No of Bytes written to the RBS
HWMSIZE - Max size(B) reached during usage
SHRINKS - No of shrinks to OPTIMAL size
WRAPS - No of times an entry wrapped into
a new extent
EXTENDS - No of new extent acquisition
This RBS is used by “-------”
 Transactions acquire lock (type TX) within RBS
header.So join V$LOCK to V$ROLLNAME. Each lock is
owned by a process. So further join V$LOCK to
V$PROCESS to map Process with RBS(script given in
Lab)
SELECT R.NAME RBS, P.PID ORACLE_PID, P.SPID OS_PID,
NVL(P.USERNAME,'NO TRANSACTION') TRANSACTION,
P.TERMINAL
FROM V$LOCK L,V$PROCESS P,V$ROLLNAME R
WHERE L.ADDR = P.ADDR(+)
AND TRUNC(L.IDL(+)/65536) = R.USN
AND L.TYPE(+) = 'TX' AND L.MODE(+) =6
ORDER BY R.NAME ;
Tips
 For BATCH jobs use big RBS explicitly assigned
 TRUNCATE does not use RBS [well almost]
 In Import use COMMIT = Y
 Use OPTIMAL judiciously
 Better waste some space on RBS datafile (keep
AUTOEXTEND)
 PUBLIC cannot be altered to PRIVATE
 PRIVATE cannot be altered to PUBLIC
A Few Questions
Your database has 20 RBS. Each RBS has 50
maxextents. Each extent size is 20K. Minextent
is 2. Now an overnight batch operation generates
18M undo volume. This batch (PLSQL) deletes
all records from 10 large tables.
(a) Could the batch operation be done normally?
(b) Suggest improvement(s)
Few Questions(!!!)
DB has 4 RBS. Size Details for each :
INITIAL 1024K
NEXT 1024K
MAXEXTENTS 25 OPTIMAL 5M.
High Water mark for each is 25M.
The datafile to RBS tablespace is 100m.
Now, when the DB is just up what % of that
datafile will be full?