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

本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

这是达梦数据库学习笔记的第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

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

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:网络接收等待

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也是一样。

Tags: v$event_name, v$session_wait, v$session_wait_history, v$system_name, 等待事件
Posted in 体系结构, 达梦数据库 on 8月 27, 2021

You must be logged in to post a comment.

love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

Categories

Archives

最新评论

国内圈子

oracle security

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

Leave a Reply