10
Copyright © Oracle Corporation, 2002. All rights reserved.
Tuning Undo Segments
10-2
Copyright © Oracle Corporation, 2002. All rights reserved.
Objectives
After completing this lesson, you should be able to do
the following:
•
Describe the concept of automatic
undo management
•
Create and maintain the automatic managed
undo tablespace
•
Set the retention period
•
Use dynamic performance views to check rollback
segment performance
•
Reconfigure and monitor rollback segments
•
Define the number and sizes of rollback segments
•
Allocate rollback segments to transactions
10-3
Copyright © Oracle Corporation, 2002. All rights reserved.
Undo Segments: Usage
Transaction
rollback
Transaction

recovery
Undo (Rollback)
segment
Read
consistency
Control
files
Data files
Redo log
files
10-4
Copyright © Oracle Corporation, 2002. All rights reserved.
Using Less Undo Space Per Transaction
•
The design of the application should allow users
to commit transactions regularly.
•
Developers should not code long transactions.
10-5
Copyright © Oracle Corporation, 2002. All rights reserved.
Using Less Undo Space
•
Import
–
Set COMMIT = Y
–
Size the set of rows with the BUFFER keyword
•
Export: Set CONSISTENT = N
•

SQL*Loader operations: Set the commit intervals
with ROWS
•
Developers should make sure that the transactions
are not unduly long.
10-6
Copyright © Oracle Corporation, 2002. All rights reserved.
Automatic Undo Management
•
The automatic undo management feature
simplifies the management of undo segments.
•
Set the UNDO_MANAGEMENT parameter to:
–
AUTO for automatic undo management
–
MANUAL for managing rollback segments manually
•
The UNDO_RETENTION parameter specifies the time
(in seconds) to retain undo information.
10-7
Copyright © Oracle Corporation, 2002. All rights reserved.
Automatic Undo Management Tablespaces
•
Create a tablespace for automatic undo
management in one of the following ways:
–
Using the UNDO TABLESPACE clause in the
CREATE DATABASE command
–

Using the CREATE UNDO TABLESPACE command
•
MINIMUM EXTENT and DEFAULT STORAGE are
system generated for undo tablespaces.
•
Restrictions:
–
Database objects cannot be created in this
tablespace.
–
You can specify the data file and the
extent_management clause only.
10-8
Copyright © Oracle Corporation, 2002. All rights reserved.
Altering an Undo Tablespace
•
The ALTER TABLESPACE command can be used to
make changes to undo tablespaces.
•
The following example adds another data file to the
undo tablespace:
•
You cannot take an undo tablespace offline that
has an active undo segment.
ALTER TABLESPACE undotbs1
ADD DATAFILE ‘/u02/oradata/testdb/undotbs1_02.dbf’
AUTOEXTEND ON;
10-9
Copyright © Oracle Corporation, 2002. All rights reserved.
Switching Undo Tablespaces

•
A DBA can switch from using one undo tablespace
to another.
•
Only one undo tablespace per instance can be
assigned as active.
•
Switching is performed by using the
ALTER SYSTEM command:
ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2;
10-10
Copyright © Oracle Corporation, 2002. All rights reserved.
Dropping an Undo Tablespace
The DROP TABLESPACE command can be used to drop
an undo tablespace:
•
An undo tablespace can be dropped only if:
–
It is not the active undo tablespace
–
It is not utilized by an active transaction
•
Queries that require a read-consistent image of
undo data that is stored in an dropped undo
tablespace will return an error.
DROP TABLESPACE undotbs_2;
10-11
Copyright © Oracle Corporation, 2002. All rights reserved.
Setting UNDO_RETENTION
UNDO_RETENTION parameter is:

•
Specified in time (seconds)
•
A target value. If space is required committed data
will be overwritten.
•
Controls the amount of undo data to retain
after committing
10-13
Copyright © Oracle Corporation, 2002. All rights reserved.
Other Parameters for Automatic
Undo Management
•
UNDO_MANAGEMENT: Specifies whether the
database uses Auto or Manual mode
•
UNDO_TABLESPACE: Specifies a particular undo
tablespace to be used
•
UNDO_SUPPRESS_ERRORS: Set to True, this
parameter suppresses errors while attempting to
execute manual operations, such as ALTER
ROLLBACK SEGMENT ONLINE, while in Auto mode
10-15
Copyright © Oracle Corporation, 2002. All rights reserved.
Monitoring Automatic Undo Management
•
Use v$undostat view to monitor undo segments.
•
This view is available for both Manual and

Auto mode.
•
The undoblks column displays the number of
undo blocks allocated.
10-16
Copyright © Oracle Corporation, 2002. All rights reserved.
Using v$undostat
SQL> SELECT begin_time, end_time, undoblks,
2 txncount, maxquerylen
3 FROM v$undostat;
BEGIN_TIME END_TIME UNDOBLKS TXNCOUNT

25-oct-01:06:04 25-oct-01:06:14 234 12
25-oct-01:05:44 25-oct-01:05:54 587 21
25-oct-01:05:34 25-oct-01:05:44 1,187 45
25-oct-01:05:24 25-oct-01:05:34 346 15
25-oct-01:05:14 25-oct-01:05:24 642 23
……
10-17
Copyright © Oracle Corporation, 2002. All rights reserved.
Performance Manager: Rollback/Undo
10-18
Copyright © Oracle Corporation, 2002. All rights reserved.
Overview
Setting manual rollback segments is:
•
Optional in the Oracle database
•
Time consuming for the DBA
10-19

Copyright © Oracle Corporation, 2002. All rights reserved.
Rollback Segment Activity
Active extentInactive extent
4 3
1 2
>update
>update
>insert
>insert
>update
T2
T1
T1
T2
T3
T4
>update
>update
>insert
>insert
>update
10-20
Copyright © Oracle Corporation, 2002. All rights reserved.
Rollback Segment Header Activity
•
Rollback segment headers contain entries for their
respective transactions.
•
Every transaction must have update access.
4 3

1
2
T4
T5
T6
T2
T1
T3
10-21
Copyright © Oracle Corporation, 2002. All rights reserved.
Growth of Rollback Segments
Active extent
Inactive extent
1
2
4 3
1
2
3
4
5
New extent
10-22
Copyright © Oracle Corporation, 2002. All rights reserved.
Tuning Manually Managed
Rollback Segments
Goals in tuning rollback segments:
•
Transactions should never wait for access to
rollback segments.

•
Rollback segments should not extend during
normal running.
•
Users and utilities should try to use less rollback
per transaction.
•
No transaction should ever run out of
rollback space.
•
Readers should always see the read-consistent
images they need.
10-23
Copyright © Oracle Corporation, 2002. All rights reserved.
Data
Diagnostic Tools
4 3
1 2
SGA
Header
Statspack
output
v$rollstat
v$rollname
v$sysstat
v$system_event
v$waitstat
v$transaction
v$session
T3

T4
T5
10-24
Copyright © Oracle Corporation, 2002. All rights reserved.
SQL> SELECT class, count FROM v$waitstat
2 WHERE class LIKE '%undo%';
or
SQL> SELECT event, total_waits, total_timeouts
2 FROM v$system_event
3 WHERE event LIKE 'undo segment tx slot';
or
SQL> SELECT sum(waits)* 100 /sum(gets) "Ratio",
2 sum(waits) "Waits", sum(gets) "Gets"
3 FROM v$rollstat;
Diagnosing Contention for Manual
Rollback Segment Header
If the number of waits for any rollback header is less
than 1% of the total number of requests, then create
more rollback segments.
10-26
Copyright © Oracle Corporation, 2002. All rights reserved.
Guidelines: Number of Manual Rollback
Segments (RBSs)
•
OLTP: One RBS for four transactions
•
Batch: One rollback segment for each
concurrent job
Large rollback
Small RBS

SQL> SET TRANSACTION USE
2 ROLLBACK SEGMENT large_rbs;
Small RBS
Small RBSSmall RBS
Small RBS
10-28
Copyright © Oracle Corporation, 2002. All rights reserved.
Guidelines: Sizing Manual
Rollback Segments
Rollback segment 1 = Rollback segment 2
INITIAL = NEXT = 2
n
Mb
MINEXTENTS = 20
OPTIMAL = 20 * INITIAL
0.00
0.30
0.10
0.20
0.40
0.50
0 10 20 30 40
Probability of
extending
Number
of extents
10-29
Copyright © Oracle Corporation, 2002. All rights reserved.
Sizing Transaction Rollback Data
•

Deletes are expensive for rollback activity.
•
Inserts use minimal rollback space.
•
Updates use rollback space, depending on the
amount of data changed in the transaction.
•
Index maintenance adds rollback.
SQL> SELECT s.username, t.used_ublk,
2 t.start_time
3 FROM v$transaction t, v$session s
4 WHERE t.addr = s.taddr;