Transcript IT 21003 Database Administration

IT 21003 Database Administration
Section 05
Rollback Segments: An Introduction

What are they?
Space within a database used to store “before”
images
 Made up of extents and blocks (similar to data
segments)
 Consist of rollback entries

 A rollback
entry has the necessary information to undo
the changes made by one transaction
Rollback Segments: An Introduction

What are they used for?

Rollback of transactions
 Read Consistency
 Recovery
Rollback Segments: Structure
Rollback segments contain rollback entries,
which consist of


Transaction ID of the transaction, which makes
the change to the database
 Block information about the affected blocks
 The “before” image – an inverse of the transaction
Rollback Segments: Structure
Consist of extents and treated as a “ring of
extents” for cyclic reuse

Extents may be freed up for “normal” use when
the rollback segment is

Dropped
Shrunk to its normal size

Each rollback segment requires a least two
extents
Rollback Segments: Structure
The rollback entries require protection using
the redo logs


They are stored within the database
 Needed for rollback when recovering from media
failure
Rollback Segments: Usage

When are rollback segments required?

Deadlock or statement execution error
 Rollback to a savepoint
 Rollback of a transaction due to user request
 Rollback of a transaction due to abnormal process
termination
 Rollback of uncommitted transactions on instance
failure
 Read-consistent snapshots – need old versions of
data and index
Rollback Segments: Usage

How big should they be?

Rollback segment sizing suffers a fundamental
tension
Short transactions – small rollback segments
 Long transactions – large rollback segments

Rollback Segments: Requirements

How many?

The SYSTEM tablespace requires its own special
rollback segment
 At least one additional rollback segment is
required for databases using more then one
tablespace
 The quantity depends entirely on the transaction
traffic
Rollback Segments: Requirements
Oracles recommended number of rollback
segments:

Number of concurrent transactions Recommended rollback segments
Fewer than 16
4
Between 16 and 31
7
Greater than 31
5 but never more than 50
Rollback Segments: Public and Private

PUBLIC rollback segments

Used by any instance accessing a database in
parallel server mode
 Declared as public in
Create Public Rollback Segment
Once “claimed” by an instance, cannot be used by
other instances

Rollback Segments: Public and Private

PRIVATE rollback segments

Limited for use by only one instance accessing the
database
 Explicitly listed in the parameter file and picked up
by the instance on startup
 Lie dormant if no instance specifies them in the
parameter file
Rollback Segments: Public and Private

The rollback_segments parameter

Example:
rollback_segments = rbs_1, rbs_2

Default value is NULL, causing the instance to use
only the SYSTEM rollback segment plus any
available public rollback segments
Rollback Segments: Usage

How are rollback segments acquired?

On startup, the instance consults the list of private
rollback segments in the parameter file and
acquires all those specified
 If the number acquired does not satisfy the ratio:
Transactions/Transactions_Per_Rollback_Segment
65/20 then Oracle attempts to acquire 4 rollback
segments
it attempts to acquire any PUBLIC rollback
segments within the database until the ratio is
reached
If the ratio is not satisfied, the segments are used
“as-is”
Rollback Segments: Creating Rollback Segments

Create [Public] Rollback Segment rs_name

[Tablespace tablespacename]
 [Storage ( [Initial n] [Next n] [Minextents n]
 [Maxextents n] [Optimal n] ) ];
Rollback Segments: Creating Rollback Segments
It is recommended to set INITIAL equal to
NEXT in order to make all extents the same
size


Size of extents should be

Small enough to minimize page faulting in the buffer
cache
 Large enough to minimize excessive recursive SQL due
to dynamic allocation of space and to support read
consistency
 After
creation, rollback segments must be
altered to be brought online for use
 Alter
Rollback Segment rs_name Online;
Rollback Segments: Guidelines
Must have a minimum of two extents
(preferably more) on creation


Set MINEXTENTS to a value between 8 and 20
 MAXEXTENTS should be “high” to cater to large
normal transactions
Initial default value for MAXEXTENTS depends on the
value of DB_BLOCK_SIZE

Transactions use rollback segments on an extentby-extent basis, so the value of INITIAL should be
equal to NEXT
Rollback Segments: Guidelines
Use PRIVATE rollback segments when
possible


PUBLIC rollback segments are mainly used in
parallel server mode
 Make a list of them in the parameter file

Cannot set a value for PCTINCREASE

It is set by Oracle to Zero
Rollback Segments: Guidelines

Make all rollback segments the same size

Rollback segments allocated to transactions on a
round-robin basis (Cyclic Reuse)
 Could define on large rollback segment and
manually direct large transactions to it

Include the following statement at the start of the
transaction:
 Set Transaction Use Rollback Segment rs_name;
Rollback Segments: Guidelines

Size of rollback segment – a rule of thumb

On average, each rollback segment should be
about 10 percent of the size of the largest table
 So:

Total size of rollback segment = T
 Number of Initial extents specified = n
 Then size of each extent(s) = T/n
Rollback Segments: Positioning
Rollback segments are critical for normal
running of a database


Loss of rollback segment(s) can interrupt
database availability
Rollback segments may be stored in any
tablespace


SYSTEM rollback segment must be in the
SYSTEM tablespace
Could store all rollback segments in the
SYSTEM tablespace

Rollback Segments: Positioning

Store a rollback segment in each tablespace

Balances I/O but makes every disk critical and is
not recommended
 Tablespaces with active rollback segments cannot
be taken offline
Store all rollback segments on a disk of their
own this is the recommended way


Create a tablespace for rollback segments
 Makes it easier to take tablespaces offline
Rollback Segments: OPTIMAL Clause
Your chance to specify an optimum working
size for a rollback segment


OPTIMAL is the amount of residual space to
which rollback segments will truncate when inactive
extents are discovered by transactions
Small values of OPTIMAL help to keep
rollback segments small enough to fit in
memory


Suitable for systems with high frequency of short
transactions
 Do not set OPTIMAL such that the rollback
segments shrinks to below 8 to 20 extents
Rollback Segments: OPTIMAL Clause
Large values reduce the number of shrink and
extend operations (Dynamic Space Allocation)


Suitable for large transactions or long-running
queries
v$rollstat – provides information about rollback
segments

Rollback Segments: v$rollstat table
Select *
 From v$rollstat;


Contains information about:

Current size and average active size of rollback
segment
 Number of shrink and extend operations
 Largest size to which the rollback segment has
extended (represented by HWMSIZE known as the high
watermark
Rollback Segments: OPTIMAL Clause

Setting Guidelines for OPTIMAL
Shrinks Avg Size
Shrunk
High
High
Periodic long transactions could cause this, set
OPTIMAL higher until shrinks are low
High
Low
OPTIMAL is too small (too many shrinks)
Low
Low
If average active size = OPTIMAL then OPTIMAL is
Ok else OPTIMAL is too large (not many shrinks
being performed)
Low
High
OPTIMAL is set correctly – Do Nothing
Rollback Segments: Setting OPTIMAL
Rollback segments can also be manually
shrunk

 Alter
Rollback Segment rs_name Shrink To 2M;
Do not set a value for OPTIMAL for the
SYSTEM rollback segment

Rollback Segments: How Transactions Use them
On initiation a transaction is allocated a
rollback segment on a round-robin (cyclic
reuse) basis


Once a rollback segment is allocated, the
transaction can use no other
 The transaction places its ID in a transaction table
held in the header block of the rollback segment

Usual to have a number of rollback segments to
prevent transactions from queuing and waiting for others
to insert their IDs
Rollback Segments: How Transactions Use them
Each rollback segment has cyclically reusable
extents and a head and tail pointer

The “Head” is the position at which the new
rollback information is to be written
 The “Tail” is the position of the oldest active data
written by an uncommitted transaction (cannot yet
be overwritten)


The Head cannot proceed past the Tail
Rollback Segments: How Transactions Use them
 A transaction
writes rollback data into the
rollback segment on an extent-by-extent basis

Starts writing into the current extent
This is the extent containing the “Head” pointer
When the extent fills – the head moves on to the
next extent


If the next extent is not yet free (contains the tail), a
new extent is allocated
Extents can be shared by transactions, but not
blocks within the extents
 Inactive extents contain no active rollback
entries from transactions

Rollback Segments: Removal of Extents
 A rollback
segment may shrink only when an
extent fills and the Head is able to move into the
next extent
 At
this point, further “Next” extents are
investigated and if they are also inactive, they are
deallocated from the rollback segment until the
OPTIMAL size is reached
 In this way server processes looking for free
extents deallocate the oldest inactive extents first
 This means it could be some time before the
rollback segment actually shrinks
Rollback Segments: Processing
Rollback Segment Phase 1
Extent 1
Transaction
1
Transaction
2
Extent 4
Extent 2
Extent 3
Active Extent without space
Active Extent with space
Rollback Segments: Processing
Rollback Segment Phase 2
Extent 1
Transaction
1
Transaction
2
Extent 4
Extent 2
Extent 3
Active Extent without space
Active Extent with space
Rollback Segments: Processing
Rollback Segment Phase 3, Option 1
Available Extents
Extent 2
Transaction
3
Transaction
4
Extent 1
Extent 3
Extent 1 has become inactive
Extent 4
Active Extent without space
Active Extent with space
Rollback Segments: Processing
Rollback Segment Phase 3, Option 2
Extent 1 remains active
No Available Extents
Extent 1
Transaction
3
Extent 5
Extent 2
Transaction
4
Extent 4
Active Extent without space
Active Extent with space
Extent 3
Rollback Segments: Dropping

Statement

Drop [Public] Rollback Segment rs_name;
 Performed when the rollback segment extents
have caused free-space fragmentation, or when it
needs to be relocated
 All storage is returned to free space
 The rollback segment must be Offline
 Remember to remove its name from the
parameter file
Rollback Segments: Manipulation
To set a rollback segment offline (unavailable
for transactions)

 Alter
[Public] Rollback Segment rs_name Offline;
 The segment will not actually go offline until all its
active transactions have completed
The SYSTEM rollback segment cannot be
taken offline
 Manipulation is similar to tables

Rollback Segments: “Snapshot Too Old” Error
This error normally occurs when a longrunning query needs to access rollback
information to obtain a consistent view of the
data


Oracle always presents queries with a consistent
set of data from a combination of data in the
database tables and rollback segments
 Transactions are given priority in the use of
rollback segments
 A transaction
can overwrite data in inactive extents
even if that information may be needed to construct a
read-consistent view for queries
Rollback Segments: “Snapshot Too Old” Error
This is the reason for maintaining a
reasonable number of extents in a rollback
segment

 Avoids
a rollback segment wrapping around too
quickly on the extents

When the “Before Image” doesn’t exists
Questions?