“Wait for a undo record” OR “Wait for stopper event to be increased” database wait events

We faced a scenario in our database where users were complaining about database performance. Database seems to be hanging and queries were either stuck or not running at all.

It was also seen that UNDO tablespace consumption was increasing.

Dump was showing significant waits with wait event messages like “Wait for a undo record” and “Wait for stopper event to be increased”.

Database was monitored closely from OEM and as can be seen below something was not right with SMON process.

The wait events “Wait for a undo record” and “Wait for stopper event to be increased” are mainly meant for parallel transaction recovery.

In case of a big transaction getting rolled back, fast-start parallel rollback gets kicked off and the background process SMON acts as a coordinator and rolls back a set of transactions in parallel using multiple server processes.
Fast start parallel rollback is mainly used by Oracle server when a system has transactions that run a long time before a commit, especially parallel Inserts, Updates, Deletes operations. When SMON discovers that the amount of recovery work is above a certain threshold then it automatically begins parallel rollback by dispersing the work among several parallel processes.

There are cases where parallel transaction recovery is not as fast as serial transaction recovery because the PQ slaves may interfere with each others work by contending for the same resource. With such a transaction rollback performance may be worse in parallel when compared to a serial rollback.

Now this contention can cause a perceived slowness and ‘hang’ like symptoms .Also as seen above SMON and parallel query slaves have taken all the available CPU.

Quick Solution is to disable parallel rollback by setting the following parameter

fast_start_parallel_rollback = false

Please note that setting this parameter dynamically can cause problems on a busy instance with a lot of active transaction work and it is safer to set this with an instance restart so as not to change the rollback strategy on active transactions.

Basics of FAST_START_PARALLEL_ROLLBACK

FAST_START_PARALLEL_ROLLBACK specifies the degree of parallelism used when recovering terminated transactions. When using fast-start parallel rollback, the background process SMON is used as a coordinator and rolls back sets of transactions in parallel, using multiple server processes.

There can be three different values for this parameter:

FALSE: Parallel rollback is disabled
LOW: Limits the maximum degree of parallelism to 2 * CPU_COUNT
HIGH : Limits the maximum degree of parallelism to 4 * CPU_COUNT

If you change the value of this parameter, then transaction recovery will be stopped and restarted with the new implied degree of parallelism.

Reference:

How to Disable Parallel Transaction Recovery When Parallel Txn Recovery is Active (Doc ID 238507.1)