Subject: Rollback Segment sizing for Large Operations
Last Revision Date: 11 September 1994
Author:Linda Fong and Vijay Oddiraju
I. Introduction
While running long transactions or large imports rollback segments will
occasionally run out of space, giving an error ORA-1562 'failed to
extend rollback segment (ID=num).' Successful completion of a
transaction (when there is no possibility of committing more
frequently) requires recreating a new rollback segment of larger size.
But how much larger should the rollback segment be in order to
accommodate the operation? In these situations, the following article
is useful for estimating the rollback segment size.
The technique outlined here gives the minimum number of bytes required
by the rollback segment for any single given transaction. Using this
method, Database Administrators can estimate how to create rollback
segments large enough to prevent large transactions from failing due to
errors such as ORA-1562. This article discusses the method used to
derive an estimation, as well as factors such as differences in row
size, datatypes, and variations in sql statements which can vary the
accuracy of the result.
II. Background
Rollback segment extents are used in a circular fashion; that is, as
each transaction commits, the space used for rollback information (or
"undo") associated with that transaction becomes available for reuse.
If the next extent in a rollback segment has active transactions in it
at the time when more space is needed, the instance attempts to extend
the rollback segment. Rollback segment space is used optimally with
extents of equal size. In Oracle V6.0, the maximum extents which can be
allocated to a given object is based on the Oracle block size. For
example, on VMS, which has an default Oracle block size of 2048 bytes,
the limit is 121. Therefore given any STORAGE clause, there is a
maximum size for that object.
To avoid process termination due to errors (such as ORA-1562 or
ORA-1547) associated with reaching maximum capacity of rollback
segments, the goal is to create rollback segments with a more
manageable number of extents. Having a rough estimate of how
much space is required for completion of the largest transaction,
DBA's can create rollback segments such that fewer extents will be
acquired. (For more information, please refer to Chapters 4 and 16
of the V6.0 Database Administrator's Guide. Also, for more discussion
on the advantage of having multiple equally-sized extents in a
rollback segment, please see RTSS bulletin "Rollback Segment Extents"
as well as other RTSS on-line bulletins on rollback segments.)
III. Estimation of Rollback Segment Size
The number of bytes required for storage of undo, or information that
is needed in case of rollback, depends on two things: 1) the type of
transaction being performed (insert, update, delete, etc.) and 2) the
actual data being processed. In general, inserting a given record
into a table generates less undo than deleting the same record,
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.) Figure 1.1 shows the amount of undo generated
for inserting and deleting the same number of rows from a test table.
--------------------------------------------------------------------
# of Rows Undo for Insert Undo for Deletens(same table)
--------- -------------- -----------------------------
1000 61946 114290
10000 621456 1143029
Figure 1.1 UNDO Generated (in bytes) for Insert and Delete
Test Transaction.
--------------------------------------------------------------------
Additionally, the undo generated for an update on a fraction of a row
is not proportionate to the amount generated for the entire row, due
to additional overhead required. For example, as illustrated in
Figure 1.2, updating 3 out of 8 total columns generates more undo
relative to that generated for the entire row.
--------------------------------------------------------------------
# of Rows Update of 3 Columns Update of 8 Columns(same table)
--------- ------------------- -------------------------------
1000 81192 101192
10000 821472 1014383
Figure 1.2. UNDO Generated (in bytes) for Update Test Transaction.
--------------------------------------------------------------------
Currently, there is no formula to predict the number of bytes of undo
that are generated for any given SQL statement. In the absence
of such a formula, DBA's can only monitor how much actually
*does* get generated for given SQL statements.
In order to estimate how much undo is generated for a large
transaction, it is best to perform the transaction on a similar
table with a few rows and extrapolate the results to the size of the
original table.
For example, let us say that the original transaction is as follows:
UPDATE PROD1 SET COL1 = SELECT COL2 FROM PROD2 WHERE COL3 = 'Y';
1. Create smaller tables from PROD1, PROD2 for testing:
CREATE TABLE TEST1 AS SELECT * FROM PROD1 WHERE ROWNUM <= 100;
CREATE TABLE TEST2 AS SELECT * FROM PROD2 WHERE ROWNUM <= 100;
2. If PROD1 and PROD2 have indexes, also be sure to create
the corresponding indexes on TEST1 and TEST2. For example:
CREATE UNIQUE INDEX I_TEST1 ON TEST1(COL1);
CREATE UNIQUE INDEX I_TEST2 ON TEST2(COL2);
3. Write a TEST.SQL file which contains a test statement modeled after
the original transaction. So given our example transaction above,
the following test transaction would be suitable:
UPDATE TEST1 SET COL1 = SELECT COL2 FROM TEST2 WHERE COL3 = 'Y';
4. Run the UNDO.SQL script (shown in Figure 3) to measure the undo
generated on the entire database while the statement(s) in the
TEST.SQL file are being run.
UNDO.SQL makes use of the V$ROLLSTAT dynamic view, which is described
in Figure 2.
--------------------------------------------------------------------
Column Name Description
----------- -----------
EXTENTS number of extents
RSSIZE size 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
Figure 2. V$ROLLSTAT View.
---------------------------------------------------------------------
The script looks at the total value of "writes" for *all* rollback
segments before and after the TEST.SQL file is executed. The
difference between the two totals is the undo generated during this
time period. Hence, the results of the test are only accurate if
it is performed in single user mode.
----------------------------------------------------------------------
rem UNDO.SQL
rem This SQL*Plus command file can be used to monitor how much UNDO is
rem generated by a transaction which can consist of multiple SQL and
rem PL/SQL statements.
rem Write the transaction to file TEST.SQL in your working directory
rem and run this command file UNDO.SQL. You may want to open a spool
rem file before running UNDO.SQL
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 (**see NOTE for explanation.)
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'
rem 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;
Figure 3. Script for Monitoring the UNDO Generated for a Test
Transaction.
----------------------------------------------------------------------
NOTE: "UNDO_OVERHEAD_FOR_THIS_SCRIPT" needs to be defined in order to
account for the unavoidable overhead of counting the statement:
INSERT INTO UNDO$BEGIN SELECT SUM(WRITES) FROM V$ROLLSTAT
as part of the UNDO GENERATED. While this overhead stays constant
for any given RDBMS version, it is not guaranteed to remain the
same between Oracle RDBMS versions. So for best results, calibrate
this script by running it with "SELECT * FROM SCOTT.EMP" as the test
transaction, making sure that UNDO GENERATED is 0. If a non-zero value
is returned, then the UNDO_OVERHEAD constant will need to be adjusted
accordingly.
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.
Rollback Segment Sizing:
Total number of bytes of undo (as determined by the above script)
could be in any number of extents. It is purely a choice of how many
extents the rollback segment should have. 10 to 20 equivalently sized
extents per rollback segment is optimal for nearly all Oracle V6.0
databases. Also, be sure to account for any other transactions which
may be active at the same time as your large transaction, because many
transactions are allowed to use the same rollback segment at the same
time.
IV. 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 DBA's 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.
For further reading:
Database Adminstrator's Guide, October 1990 Revision.
"ROLLBACK SEGMENT EXTENTS", document# 99267.6 on the RTSS Bulletin Board.
Acknowledgments:
We would like to thank Tuomas Pystynen, Walter Lindsay and Mike
Hartstein for providing valuable information and assistance.