mysql 查看等待事件_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等待事件消失

阅读(4247) | 评论(0) | 转发(0) |