After we have killed the user who has ran a delete command for more than 5 hours, the parallel recovery has started with 47 processes active. The whole production server was under heavy load an massive delays occured on all our PROD databases.
After we have killed the user who has ran a delete command for more than 5 hours, the parallel recovery has started with 47 processes active. The whole production server was under heavy load an massive delays occured on all our PROD databases.
The problem was solved by killing all the p0xx processes and recovery continued with only one process (this one process was faster than all 47 parallel processes).
Here is the step-by-step I have found on the metalink (238507.1).
Cause:
There are cases where parallel transaction recovery is not as fast as serial
transaction recovery, because the pq slaves are interfering with each other.
This depends mainly on the type of changes that need to be made during rollback
and usually may happen when rolling back INDEX Updates in parallel.
Solution:
1. Find SMON's Oracle PID:
SQL> select pid, program from v$process where program like '%SMON%';
PID PROGRAM
---------- ------------------------------------------------
6 oracle@stsun7 (SMON)
2. Disable SMON transaction cleanup:
SQL> oradebug setorapid 'SMON's Oracle PID';
SQL> oradebug event 10513 trace name context forever, level 2
3. Kill the PQ slaves that are doing parallel transaction recovery. (kill -9)
select SPID from V$PROCESS where PID in (select PID from V$FAST_START_SERVERS);
4. Turn off fast_start_parallel_rollback:
alter system set fast_start_parallel_rollback=false;
5. Re-enable transaction recovery as it was disabled in step 2.
SQL> oradebug setorapid 'SMON's Oracle PID';
SQL> oradebug event 10513 trace name context off
The problem was solved by killing all the p0xx processes and recovery continued with only one process (this one process was faster than all 47 parallel processes).
Here is the step-by-step I have found on the metalink (238507.1).
Cause:
There are cases where parallel transaction recovery is not as fast as serial
transaction recovery, because the pq slaves are interfering with each other.
This depends mainly on the type of changes that need to be made during rollback
and usually may happen when rolling back INDEX Updates in parallel.
Solution:
1. Find SMON's Oracle PID:
SQL> select pid, program from v$process where program like '%SMON%';
PID PROGRAM
---------- ------------------------------------------------
6 oracle@stsun7 (SMON)
2. Disable SMON transaction cleanup:
SQL> oradebug setorapid 'SMON's Oracle PID';
SQL> oradebug event 10513 trace name context forever, level 2
3. Kill the PQ slaves that are doing parallel transaction recovery. (kill -9)
select SPID from V$PROCESS where PID in (select PID from V$FAST_START_SERVERS);
4. Turn off fast_start_parallel_rollback:
alter system set fast_start_parallel_rollback=false;
5. Re-enable transaction recovery as it was disabled in step 2.
SQL> oradebug setorapid 'SMON's Oracle PID';
SQL> oradebug event 10513 trace name context off