Parallel Rollback may hang database, Parallel query servers get 100% cpu (Doc ID 144332.1)

Last updated on FEBRUARY 23, 2024

Applies to:

Symptoms

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. After killing shadow process or aborting the database the v$transaction entry is lost, so you cannot estimate by examining v$transaction.used_ublk how the rollback procedure proceeds. In parallel rollback checking v$lock you see SMON having one TX lock and x (where x is the init.ora -> parallel_max_servers value) PS locks (parallel secondary synchronization lock). Also each Parallel Query process has also 1 PS lock. And a lot of redo log activity is happening. In order to identify if this is your case check the following views: V$fast_start_servers -> provides information about all the recovery secondary performing parallel transaction recovery. This contains one row for each parallel query secondary. Column STATE shows the state of the server being IDLE or RECOVERING. Column UNDOBLOCKSDONE shows the percentage of the assigned work done so far. V$fast_start_transactions -> contains one row for each one of the transactions that Oracle is recovering in Parallel. Important columns here are: USN -> the undo segment number of the transaction (join with v$rollstat.usn and v$rollname.usn to find the rollback segment used, v$rollstat.xacts would show a value larger than 0). UNDOBLOCKSDONE -> the number of undo blocks completed in this transaction UNDOBLOCKSTOTAL -> the total number of undo blocks that need recovery CPUTIME -> the time for which recovery has progressed in seconds You may notice that UNDOBLOCKSDONE is not increasing or increases very slowly.

Changes

No changes

Cause