1 / 20

Rollback Segments

Rollback Segments. Nilendu Misra (MAR’99) nilendu@innocent.com. 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

kanan
Download Presentation

Rollback Segments

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Rollback Segments Nilendu Misra (MAR’99) nilendu@innocent.com

  2. Areas Covered • Guidelines to Manage • Creating/Altering/Dropping RBS • Storage Parameters • Monitoring & Tuning

  3. What Is • For every transaction taking place RBS keeps the BEFORE IMAGE of the DATA • READ CONSISTENCY • ROLLBACK Transaction • RECOVER Database

  4. 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

  5. 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?)

  6. Number (Cont.) • Thumb Rule is to create N RBS where N = no. of users / 4 • 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)

  7. 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 ;

  8. 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

  9. 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

  10. 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;

  11. 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_ROLLBACK_SEGMENT] • Drop rollback segments when the extents are too fragmented on disk, or the segment needs to be relocated in a different tablespace.

  12. 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

  13. 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

  14. 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

  15. 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)

  16. 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

  17. 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 ;

  18. 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

  19. 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)

  20. 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?

More Related