近几年国产数据库越来越火，作为老牌国产数据库达梦，近期也是炙手可热，不得不抽时间研究一下。大家都说达梦数据库跟Oracle的兼容性是比较高的，从个人实际测试来看，确实还不错，提供了很多类似Oracle数据库的视图，作为Oracle dba的我，基本上可以无缝切换，直接上手。
近期打算写一个系列的达梦数据库文章，首先从内存结构开始吧。
SQL> select name,TOTAL_SIZE,TARGET_SIZE,IS_OVERFLOW,FILE_NAME from v$mem_pool;
LINEID NAME TOTAL_SIZE TARGET_SIZE IS_OVERFLOW FILE_NAME
---------- --------------------- -------------------- -------------------- ----------- -----------------------------------------------
1 SHARE POOL 524288000 0 N /data/sdb/wxy/trunk8_rel_2008_f/knl/mem2.c
2 BACKUP POOL 4194304 4194304 N /data/sdb/wxy/trunk8_rel_2008_f/knl/mem2.c
3 MON ITEM ARR 134217728 136314880 N /data/sdb/wxy/trunk8_rel_2008_f/mon/dthrd.c
4 LARGE_MEM_SQL_MONITOR 1048576 0 N /data/sdb/wxy/trunk8_rel_2008_f/mon/dsql.c
5 CYT_CACHE 327680 10485760 N /data/sdb/wxy/trunk8_rel_2008_f/crypto/cyt.c
6 XMAL SYS 65536 0 N /data/sdb/wxy/trunk8_rel_2008_f/xmal/xmal.c
7 XBOX SYS 327680 0 N /data/sdb/wxy/trunk8_rel_2008_f/xmal/xbox.c
8 DICT CACHE 52428800 104857600 N /data/sdb/wxy/trunk8_rel_2008_f/dict/ndct.c
9 INJECT HINT 65536 0 N /data/sdb/wxy/trunk8_rel_2008_f/dict/ndct.c
10 CHECK POINT 131072 10485760 N /data/sdb/wxy/trunk8_rel_2008_f/log/ckpt2.c
11 HUGE AUX 65536 16777216 N /data/sdb/wxy/trunk8_rel_2008_f/hfs/haux.c
12 SQL CACHE MANAGERMENT 209715200 629145600 N /data/sdb/wxy/trunk8_rel_2008_f/mgr/scp.c
13 MEM FOR PIPE 65536 655360 N /data/sdb/wxy/trunk8_rel_2008_f/pub/ifun_pipe.c
14 FLASHBACK SYS 393088 16777216 N /data/sdb/wxy/trunk8_rel_2008_f/trx/fback.c
15 RT_MEMOBJ_VPOOL 1048576 33554432 N /data/sdb/wxy/trunk8_rel_2008_f/job/job.c
16 DBLINK POOL 131072 16777216 N /data/sdb/wxy/trunk8_rel_2008_f/dblnk/dblnk.c
17 NSEQ CACHE 65536 655360 N /data/sdb/wxy/trunk8_rel_2008_f/npar/nseq.c
18 PARALLEL LOADER POOL 65536 33554432 N /data/sdb/wxy/trunk8_rel_2008_f/bldr_dll/bldr.c
19 POLICY GRP 65536 16777216 N /data/sdb/wxy/trunk8_rel_2008_f/dict/ndctpgrp.c
20 PURG_POOL 65536 10485760 N /data/sdb/wxy/trunk8_rel_2008_f/trx/purg2.c
21 DSQL STAT HISTORY 15728640 16777216 N /data/sdb/wxy/trunk8_rel_2008_f/mon/dsql.c
22 SESSION 6307840 33554432 N /data/sdb/wxy/trunk8_rel_2008_f/mgr/sess4.c
23 RT_HEAP 1064960 8388608 N /data/sdb/wxy/trunk8_rel_2008_f/mgr/sess4.c
24 VIRTUAL MACHINE 2162688 33554432 N /data/sdb/wxy/trunk8_rel_2008_f/op/vm.c
25 DSQL ET POOL 3145728 16777216 N /data/sdb/wxy/trunk8_rel_2008_f/mon/dsql.c
25 rows got
从达梦的内存结构来看；DM8中包含了25个内存结构；相比Oracle来讲似乎简单的太多了。这里简单描述一下相关核心内存结构的作用：
目录
1、share pool
即共享内存，从字面意思来看，类似Oracle的share pool；实际上并不是；该参数是整个共享内存的大小；通过参数memory_pool来进行控制。
SQL> select name,VALUE,DESCRIPTION from v$parameter where name='MEMORY_POOL';
LINEID NAME VALUE DESCRIPTION
---------- ----------- ----- ----------------------------
1 MEMORY_POOL 500 Memory Pool Size In Megabyte
used time: 6.684(ms). Execute id is 234.
SQL> select PARA_NAME,PARA_VALUE,DESCRIPTION ,PARA_TYPE from v$dm_ini where PARA_NAME like '%MEMORY_%';
LINEID PARA_NAME PARA_VALUE DESCRIPTION PARA_TYPE
---------- ---------------------- ---------- ---------------------------------------------------------------------------------- ---------
1 MEMORY_POOL 500 Memory Pool Size In Megabyte IN FILE
2 MEMORY_TARGET 0 Memory Share Pool Target Size In Megabyte SYS
3 MEMORY_EXTENT_SIZE 1 Memory Pool Extent Size In Megabyte IN FILE
4 MEMORY_LEAK_CHECK 0 Memory Leak Checking Flag SYS
5 MEMORY_MAGIC_CHECK 2 Memory Magic Checking Flag IN FILE
6 MEMORY_BAK_POOL 4 Memory Backup Pool Size In Megabyte IN FILE
7 HUGE_MEMORY_PERCENTAGE 50 Maximum percent of HUGE buffer that can be allocated to work as common memory pool IN FILE
7 rows got
SQL> select PARA_TYPE,count(1) from v$dm_ini group by PARA_TYPE;
LINEID PARA_TYPE COUNT(1)
---------- --------- --------------------
1 READ ONLY 78
2 SYS 161 --静态参数
3 IN FILE 178 --手动参数
4 SESSION 226 --动态参数
从参数来看，DM也有类似Oracle的内存自动管理机制，如memory_target。
参数属性分为三种:静态、动态和手动。
静态，可以被动态修改，修改后重启服务器才 能生效。
动态，可以被动态修改，修改后即时生效。动态参数又分为会话级和系统级两种。会话级参数被修改后，新参数值只会影响新创建的会话，之前创建的会话不受影响;系统级参数 的修改则会影响所有的会话。
手动，不能被动态修改，必须手动修改 dm.ini 参数文件，然后重启才能生效。
2、 buffer 缓冲
在DM数据库中，buffer 缓冲分为4种，分别为：
normal缓冲区，对应的ini参数是buffer,
keep缓冲区，常驻的数据都放在keep数据页中，对应的ini参数就是keep,
recycle缓冲区，高并发系统、使用with as语句较多，临时数据较多需要将这个recycle数据页所占用的内存值调大，对应的ini参数是recycle。
记住所有的页大小在初始化实例时已经设定好了，现在使用ini参数修改的是缓冲区内存大小。
fast缓冲区，fast包含数据页和回滚页, 常驻缓冲区，这两者都是由系统自动管理，无需用户干涉对应的ini参数是fast_pool_pages和fast_roll_pages，代表fast缓冲区的大小为多少个数据页。，
SQL> select name,PAGE_SIZE,sum(N_TOTAL_PAGES),sum(FREE) from v$bufferpool group by name,page_size;
LINEID NAME PAGE_SIZE SUM(N_TOTAL_PAGES) SUM(FREE)
---------- ------- ----------- -------------------- --------------------
1 KEEP 8192 1024 1024
2 RECYCLE 8192 128000 127998
3 FAST 8192 3000 0
4 NORMAL 8192 508992 508123
5 ROLL 8192 128 128
从上述内存结构的作用来看，keep池类似Oracle buffer cache的keep池；正常情况下的cache 使用是normal类型的缓存。 从DM8的查询结果来这里还多了一类ROLL类型。
不知道这里其中的FAST和ROLL 两类缓存的作用是什么。猜测应该是永久性内存，针对数据页和回滚页。
SQL> select name,type,value,SYS_VALUE,FILE_VALUE from v$parameter where name like '%BUFFER%';
LINEID NAME TYPE VALUE SYS_VALUE FILE_VALUE
---------- ------------------- ------- ----- --------- ----------
1 HUGE_BUFFER IN FILE 80 80 80
2 HUGE_BUFFER_POOLS IN FILE 4 4 4
3 BUFFER IN FILE 4000 4000 4000
4 BUFFER_POOLS IN FILE 11 11 11
5 BUFFER_MODE IN FILE 0 0 0
6 BUFFER_FAST_RELEASE SYS 1 1 1
7 MAX_BUFFER IN FILE 4000 4000 100
修改参数通过sp_set_para_value进行操作；2表示静态参数，1表示动态。如果参数属性不对，那么修改是不会成功的。
SQL> sp_set_para_value(1,'BUFFER',4100);
sp_set_para_value(1,'BUFFER',4100);
[-839]:Try to alter static ini parameter.
used time: 5.332(ms). Execute id is 0.
SQL> sp_set_para_value(2,'BUFFER',4100);
DMSQL executed successfully
used time: 3.119(ms). Execute id is 290.
SQL>
root@mogdb ~]# cat /opt/dm/dmdbms/data/enmotech/dm.ini| grep BUFFER
HUGE_BUFFER = 80 #Initial Huge Buffer Size In Megabytes
HUGE_BUFFER_POOLS = 4 #number of Huge buffer pools
BUFFER = 4100 #Initial System Buffer Size In Megabytes
BUFFER_POOLS = 11 #number of buffer pools
3、SQL缓冲区
在DM数据库中，SQL缓冲区类似Oracle中的shared pool，不过功能不太一样。通过cache_pool_Size参数来进行大小定义。
可以缓存执行过的SQL，SQL执行计划等，应该可以极大避免硬解析，同时还能缓存SQL执行的结果集；相当于又具备了Oracle Result cache的功能。
SQL> select * from v$dm_ini where para_NAME = 'CACHE_POOL_SIZE';
LINEID PARA_NAME PARA_VALUE MIN_VALUE MAX_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION PARA_TYPE
---------- --------------- ---------- --------- --------- ------- ---------- ---------- ---------------------------- ---------
1 CACHE_POOL_SIZE 200 1 67108864 N 200 200 SQL buffer size in megabytes IN FILE
按照DM官方的文档管理手册描述，如果要使用结果集缓存，还需要同时设置 RS_CAN_CACHE=1和USE_PLN_POOL 参数。另外还可以通过CLT_CACHE_TABLES
参数来控制，指定具体哪些表的查询结果可以被缓存。
SQL> select TYPE$,sum(ITEM_SIZE) from v$cacheitem group by type$ order by 2;
LINEID TYPE$ SUM(ITEM_SIZE)
---------- ----- --------------------
1 SQL 348841
2 PLN 5138952
另外还有相关试图可以查询sql和结果集的缓存情况，如：vcachepln、vcachers、v$cachesql
4、字典缓存
SQL> select PARA_NAME,PARA_VALUE,DESCRIPTION from v$dm_ini where PARA_NAME='DICT_BUF_SIZE';
LINEID PARA_NAME PARA_VALUE DESCRIPTION
---------- ------------- ---------- ----------------
1 DICT_BUF_SIZE 50 dict buffer size
DM数据库中通过dict_buf_size参数来进行控制。数据库对像比如表，索引，视图，序列，同义词，触发器，存储过程的信息都缓存在数据字典缓冲区中。
可以通过v$dict_cache 内存视图来查看该内存结构的使用情况：
SQL> select * from v$dict_cache;
LINEID ADDR POOL_ID TOTAL_SIZE USED_SIZE DICT_NUM
---------- ---------------- ----------- ----------- ----------- -----------
1 0x0x7f75f0cdcdc0 0 52428800 176962 81
我这里默认值是50MB，如果数据库对象比较多，这里应该需要调大一些。
5. 日志缓冲区（即log buffer）
在DM数据库中，也有类似Oracle一样的Log buffer内存结构；不过在DM数据库中看上去似乎更复杂一点点。通过查看参数
发现了有6个如下相关的参数：
45 RLOG_BUF_SIZE 1024 The Number Of Log Pages In One Log Buffer
46 RLOG_POOL_SIZE 256 Redo Log Pool Size In Megabyte
47 LOG_BUF_SIZE 1024 The Number Of Log Pages In One Log Buffer
48 LOG_POOL_SIZE 256 Redo Log Pool Size In Megabyte
49 REDO_BUF_SIZE 64 The max buffer size of rlog redo In Megabyte
50 REDOS_BUF_SIZE 1024 The max buffer size of rlog redo for standby In Megabyte
SQL> select FILE_LSN,FLUSH_LSN,CUR_LSN,NEXT_SEQ,FLUSH_PAGES,FLUSHING_PAGES,TOTAL_SPACE,FREE_SPACE from v$rlog;
LINEID FILE_LSN FLUSH_LSN CUR_LSN NEXT_SEQ FLUSH_PAGES FLUSHING_PAGES TOTAL_SPACE FREE_SPACE
---------- -------------------- -------------------- -------------------- -------------------- ----------- -------------- -------------------- --------------------
1 45107844 45107844 45107844 713825 0 0 9437171712 9437171712
used time: 1.064(ms). Execute id is 272.
SQL> select file_id,PATH,RLOG_SIZE,CREATE_TIME from v$rlogfile;
LINEID FILE_ID PATH RLOG_SIZE CREATE_TIME
---------- ----------- ------------------------------------------- ------------------------------------------------------------
1 0 /opt/dm/dmdbms/data/enmotech/enmotech01.log 3145728000 2021-05-26 02:06:28.000000
2 1 /opt/dm/dmdbms/data/enmotech/enmotech02.log 3145728000 2021-05-26 02:06:28.000000
3 2 /opt/dm/dmdbms/data/enmotech/enmotech03.log 3145728000 2021-05-26 02:21:22.000000
used time: 0.970(ms). Execute id is 273.
从上面的信息来看，通过rlog_pool_size 来定义日志缓冲区的内存大小. 另外DM也有类似Oracle的排序内存区，hash等。这里不做多余介绍。
从DM的内存结构来看，比Oralce确实要简单的太多。
附录：DM8中内存相关参数的解释
#memory pool and buffer
MAX_OS_MEMORY = 100 #Maximum Percent Of OS Memory
MEMORY_POOL = 500 #Memory Pool Size In Megabyte
MEMORY_TARGET = 0 #Memory Share Pool Target Size In Megabyte
MEMORY_EXTENT_SIZE = 1 #Memory Extent Size In Megabyte
MEMORY_LEAK_CHECK = 0 #Memory Pool Leak Checking Flag
MEMORY_MAGIC_CHECK = 2 #Memory Pool Magic Checking Flag
MEMORY_BAK_POOL = 4 #Memory Backup Pool Size In Megabyte
HUGE_MEMORY_PERCENTAGE = 50 #Maximum percent of HUGE buffer that can be allocated to work as common memory pool
HUGE_BUFFER = 80 #Initial Huge Buffer Size In Megabytes
HUGE_BUFFER_POOLS = 4 #number of Huge buffer pools
BUFFER = 4000 #Initial System Buffer Size In Megabytes
BUFFER_POOLS = 11 #number of buffer pools
FAST_POOL_PAGES = 3000 #number of pages for fast pool
FAST_ROLL_PAGES = 1000 #number of pages for fast roll pages
KEEP = 8 #system KEEP buffer size in Megabytes
RECYCLE = 1000 #system RECYCLE buffer size in Megabytes
RECYCLE_POOLS = 5 #Number of recycle buffer pools
ROLLSEG = 1 #system ROLLSEG buffer size in Megabytes
ROLLSEG_POOLS = 19 #Number of rollseg buffer pools
MULTI_PAGE_GET_NUM = 1 #Maximum number of pages for each read of buffer
PRELOAD_SCAN_NUM = 4 #The number of pages scanned continuously to start preload task
PRELOAD_EXTENT_NUM = 5 #The number of clusters preloaded for the first time
SORT_BUF_SIZE = 10 #maximum sort buffer size in Megabytes
SORT_BLK_SIZE = 1 #maximum sort blk size in Megabytes
SORT_BUF_GLOBAL_SIZE = 500 #maximum global sort buffer size in Megabytes
SORT_FLAG = 0 #choose method of sort
HAGR_HASH_SIZE = 100000 #hash table size for hagr
HJ_BUF_GLOBAL_SIZE = 500 #maximum hash buffer size for all hash join in Megabytes
HJ_BUF_SIZE = 50 #maximum hash buffer size for single hash join in Megabytes
HJ_BLK_SIZE = 1 #hash buffer size allocated each time for hash join in Megabytes
HAGR_BUF_GLOBAL_SIZE = 500 #maximum buffer size for all hagr in Megabytes
HAGR_BUF_SIZE = 50 #maximum buffer size for single hagr in Megabytes
HAGR_BLK_SIZE = 1 #buffer size allocated each time for hagr in Megabytes
MTAB_MEM_SIZE = 8 #memory table size in Kilobytes
FTAB_MEM_SIZE = 0 #file table package size in Kilobytes
MMT_GLOBAL_SIZE = 4000 #memory map table global size in megabytes
MMT_SIZE = 0 #memory map table size in megabytes
MMT_FLAG = 1 #ways of storing bdta data in memory map table
DICT_BUF_SIZE = 50 #dictionary buffer size in Megabytes
HFS_CACHE_SIZE = 160 #hfs cache size in Megabytes, used in huge horizon table for insert, update,delete
VM_STACK_SIZE = 256 #VM stack size in Kilobytes
VM_POOL_SIZE = 64 #VM pool size in Kilobytes
VM_POOL_TARGET = 32768 #VM pool target size in Kilobytes
SESS_POOL_SIZE = 16 #session pool size in Kilobytes
SESS_POOL_TARGET = 32768 #session pool target size in Kilobytes
RT_HEAP_TARGET = 8192 #runtime heap target size in Kilobytes
VM_MEM_HEAP = 0 #Whether to allocate memory to VM from HEAP
RFIL_RECV_BUF_SIZE = 16 #redo file recover buffer size in Megabytes
N_MEM_POOLS = 1 #number of memory pools
COLDATA_POOL_SIZE = 0 #coldata pool size for each worker group
HAGR_DISTINCT_HASH_TABLE_SIZE = 10000 #Size of hagr distinct hash table
CNNTB_HASH_TABLE_SIZE = 100 #Size of hash table in connect-by operation
GLOBAL_RTREE_BUF_SIZE = 100 #The total size of buffer for rtree
SINGLE_RTREE_BUF_SIZE = 10 #The size of buffer for single rtree
SORT_OPT_SIZE = 0 #once max memory size of radix sort assist count array
DFS_BUF_FLUSH_OPT = 0 #Whether to flush buffer page in opt mode for DFS storage
最后修改时间：2021-08-26 11:56:58
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
