Rollback segment size refers to needing an accurate minimum number of bytes required by the rollback segment for any single given transaction or for the most important of these. There are differences between the kind of transactions (INSERTS, UPDATES and DELETES).
For example the INSERTS generates less UNDO than deleting records because while rolling back an insert only requires deleting the row, rolling back a delete requires reinserting that row. It would take less bytes to store a rowid than to store information to reconstruct the actual row itself.
# of Rows Undo for Insert Undo for Deletens (same table)
--------- --------------- -----------------------------
1000 61946 114290
10000 621456 1143029
Create a test table with a amount of records from your main table.
Ex:
CREATE TABLE TEST1 AS SELECT * FROM PROD1 WHERE ROWNUM <= 100;
CREATE TABLE TEST2 AS SELECT * FROM PROD2 WHERE ROWNUM <= 100;
Write a TEST.SQL file which contains a test statement modeled after the original transaction.
Ex:
UPDATE TEST1 SET COL1 = SELECT COL2 FROM TEST2 WHERE COL3 = 'Y';
Run the script bellow to measure the undo generated on the entire database while a statement is being run.
Script for Monitoring the UNDO Generated for a Test Transaction.
rem Note, the user must have select access to V$ROLLSTAT.
rem Note, other users should not do anything during this test.
SET FEEDBACK OFF
SET TERMOUT OFF
COLUMN NAME FORMAT A40
DEFINE UNDO_OVERHEAD_FOR_THIS_SCRIPT = 54
DROP TABLE UNDO$BEGIN;
DROP TABLE UNDO$END;
CREATE TABLE UNDO$BEGIN (WRITES NUMBER);
CREATE TABLE UNDO$END (WRITES NUMBER);
INSERT INTO UNDO$BEGIN SELECT SUM(WRITES) FROM V$ROLLSTAT
/
rem If logged on to sqlplus with DBA privileges then 'sys.v$rollstat' should be used.
SET TERMOUT ON
SET FEEDBACK ON
@TEST
SET TERMOUT OFF
SET FEEDBACK OFF
rem Where 'TEST' is a sql file which has the test transaction.
INSERT INTO UNDO$END SELECT SUM(WRITES) FROM V$ROLLSTAT
/
SET TERMOUT ON
SET FEEDBACK ON
SELECT ((E.WRITES-B.WRITES) - &UNDO_OVERHEAD_FOR_THIS_SCRIPT)
"NUMBER OF UNDO BYTES GENERATED" FROM UNDO$BEGIN B, UNDO$END E
/
SET TERMOUT OFF
SET FEEDBACK OFF
DROP TABLE UNDO$BEGIN;
DROP TABLE UNDO$END;
The script makes use of the V$ROLLSTAT dynamic view as below.
--------------------------------------------------------------------
COLUMN NAME DESCRIPTION
--------------------------------------------------------------------
EXTENTS number of extents
RSSIZE number of rollback segment in bytes
WRITES total number of bytes written to rollback segment since startup
XACTS number of active transactions
GETS number of header gets
WAITS number of header waits
Calculation of total undo.
If the undo is 'x' bytes for 'n' number of rows then
T = x * (N / n) * 1.05
Where T = Approximate Total undo for the transaction in bytes.
x = Undo for the test transaction.
N = Total number of rows in the original table.
n = Total number of rows in the test table.
Conclusions.
This technique is most helpful for determining the total amount of rollback information (undo) which will be generated, especially in those cases for which the operations take a long time and are costly to rerun. Since results using this method are based on the ability of the test transaction to extrapolate correctly to the actual situation, differences in row sizing and datatypes between test scenarios and actual transactions will affect the accuracy. Nevertheless, this technique is still useful for allowing DBAs to ensure that they will not run out of rollback segment space when attempting to perform any single large transaction. For those transactions which use widely varying sql statements or manipulate rows of changing sizes, testing with a larger initial sample will produce best results.