SMON: Restarting fast_start parallel rollback

My one of the client reported me database hang and slow response on update/insert queries and ERP almost stopped, on diagnosis, I came to know huge transaction report have killed before commit. So database was busy and consuming almost CPU count for recovering all uncommitted transactions.

What is Fast Start Parallel Rollback?

In fast-start parallel rollback, 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 useful 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, 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.

Because of this contention and the perceived slowness and ‘hang’ like symptoms (the database may seem to hang, SMON and parallel query slaves may be seen to take all the available CPU), DBA intervention may be taken. If a large transaction is terminated, the cleanup may also take time.

Solution

Disable parallel roll back by setting “FAST_START_PARALLEL_ROLLBACK” parameter to FALSE:

SQL> alter system set FAST_START_PARALLEL_ROLLBACK=FALSE scope=both;

FAST_START_PARALLEL_ROLLBACK:

This parameter is used to specifies the degree of parallelism in order to recover terminated transactions.

Possible values:

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

Note: In case of above parameter change then transaction recovery will stop and start with new degree of parallelism

Stay Tune. 🙂

Leave a Reply

%d bloggers like this: