Chinaunix首页 | 论坛 | 博客
  • 博客访问: 547727
  • 博文数量: 128
  • 博客积分: 4000
  • 博客等级: 上校
  • 技术积分: 1345
  • 用 户 组: 普通用户
  • 注册时间: 2008-01-22 21:43
文章分类

全部博文(128)

文章存档

2009年(30)

2008年(98)

我的朋友

分类: Oracle

2009-05-31 19:54:22

今天在做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等待事件消失
阅读(5157) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~