Wait for a undo record
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 wait for a undo record[편집]
AWR report shows wait for a undo record event as top event
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------
wait for a undo record 147,295 7,316 50 34.8 Other <<<<<<<<<<<
db file sequential read 1,592,798 3,979 2 18.9 User I/O
log file sync 1,478,325 2,180 1 10.4 Commit
...
1.1 [원인][편집]
In parallel transaction recovery this wait-event is used in a number of places while waiting for some work.
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 secondary 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.
Because of this contention and the perceived slowness and 'hang' like symptoms (the database may seem to hang, SMON and parallel query secondary may be seen to take all the available CPU), DBA intervention may be taken. If a large transaction is terminated, the the cleanup may also take time.
1.2 [해결책][편집]
You can disable parallel rollback by setting the following parameter
fast_start_parallel_rollback = falseIf this is a one-time issue, reported after rollback of a huge transaction (s), you can remove this parameter after the transaction recovery.
BEWARE: that setting this parameter dynamically can cause problems on a busy instance with a lot of active transaction work and it is safer to set this with an instance restart so as not to change the rollback strategy on active transactions.