wait for a undo record等待事件

最新推荐文章于 2021-07-22 18:05:03 发布

转载最新推荐文章于 2021-07-22 18:05:03 发布 · 1.3k 阅读

文章标签：

#数据库 #session #user #sql #file #events

Oracle 同时被 2 个专栏收录

523 篇文章

订阅专栏

Oracle EBS

416 篇文章

订阅专栏

本文详细分析了在数据库恢复过程中出现的waitforaundorecord等待事件，包括其产生的原因、影响及解决方法。通过查询数据库等待事件统计和fast_start_servers状态，发现大量事务处于恢复状态导致此等待事件频繁发生。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天在做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