Using Fast Start Parallel Rollback in Oracle | Oracle Parameters

Using Fast Start Parallel Rollback in Oracle Database

Oracle Parameters - Fast Start Parallel Rollback - Oracle Database

There are times when users disconnect from database without committing data and oracle database has to suffer the pressure of undoing all the changes that were made by those users. It's not that much of a issue in small databases and a small user group but can cause high CPU usage and hang issues in large databases with thousands of users (although highly experienced Oracle DBAs always configure the databases to avoid such issues effectively)

To make this recovery faster, we have a parameter named as FAST_START_PARALLEL_ROLLBACK in Oracle Database.

FAST_START_PARALLEL_ROLLBACK:

This parameter is used to specify 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

In fast-start parallel rollback (FSPR), the background process SMON performs as a coordinator and rolls back multiple transactions in parallel using multiple server processes. FSPR is useful in cases where a system has a lot of transactions that run a long time before the user made the commit, especially parallel Inserts, Updates, Deletes operations. The same feature is automatically managed by SMON when it sees that the burden is crossing the threshold limit.

To Enable this feature,

In Single Instance,

SQL> alter system set FAST_START_PARALLEL_ROLLBACK=LOW/HIGH scope=both;

In Oracle RAC,

SQL> alter system set FAST_START_PARALLEL_ROLLBACK=LOW/HIGH scope=both sid='*';

Note:-

There are cases when enabling this feature can also cause you hanging issues as a lot of parallel processes can caused resource contention causing deadlock situations to happen. So use the same keeping each and every perspective in mind.

To Disable this feature,

In Single Instance,

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

In Oracle RAC,

SQL> alter system set FAST_START_PARALLEL_ROLLBACK=FALSE scope=both sid='*';

I hope this helps !!