Rollback Segments Nilendu Misra MAR 99 nilenduinnocent com
Rollback Segments Nilendu Misra (MAR’ 99) nilendu@innocent. com
Areas Covered l Guidelines to Manage l Creating/Altering/Dropping RBS l Storage Parameters l Monitoring & Tuning
What Is l For every transaction taking place RBS keeps the BEFORE IMAGE of the DATA l READ CONSISTENCY l ROLLBACK Transaction l 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 l During creation SYSTEM RBS l After creation create several RBS in a separate tablespace l Every RBS should be of same size l Every EXTENT in a RBS is of same size l Ideally number of RBS should be equal to Maximum number of concurrent transactions (WHY? )
Number (Cont. ) l Thumb Rule is to create N RBS where N = no. of users / 4 l One transaction can use ONE RBS l Many transactions can acquire the same RBS l TRANSACTION TABLE contains the address of the row modified & status(Committed / Active)
Number (Cont. ) l CIRCULAR BUFFER. Should have at least 2 extents l If the NEXT extent is not available, new extent is allocated l PCTINCREASE = 0 l OPTIMAL l SET TRANSACTION USE ROLLBACK SEGMENT R 01 ;
Tips l Should be minimum 20 extents l ALTER ROLLBACK SEGMENT R 01 OFFLINE; l Sizing should be done W. R. T largest transaction l Another Thumb Rule : SIZE = 10% of Largest Table Size l SYSTEM rollback segment cannot be taken OFFLINE
Creation CREATE [PUBLIC] ROLLBACK SEGMENT R 5 TABLESPACE “RBS” STORAGE (INITIAL 1024 K NEXT 1024 K OPTIMAL 5 M 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 R 05 STORAGE (NEXT 256 K); [Note : This could result in mismatched extent size] ALTER ROLLBACK SEGMENT R 05 SHRINK TO 1000 K; ALTER ROLLBACK SEGMENT R 05 SHRINK TO OPTIMAL;
Dropping l A RBS must not be in USE l 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] l 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 “-------” l 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 l For BATCH jobs use big RBS explicitly assigned l TRUNCATE does not use RBS [well almost] l In Import use COMMIT = Y l Use OPTIMAL judiciously l Better waste some space on RBS datafile (keep AUTOEXTEND) l PUBLIC cannot be altered to PRIVATE l PRIVATE cannot be altered to PUBLIC
A Few Questions Your database has 20 RBS. Each RBS has 50 maxextents. Each extent size is 20 K. Minextent is 2. Now an overnight batch operation generates 18 M 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 1024 K NEXT 1024 K MAXEXTENTS 25 OPTIMAL 5 M. High Water mark for each is 25 M. The datafile to RBS tablespace is 100 m. Now, when the DB is just up what % of that datafile will be full?
- Slides: 20