wait for a undo record 等待事件

某天在开发测试库中，发现数据库缓慢，内存被吃满，我们查看进程的内存使用率

ps -eo ppid,pid,user,%mem,vsz,rss,comm,args --sort rss

    1  3689 oracle   10.1 3816784 819120 oracle        ora_p004_sinitek
    1  3681 oracle   10.1 3816784 822484 oracle        ora_p002_sinitek
    1  3677 oracle   10.1 3816784 822532 oracle        ora_p001_sinitek
    1  3693 oracle   10.1 3816784 825156 oracle        ora_p005_sinitek
    1  3685 oracle   10.1 3816784 825520 oracle        ora_p003_sinitek
    1  3697 oracle   10.2 3816784 832316 oracle        ora_p006_sinitek
    1  3701 oracle   10.2 3816804 833720 oracle        ora_p007_sinitek
    1  3673 oracle   16.4 3816784 1331024 oracle       ora_p000_sinitek
    1  3753 oracle   17.1 3827600 1388388 oracle       ora_j000_sinitek
    1  3527 oracle   20.9 3846652 1702192 oracle       ora_dbw0_sinitek

 可以发现ora_p00x的这8个进程占用了大量的内存

在网上搜索，只说这些是并行的进程，并没有说这些进行有什么用

这个时候，我们查看系统的当前等待事件：

SYS@sinitek>select event,count(*) from v$session_wait group by event;

EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
SQL*Net message from client                                              82
wait for a undo record                                                    5
log file sync                                                             3
db file async I/O submit                                                  2
read by other session                                                     1
rdbms ipc message                                                         9
db file sequential read                                                  13
pmon timer                                                                1
wait for stopper event to be increased                                    1
jobq slave wait                                                           1
Streams AQ: qmn slave idle wait                                           1

EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
log file parallel write                                                   1
Space Manager: slave idle wait                                            1
SQL*Net message to client                                                 1
Streams AQ: qmn coordinator idle wait                                     1
Streams AQ: waiting for time management or cleanup tasks                  1
VKTM Logical Idle Wait                                                    1
DIAG idle wait                                                            2
SGA: MMAN sleep for component shrink                                      1

 

其中wait for undo record引起了我的注意

 我们查询回滚处理的v$FAST_START_SERVERS视图

SYS@sinitek>select * from V$FAST_START_SERVERS;

STATE       UNDOBLOCKSDONE        PID XID
----------- -------------- ---------- ----------------
RECOVERING           14447         20 07000B00FCB02A00
RECOVERING               0              21 07000B00FCB02A00
RECOVERING               0              22 07000B00FCB02A00
RECOVERING               0              23 07000B00FCB02A00
RECOVERING               0              24 07000B00FCB02A00
RECOVERING               0              25 07000B00FCB02A00
RECOVERING               0              26 07000B00FCB02A00
RECOVERING               0              27 07000B00FCB02A00

 

这个的数量也是8，跟上面p00x的进程的数量正好吻合，他们之间是不是有什么联系

SYS@sinitek>select spid from v$process where pid in (select pid from v$fast_start_servers);

SPID
------------------------
3673
3677
3681
3685
3689
3693
3697
3701

可以发现，这些进程就是p00x这8个进程，说明p00x这8个进程就是用来进行事务回滚恢复的

我们可以通过另外一个视图来查看进程回滚恢复的进度 v$fast_start_transactions;

SYS@sinitek>select * from v$fast_start_transactions;

       USN        SLT        SEQ STATE            UNDOBLOCKSDONE UNDOBLOCKSTOTAL        PID    CPUTIME  PARENTUSN  PARENTSLT  PARENTSEQ XID              PXID            RCVSERVERS
---------- ---------- ---------- ---------------- -------------- --------------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ----------
        10         28    3441697 RECOVERING                  403          415471         32        102          0          0          0 0A001C0021843400 0000000000000000          8

应该是开发人员，在进行大事务处理的时候造成的这种大量的回滚，回滚产生的原因我们不再深究……

关于产生 wait for a undo record 的原因 根据网友 dba老菜的总结

1.大事务运行过程中被异常终止是一件比较严重的事情，死事务的回滚可能会占严重消耗的系统资源。
2.cpu争用回滚段造成等待事件：wait for a undo record
3. fast_start_parallel_rollback参数
false  -- 即是关闭parallel rollback功能
low    -- 也是10g的默认值，该值含有是最大的rollback进程为2*cpu_count个
high   -- 当设置为该值时，最大的rollback进程为4*cpu_count个

在cpu争用比较严重的情况下，有的时候使用串行回滚比并行回滚更有效率

SYS@sinitek>show parameter fast_st

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
fast_start_parallel_rollback         string      LOW

 

SYS@sinitek>alter system set fast_start_parallel_rollback=false scope=spfile;
SYS@sinitek>shutdown immediate;  
SYS@sinitek>startup;  

--end;