Objectives

After completing this lesson, you should be able to

do the following:

Use the dynamic performance views to check rollback segment performance

Reconfigure and monitor rollback segments

Define the number and sizes of rollback segments

Appropriately allocate rollback segments to transactions

Rollback Segment Header Activity

Rollback segment headers contain entries for their respective transactions.

Every transaction must have update access.

Growth of Rollback Segments

Transaction Types

Read-only

Tuning the 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.

No transaction should ever run out of rollback space.

Readers should always see the read-consistent images they need.

Diagnostic Tools for Tuning Rollback Segments

Diagnosing Rollback Segment Header Contention

The ratio of the sum of waits to the sum of gets should be less than 1%.

If not, create more rollback segments.

Diagnosing Rollback Segment Contention

The number of waits for any class should be less than 1% of the total number of requests.

If not, create more rollback segments.

Guidelines: How Many Rollback Segments?

OLTP: One RBS for four transactions

Batch: One rollback segment for each concurrent job

Guidelines: Sizing Rollback Segments

Sizing Transaction Rollback Data

Deletes are expensive.

Inserts use minimal rollback space.

Updates use rollback space depending on the number of columns.

Index maintenance adds rollback.

Sizing Transaction Rollback Data

Using Less Rollback

The design of the application should allow users to commit regularly.

Developers should not code long transactions.

Using Less Rollback

Import

Set COMMIT = Y

Size the set of rows with BUFFER

Export: Set CONSISTENT=N

SQL*Loader: Set the COMMIT intervals with ROWS

Possible Problems

Transaction fails for lack of rollback space

“Snapshot too old” error occurs if:

The Interested Transaction List in the block being queried has been reused, and the SCN in the block is newer than the SCN at the start of the query.

The transaction slot in the rollback segment header has been reused.

The undo data in the rollback segment has been overlaid after a commit.

Summary

In this lesson, you should have learned how to:

Avoid contention for rollback segment headers

Work out numbers and sizes of rollback segments

Monitor the rollback space used by transactions

Monitor the accurate value of the OPTIMAL storage parameter

Identify possible rollback segment problems


		After completing this lesson, you should be able to
		do the following:
		Use the dynamic performance views to check rollback segment performance
		Reconfigure and monitor rollback segments
		Define the number and sizes of rollback segments
		Appropriately allocate rollback segments to transactions


		Rollback segment headers contain entries for their respective transactions.
		Every transaction must have update access.


		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.
		No transaction should ever run out of rollback space.
		Readers should always see the read-consistent images they need.


	The ratio of the sum of waits to the sum of gets should be less than 1%.
	If not, create more rollback segments.


	The number of waits for any class should be less than 1% of the total number of requests.
	If not, create more rollback segments.


		OLTP: One RBS for four transactions
		Batch: One rollback segment for each concurrent job


		Deletes are expensive.
		Inserts use minimal rollback space.
		Updates use rollback space depending on the number of columns.
		Index maintenance adds rollback.


		The design of the application should allow users to commit regularly.
		Developers should not code long transactions.


		Import
			Set COMMIT = Y
			Size the set of rows with BUFFER
		Export: Set CONSISTENT=N
		SQL*Loader: Set the COMMIT intervals with ROWS


		Transaction fails for lack of rollback space
		“Snapshot too old” error occurs if:
			The Interested Transaction List in the block being queried has been reused, and the SCN in the block is newer than the SCN at the start of the query.
			The transaction slot in the rollback segment header has been reused.
			The undo data in the rollback segment has been overlaid after a commit.


		In this lesson, you should have learned how to:
		Avoid contention for rollback segment headers
		Work out numbers and sizes of rollback segments
		Monitor the rollback space used by transactions
		Monitor the accurate value of the OPTIMAL storage parameter
		Identify possible rollback segment problems