等待事件wait for a undo record 與 fast_start_parallel_rollback引數

今天一個客戶報資料庫執行速度很慢，登上去查。

檢查資料庫，發現資料庫中存在很多wait for a undo record等待事件。再檢查發現有很多的併發程式在執行。

通常，如果有很多併發程式，可以根據v$px_session檢視去檢視，檢視v$px_session檢視，發現所有的併發程式都是由smon程式導致（即qcsid列為smon程式的session id）

而smon程式的等待事件為wait for stopper event to be increased 

關於這個現象，查詢mos，可以看到相應的解釋和解決方法（464246.1）

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.  

Becasue 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 intervent

即smon程式在做大事務的回滾，預設引數fast_start_parallel_rollback引數為low，即回滾時會啟動2*CPU個數 個併發程式。而由於是使用併發，所以可能由於併發之間相互使用共同的資源，導致回滾速度更慢。

解決方法為將fast_start_parallel_rollback引數改為false

fast_start_paralllel_rollback引數是可以動態修改的，但是對於正在執行大量的回滾操作的資料庫例項來說，可能動態修改會導致一些問題（具體會是什麼問題，oracle官方文件並沒有說），正確修改方法參考官方文件（238507.1）

1.查詢smon程式ID

2.禁用smon程式的事務清理（Disable SMON transaction cleanup）

  oradebug setorapid 'SMON's Oracle PID';

  oradebug event 10513 trace name context forever, level 2

3.查詢V$FAST_START_SERVERS檢視，將所有smon啟用的併發程式殺掉

4.修改fast_start_parallel_rollback引數

  alter system set fast_start_parallel_rollback=false;

5.啟用smon程式的事務清理（enable transaction recovery）

  oradebug setorapid 'SMON's Oracle PID';

  oradebug event 10513 trace name context off

關於fast_start_paralllel_rollback引數的介紹：

FALSE indicates that parallel rollback is disabled     禁用併發回滾（注意是禁用併發回滾，不是禁用回滾。只是回滾的時候不使用併發）

LOW limits the number of rollback processes to 2 * CPU_COUNT     預設值

HIGH limits the number of rollback processes to 4 * CPU_COUNT

對於資料庫中的回滾事務，可以檢視下面的檢視監控：

V$FAST_START_SERVERS

V$FAST_START_TRANSACTIONS

分別檢視回滾的程式資訊和事務資訊

select pid from v$fast_start_servers where state='RECOVERING';

select spid from v$process where pid in (select pid from v$fast_start_servers where state='RECOVERING');

select * from v$fast_start_transactions;

Database Appears to Hang Waits for "Wait for a undo record" and "Wait for stopper event to be increased" Due to Parallel Transaction Recover (文件 ID 464246.1)

How to Disable Parallel Transaction Recovery When Parallel Txn Recovery is Active (文件 ID 238507.1)