Oracle undo sizing
Oracle Database Tips by Donald Burleson
Question: How do I size
my UNDO to make sure that all of my Oracle tasks complete
successfully? What are the guidelines for UNDO sizing using the
undo_retention and the UNDO tablespace?
Answer: Undo sizing is
straightforward. The larger your undo tablespace, the more
"before images" you can hold for long-running DML (updates,
inserts). The Oracle UNDO tablespace must have enough space to
rollback any long-running update.
Given enough time, Oracle will
automatically manage the UNDO usage.
In Oracle, UNDO size can be controlled with
the undo_retention parameter and the size of the UNDO tablespace,
and the setting for these are determined by the level of DML
activity in the database:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
SEE CODE DEPOT FOR FULL SCRIPTS
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
Also, note that the automatic undo management feature introduced in Oracle 9i has
been improved with the following capabilities.
Some of the undo management related initialization parameters are
eliminated in Oracle Database 10g as they are automatically
calculated. They include max_rollback_segment, row_locking,
undo_suppress_errors, serializable, and transaction_auditing.
This script is useful in determining a HWM for
sessions, for UNDO sizing:
rem session.sql - displays all
set echo off;
set termout on;
set linesize 80;
set pagesize 60;
set newpage 0;
rpad(c.name||':',11)||rpad(' current logons='||
a.name = 'logons cumulative'
ttitle "dbname Database|UNIX/Oracle Sessions";
set heading off;
select 'Sessions on database '||substr(name,1,8) from v$database;
set heading on;
SEE CODE DEPOT FOR FULL SCRIPTS
b.paddr = a.addr
order by spid;
set heading off;
select 'To kill, enter SQLPLUS> ALTER SYSTEM KILL SESSION',
''''||'SID, SER#'||''''||';' from dual;
Capacity planning for UNDO Segments
UNDO segments are used to store
information on changed rows until those rows are rolled back or
committed. If a rollback occurs, either from manual initiation using a
ROLLBACK command or due to abnormal termination of a user process due
to errors or failures, the data in the UNDO segment is used to
restore the database to the state before the transaction began.
The care and feeding or UNDO segments is
another empirical process. A few general observations on undo sizing:
1. If your users are doing heavy DML
operations make sure there are enough properly sized rollback segment
extents to allow each user performing DML to have an UNDO segment
extent without waiting.
2. If you expect heavy DML loads, have
multiple UNDO tablespaces spread across multiple disk
3. Set the default sizing in a UNDO tablespace such that you can simply issue a CREATE ROLLBACK
SEGMENT command specifying the tablespace and you don't have to worry
about the STORAGE specification.
4. Try to limit the number of simultaneous
users per UNDO to four. Size rollback segments such that:
a. INITIAL=NEXT=size of average transaction
b. OPTIMAL=size of average large transaction
(rounded up to the next NEXT value)
c. MINIMUM EXTENTS = OPTIMAL/INITIAL.
5. For large batch transactions create special
large extent rollback segments in a separate tablespace from the other
rollback segments, only bring them online when needed and use SET
TRANSACTION command to assign them to specific transactions.
6. Try to avoid running large batch
transactions simultaneously with OLTP or smaller transactions.
Generally you won't know the size of the
average transaction, average large transaction or number of
simultaneous DML users until the application has been running for a
while. Use the DBA_ROLLBACK_SEGS, V$ROLLSTAT and V$ROLLNAME views to
calculate the average values.
UNDO segments are sized and assigned in a
round-robin methodology. Each user gets assigned one extent to begin
with which is used in a circular fashion if possible. When a user
overwrites his own data it is called a WRAP. If the users data needs
exceed the size of one UNDO extent then a second is
assigned, this is called an EXTEND.
When a user forces a rollback
segment to extend beyond the OPTIMAL setting for that rollback
segment, the next user to use the rollback segment must wait for the
rollback segment to go back to the OPTIMAL setting, this is called a
When a WRAP, EXTEND or SHRINK occurs this
results in an UNDO WAIT (either block or header) condition. A WRAP
causes a very minor wait, an EXTEND into a newly assigned extent a
slightly more expensive wait and the SHRINK the most expensive wait.
By properly sizing rollback segment extents virtually all waits will
be of the lower expense WRAP and EXTEND type and not the SHRINK type.
Ideally all UNDO WAIT conditions should be caused by WRAP situations.