达梦数据库学习笔记 — 等待事件

这是达梦数据库学习笔记的第5篇。重点看下等待事件。查看达梦官方手册发现也提供了类似Oracle OWI一样的等待事件接口；方便进行问题诊断和排查。

SQL> select PARA_NAME,PARA_VALUE,FILE_VALUE,DESCRIPTION from
2   v$dm_ini where PARA_NAME like '%ENABLE_MO%';
 
PARA_NAME            PARA_VALUE FILE_VALUE DESCRIPTION                                           
-------------------- ---------- ---------- ------------------------------------------------------
ENABLE_MONITOR       1          1          Whether to enable monitor
ENABLE_MONITOR_DMSQL 1          1          Flag of performance monitoring:sql or method exec time
RAC_ENABLE_MONITOR   1          1          Whether to monitor request time
DSC_ENABLE_MONITOR   1          1          Whether to monitor request time

首先需要打开ENABLE_MONITOR等参数，属性1 表示打开，0表示关闭。打开之后能够在v$session_event等视图中进行相关信息查看。

SQL> select * from v$event_name;
 
EVENT#      EVENT                     WAIT_CLASS# WAIT_CLASS 
----------- ------------------------- ----------- -----------
0           dbfile read               0           User I/O
1           dbfile multi read         0           User I/O
2           dbfile read wait          0           User I/O
3           dbfile flush wait         0           User I/O
4           dbfile remote read        1           System I/O
5           instance recovery read    1           System I/O
6           dbfile remote write       1           System I/O
7           dbfile discard write      1           System I/O
8           dbfile ckpt write         1           System I/O
9           dbfile extend             1           System I/O
10          shared memory pool busy   2           Concurrency
11          buffer busy wait          2           Concurrency
12          dict cache busy           2           Concurrency
13          plan cache busy           2           Concurrency
14          redo log system busy      2           Concurrency
15          redo log buffer busy      2           Concurrency
16          kernel busy               2           Concurrency
17          table lock busy           2           Concurrency
18          tid lock busy             2           Concurrency
19          parallel bdta pool busy   2           Concurrency
20          iid system busy           2           Concurrency
21          session system busy       2           Concurrency
22          public vpool busy         2           Concurrency
23          pseg queue busy           2           Concurrency
24          pseg stack busy           2           Concurrency
25          page s latch wait         2           Concurrency
26          page x latch wait         2           Concurrency
27          rlog flush wait           3           Rlog
28          commit wait               4           Transaction
29          table lock wait           4           Transaction
30          trxid lock wait           4           Transaction
31          dead lock                 4           Transaction
32          transaction system busy   2           Concurrency
33          transaction view busy     2           Concurrency
34          purge system busy         2           Concurrency
35          file system busy          2           Concurrency
36          asm system busy           2           Concurrency
37          lbs ctl busy              2           Concurrency
38          gtv system busy           2           Concurrency
39          out of share mem pool     5           Memory
40          out of share coldata pool 5           Memory
41          network send wait         6           Network
42          network recv wait         6           Network
43          preload wait              7           Preload
44          preload IO wait           7           Preload
 
45 rows got
 
SQL> select WAIT_CLASS#,WAIT_CLASS,count(1) from v$event_name group by WAIT_CLASS#,WAIT_CLASS
2   order by 1;
 
WAIT_CLASS# WAIT_CLASS  COUNT(1)            
----------- ----------- --------------------
0           User I/O    4
1           System I/O  6
2           Concurrency 24
3           Rlog        1
4           Transaction 4
5           Memory      2
6           Network     2
7           Preload     2
 
8 rows got

从查询来看，达梦8 中支持45种等待事件。其中有10类跟IO相关；绝大部分是跟并发有关;占了24种。 从官方手册中国摘取了关于等待事件的解释：

dbfile read:读用户数据文件;
dbfile multi read:批量读用户数据文件;
dbfile read wait:写用户数据文件;
dbfile remote read: DSC引起的读磁盘;
instance recovery read: recorvery引起的读磁盘;
dbfile remote write:DSC引起的写磁盘;
dbfile discardwrite:buf不够引起的写磁盘;
dbfile ckpt write:ckpt引起的写磁盘;
dbfile extend: 扩库文件引起的写磁盘;
shared memory pool busy:共享内存并发使用冲突;
buffer busy wait:buffer 并发使用冲突;
dict cache busy: 字典cache并发使用冲突;
plan cache busy: 计划cache并发使用冲突;
redo log system busy:redo log 系统并发冲突;
redo log buffer busy:redo log 缓冲区并发冲突;
kernel busy: kernel 并发冲突;
table lock busy:表锁系统 并发冲突;
tid lock busy:行锁系统并发冲突;
parallel bdta pool busy:并行bdta pool 并发冲突;
iid system busy:iid 系统并发冲突; 
session system busy:session 系统并发冲突;
public vpool busy:公共 vpool 并发冲突;
pseg queue busy:pseg队列并发冲突;
pseg stack busy:pseg堆栈并发冲突;
page busy wait: 数据页并发冲突;
table lock wait:事务间表锁等待发生;
trxid lock wait:事务间行锁等待发生;
dead lock: 事务间产生死锁;
transaction system busy: 事务系统并发冲突;
transaction view busy: 事务可见性视图并发冲突;
purge system busy: purge 系统并发冲突;
file system busy:文件 系统并发冲突;
asm system busy:ASM 系统并发 冲突; 
out of share mem pool:共享内存池不 足;
out of share coldata pool:共享 coldata 池不足:
network send wait:网络发送等待; 
network recv wait:网络接收等待

同样v$session_wait/v$session_wait_history 类似Oracle的ASH，也提供了一些详细信息，如P1，P2，P3，P4等。可以进行具体信息跟踪下钻。

不过从官方手册来看，这个解释也是过于简单了，基本上是说了等于白说。。总的来说还是非常简单，至少比Oracle动辄上千等待事件好多的了。。。

关于等待事件，目前来看比较简单，后面有问题再进行深入研究吧。个人觉得这一块儿实际上应该是个难点，包括Oracle也是一样。