wait for a undo record等待事件

今天在做AWR分析时，碰到了个新的等待事件：wait for a undo record

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
wait for a undo record               54,998       3,557     65   43.0      Other
CPU time                                          1,687          20.4
db file sequential read             187,848       1,632      9   19.7   User I/O
wait for stopper event to be i        4,027         278     69    3.4      Other
read by other session                47,621         276      6    3.3   User I/O
          -------------------------------------------------------------

查看了下当前数据库的等待事件，有大量的的Txn Recovery Start，
看样子是kill的大事务，数据库正在恢复

SQL> select sid, event, wait_class from v$session_wait s where s.EVENT not in ('SQL*Net message from client', 'rdbms ipc message');
 
SID     EVENT                                                            WAIT_CLASS
---- ---------------------------------------------------------------- ----------------------------------------------------------------
2070    PX Deq: Txn Recovery Start                    Idle
2114    PX Deq: Txn Recovery Start                    Idle
2168    PX Deq: Txn Recovery Start                    Idle
2239    SQL*Net message to client                    Network
2249    PX Deq: Txn Recovery Start                    Idle
2264    jobq slave wait                            Idle
2283    wait for a undo record                        Other
2306    PX Deq: Txn Recovery Start                    Idle
2383    jobq slave wait                            Idle
2414    PX Deq: Txn Recovery Start                    Idle
2455    PX Deq: Txn Recovery Start                    Idle
2474    jobq slave wait                            Idle
2475    db file sequential read                        User I/O
2492    jobq slave wait                            Idle
2499    PX Deq: Txn Recovery Start                    Idle
2512    PX Deq: Txn Recovery Start                    Idle
2550    PX Deq: Txn Recovery Start                    Idle
2556    PX Deq: Txn Recovery Start                    Idle
2591    PX Deq: Txn Recovery Start                    Idle
2629    PX Deq: Txn Recovery Start                    Idle
2664    PX Deq: Txn Recovery Start                    Idle
2700    db file sequential read                        User I/O
2712    PX Deq: Txn Recovery Start                    Idle
2726    jobq slave wait                            Idle
2727    Streams AQ: waiting for time management or cleanup tasks    Idle
2735    Streams AQ: qmn coordinator idle wait                Idle
2736    Streams AQ: qmn slave idle wait                    Idle
2738    jobq slave wait                            Idle
2748    wait for stopper event to be increased                Other
 
29 rows selected

查看v$fast_start_servers：

SQL> select pid, state, undoblocksdone from v$fast_start_servers;
 
       PID STATE       UNDOBLOCKSDONE
---------- ----------- --------------
       157 RECOVERING            3123
       322 RECOVERING               0
       335 RECOVERING               0
       455 RECOVERING               0
       458 RECOVERING               0
       460 RECOVERING               0
       461 RECOVERING               0
       463 RECOVERING               0
       467 RECOVERING               0
       468 RECOVERING               0
       471 RECOVERING               0
       472 RECOVERING               0
       477 RECOVERING               0
       480 RECOVERING               0
       481 RECOVERING               0
       484 RECOVERING               0
 
16 rows selected
 
SQL> select pid, state, undoblocksdone from v$fast_start_servers;
 
       PID STATE       UNDOBLOCKSDONE
---------- ----------- --------------
       157 RECOVERING            3458
       322 RECOVERING               0
       335 RECOVERING               0
       455 RECOVERING               0
       458 RECOVERING               0
       460 RECOVERING               0
       461 RECOVERING               0
       463 RECOVERING               0
       467 RECOVERING               0
       468 RECOVERING               0
       471 RECOVERING               0
       472 RECOVERING               0
       477 RECOVERING               0
       480 RECOVERING               0
       481 RECOVERING               0
       484 RECOVERING               0
 
16 rows selected


当数据库恢复结束，wait for a undo record等待事件消失

from:http://blog.chinaunix.net/u2/60220/showart_1950669.html