Notes

Outline

Rollback Segment Tuning

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 Segments: Usage

Rollback Segment Activity

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