Wait for stopper event to be increased

Wait Event: Wait for stopper event to be increased:

Operating System AIX 5.3.0.0 5300-05 (64-bit)
Oracle 10.2.0.3
Two node RAC

On 02/28/2008, OEM suddenly started showing wait events in the category “Others”, on drill down we found a wait even which I haven’t came across before. Wait event “Wait for stopper event to be increased”

A quick search on the metalink for the wait event came across  Doc ID:  Note:464246.1,
which had similar

Symptoms:
———
Database is hanging. Undo tablespace is growing.

The hang encountered because of parallel transaction recovery as the systemstate dump shows the significant waits for “Wait for a undo record” and “Wait for stopper event to be increased”.

Solution:
———-
To disable the parallel rollback by setting the following parameterfast_start_parallel_rollback = falseExplaination:
————– Sometimes Parallel Rollback of Large Transaction may become very slow. After killing a large running transaction (either by killing the shadow process or aborting the database) then database seems to hang, or smon and parallel query servers taking all the available cpu.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 comitting, 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 are interfering with each other. It looks like the changes made by this transaction cannot be recovered in parallel without causing a performance problem. The parallel rollback slave processes are most likely contending for the same resource, which results in even worse rollback performance compared to a serial rollback.Our database has 29GB of undo tablespace.

We implemented the solution,  and the problem disappeared.

ops$oracle@DB> show parameter fast_start_parallel_rollback

NAME                                 TYPE        VALUE
———————————— ———– ——————————
fast_start_parallel_rollback         string      LOW
ops$oracle@DB> alter system set fast_start_parallel_rollback = false ;
System altered.

ops$oracle@DB> 
alter system set fast_start_parallel_rollback = false scope=both;

System altered.

In the OEM graph, we see problem resurfaced around 1:30 pm after we had made the change at 1:25 pm, this happened because we changed back the  value of  fast_start_parallel_rollback=LOW ( defaultin our system) to see if the parameter change actually made the impact and answer was YES.