Performance Tuning - Question 098

Which type of transaction should you assign to a specific large rollback segment?

A. Batch jobs that modify many rows.

B. Long running serializable transactions.

C. Long running reports, to avoid ‘snapshot too old’ errors.

D. Discrete transactions that modify many rows in the same block.

Answer: A

Explanation:

You need to assign a batch jobs modifying many rows to specific large rollback segment using SET TRANSACTION command.

Incorrect Answers

B: Long running serializable transactions do not require large rollback segments.

C: Reports do not modify any table records. So they do not need specific rollback segment.

D: Discrete transactions that modify many rows in the same block will not cause rollback segment contention.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 187-188

Chapter 3: SQL Application Tuning and Design