达梦数据库体系结构研究

总体来说，和Oracle差别不大， 也是由数据库和实例构成，
其中数据库指的是磁盘上存放在DM 数据库中的数据的集合，实例一般是由一组正在运行的DM 后台进程/线程以及一个大型的共享内存组成。

最大的差别在于Oracle是多进程而达梦数据库是单进程多线程模式
[[email protected] bin]# ps -ef|grep dm
avahi 1729 1 0 06:16 ? 00:00:00 avahi-daemon: registering [dm1.local]
root 1792 1 0 06:16 ? 00:00:00 rpc.idmapd
root 2296 1 0 06:16 ? 00:00:00 /usr/sbin/gdm-binary -nodaemon
root 2324 2296 0 06:16 ? 00:00:00 /usr/libexec/gdm-simple-slave --display-id /org/gnome/DisplayManager/Display1 --force-active-vt
root 2326 2324 0 06:16 tty1 00:00:26 /usr/bin/Xorg :0 -nr -verbose -auth /var/run/gdm/auth-for-gdm-mokwXu/database -nolisten tcp vt1
gdm 2411 1 0 06:16 ? 00:00:00 /usr/bin/dbus-launch --exit-with-session
root 2511 2324 0 06:18 ? 00:00:00 pam: gdm-password
dmdba 8700 1 0 13:12 pts/0 00:00:05 /opt/dmdbms/bin/dmserver /dm7/data/DAMENG/dm.ini -noconsole
dmdba 9565 1 0 13:56 pts/0 00:00:00 /opt/dmdbms/bin/dmap
root 10719 3043 0 15:34 pts/0 00:00:00 grep dm
[[email protected] bin]#
[[email protected] bin]# pstree -p|grep dm
|-dmap(9565)-±{dmap}(9567)
| |-{dmap}(9568)
| |-{dmap}(9569)
| |-{dmap}(9570)
| |-{dmap}(9571)
| |-{dmap}(9572)
| |-{dmap}(9573)
| |-{dmap}(9574)
| |-{dmap}(9576)
|-dmserver(8700)-±{dmserver}(8704)
| |-{dmserver}(8705)
| |-{dmserver}(8706)
| |-{dmserver}(8707)
| |-{dmserver}(8708)
| |-{dmserver}(8709)
| |-{dmserver}(8710)
| |-{dmserver}(8711)
| |-{dmserver}(8712)
| |-{dmserver}(8713)
| |-{dmserver}(8714)
| |-{dmserver}(8715)
| |-{dmserver}(8716)
| |-{dmserver}(8717)
| |-{dmserver}(8718)
| |-{dmserver}(8719)
| |-{dmserver}(8720)
| |-{dmserver}(8721)
| |-{dmserver}(8722)
| |-{dmserver}(8723)
| |-{dmserver}(8724)
| |-{dmserver}(8725)
| |-{dmserver}(8726)
| |-{dmserver}(8727)
| |-{dmserver}(8728)
| |-{dmserver}(8729)
| |-{dmserver}(8730)
| |-{dmserver}(8731)
| |-{dmserver}(8732)
| |-{dmserver}(8733)
| |-{dmserver}(8734)
| |-{dmserver}(8735)
| |-{dmserver}(8736)
| |-{dmserver}(8737)
| |-{dmserver}(8738)
| |-{dmserver}(8739)
| |-{dmserver}(8740)
| |-{dmserver}(8741)
| |-{dmserver}(8742)
| |-{dmserver}(8743)
| |-{dmserver}(8744)
| |-{dmserver}(8745)
| |-{dmserver}(8746)
| |-{dmserver}(8747)
| |-{dmserver}(8748)
| |-{dmserver}(8749)
| |-{dmserver}(8750)
| |-{dmserver}(8751)
| |-{dmserver}(8752)
| |-{dmserver}(8753)
| |-{dmserver}(8754)
| |-{dmserver}(8755)
| |-{dmserver}(8756)
| |-{dmserver}(8757)
|-gdm-binary(2296)—gdm-simple-slav(2324)-±Xorg(2326)
| `-gdm-session-wor(2511)—gnome-session(2527)-±abrt-applet(2670)
|-rpc.idmapd(1792)

SQL> Select name,thread_desc from v$threads;
行号 NAME THREAD_DESC

---

1 dm_qwatch_thd Thread for monitoring the exit/quit/ctrl-c signal in posix
2 dm_io_thd IO thread
3 dm_io_thd IO thread
4 dm_io_thd IO thread
5 dm_io_thd IO thread
6 dm_rsyswrk_thd Asynchronous archiving thread
7 dm_rsyswrk_thd Asynchronous archiving thread
8 dm_chkpnt_thd Flush checkpoint thread
9 dm_redolog_thd Redo log thread,used to flush log
10 dm_rapply_thd Log apply thread which receive redo-logs from primary site by standby site
11 dm_rsyswrk_thd Asynchronous archiving thread
12 dm_sqllog_thd Thread for writing dmsql dmserver
13 dm_purge_thd Purge thread
14 dm_hio_thd IO thread for HFS to read data pages
15 dm_hio_thd IO thread for HFS to read data pages
16 dm_hio_thd IO thread for HFS to read data pages
17 dm_hio_thd IO thread for HFS to read data pages
18 dm_tskwrk_thd Task Worker Thread for SQL parsing and execution for sevrer itself
19 dm_tskwrk_thd Task Worker Thread for SQL parsing and execution for sevrer itself
20 dm_tskwrk_thd Task Worker Thread for SQL parsing and execution for sevrer itself
21 dm_tskwrk_thd Task Worker Thread for SQL parsing and execution for sevrer itself
22 dm_tskwrk_thd Task Worker Thread for SQL parsing and execution for sevrer itself
23 dm_tskwrk_thd Task Worker Thread for SQL parsing and execution for sevrer itself
24 dm_tskwrk_thd Task Worker Thread for SQL parsing and execution for sevrer itself
25 dm_tskwrk_thd Task Worker Thread for SQL parsing and execution for sevrer itself
26 dm_tskwrk_thd Task Worker Thread for SQL parsing and execution for sevrer itself
27 dm_tskwrk_thd Task Worker Thread for SQL parsing and execution for sevrer itself
28 dm_tskwrk_thd Task Worker Thread for SQL parsing and execution for sevrer itself
29 dm_tskwrk_thd Task Worker Thread for SQL parsing and execution for sevrer itself
30 dm_tskwrk_thd Task Worker Thread for SQL parsing and execution for sevrer itself
31 dm_trctsk_thd Thread for writing trace information
32 dm_tskwrk_thd Task Worker Thread for SQL parsing and execution for sevrer itself
33 dm_wrkgrp_thd User working thread
34 dm_wrkgrp_thd User working thread
35 dm_wrkgrp_thd User working thread
36 dm_wrkgrp_thd User working thread
37 dm_wrkgrp_thd User working thread
38 dm_wrkgrp_thd User working thread
39 dm_wrkgrp_thd User working thread
40 dm_tskwrk_thd Task Worker Thread for SQL parsing and execution for sevrer itself
41 dm_wrkgrp_thd User working thread
42 dm_wrkgrp_thd User working thread
43 dm_wrkgrp_thd User working thread
44 dm_wrkgrp_thd User working thread
45 dm_wrkgrp_thd User working thread
46 dm_wrkgrp_thd User working thread
47 dm_wrkgrp_thd User working thread
48 dm_wrkgrp_thd User working thread
49 dm_wrkgrp_thd User working thread
50 dm_audit_thd Thread for flush audit logs
51 dm_tskwrk_thd Task Worker Thread for SQL parsing and execution for sevrer itself
52 dm_lsnr_thd Service listener thread
53 dm_sched_thd Server scheduling thread,used to trigger background checkpoint, time-related triggers
54 dm_sql_thd User session thread
54 rows got
已用时间: 68.593(毫秒). 执行号:7.

SQL> select sess_id ,sql_text from v$sessions where state=‘ACTIVE’;
行号 SESS_ID SQL_TEXT

---

1 197446648 select sess_id ,sql_text from v$sessions where state=‘ACTIVE’;
已用时间: 62.462(毫秒). 执行号:8.

内存区的参数修改

相对于Oralce 的 alter system set ……，达梦是sp_set_para_value(scope,para_name,para_value);
其中scope取值及含义1:修改配置文件和内存 2：只修改配置文件。而para_name我们可以观察分析一下
看看参数都有哪些类型
SQL> select type ,count() from v$parameter group by type;
行号 TYPE COUNT()

---

1 READ ONLY 75
2 SYS 128
3 IN FILE 163
4 SESSION 197
已用时间: 89.326(毫秒). 执行号:9.
经查询这些类型差异如下：
Sys/ session 动态参数，同时修改内存和配置文件
Read only 在数据库运行状态时，不能修改
In file 静态参数，修改配置文件，重启服务生效

SQL> desc v$dm_ini; 行号 NAME TYPE$dmi​ni;行号NAMETYPE NULLABLE

---

1 PARA_NAME VARCHAR(128) Y
2 PARA_VALUE VARCHAR(256) Y
3 MIN_VALUE VARCHAR(256) Y
4 MAX_VALUE VARCHAR(256) Y
5 MPP_CHK CHAR(1) Y
6 SESS_VALUE VARCHAR(256) Y
7 FILE_VALUE VARCHAR(256) Y
8 DESCRIPTION VARCHAR(256) Y
9 PARA_TYPE VARCHAR(200) Y

SQL> select para_name,para_value,para_type from v$dm_ini where para_name like ‘%BUFFER%’;
行号 PARA_NAME PARA_VALUE PARA_TYPE

---

1 HUGE_BUFFER 8 IN FILE
2 BUFFER 100 IN FILE
3 BUFFER_POOLS 3 IN FILE
4 BUFFER_FAST_RELEASE 1 SYS
5 MAX_BUFFER 100 IN FILE

sp_set_para_value(2,‘BUFFER’,200);
已用时间: 2.842(毫秒). 执行号:4.
SQL> sp_set_para_value(2,‘BUFFER’,200);
DMSQL 过程已成功完成

重启实例[[email protected] bin]# service DmServiceTEST restart
Stopping DmServiceTEST: [ OK ]
Starting DmServiceTEST: [ OK ]
[[email protected] bin]#
再次登陆查询，发现已经修改完毕SQL> select para_name,para_value,para_type from v$dm_ini where para_name like ‘%BUFFER%’;
行号 PARA_NAME PARA_VALUE PARA_TYPE

---

1 HUGE_BUFFER 8 IN FILE
2 BUFFER 200 IN FILE
3 BUFFER_POOLS 7 IN FILE
4 BUFFER_FAST_RELEASE 1 SYS
5 MAX_BUFFER 200 IN FILE
已用时间: 2.842(毫秒). 执行号:4.