达梦DM8SQL优化相关知识点整理（执行计划、统计信息、内存参数修改）
1、执行计划
执行计划就是一条 SQL 语句在数据库中的执行过程或访问路径的描述。 执行计划顺序： 各计划节点的执行顺序为：缩进越多的越先执行，同样缩进的上面的先执行，下面的后执行，上下的优先级高于内外。 缩进最深的，最先执行；缩进深度相同的，先上后下。 口诀：最右最上先执行
#CSCN2: [1, 2, 12]; INDEX33555496(TEST)
操作符，[代价，行数，字节数] 描述
查看执行计划几种方法：
（1）达梦管理工具
（2）达梦ET工具
ET 工具是 DM 数据库自带的 SQL 性能分析工具，能够统计 SQL 语句执行过程中每个操作符的实际开销，为 SQL 优化提供依据以及指导。 ET 功能的开启将对数据库整体性能造成一定影响，优化工作结束后尽量关闭该功能以提升数据库整体运行效率。 修改INI参数开启ET： 老版本： ENABLE_MONITOR = 3 新版本： ENABLE_MONITOR = 1（默认打开） MONITOR_TIME = 1 （默认打开） MONITOR_SQL_EXEC = 1（设置成1）
或者动态修改： ----两个参数均为动态参数，可直接调用系统函数进行修改
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1);
----关闭 ET
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',0);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',0);
执行 SQL 语句后，客户端会返回 SQL 语句的执行号。点击执行号 如果没有图形界面，调用存储过程可返回相同结果 命令调用：CALL ET(55);
ET结果说明：
OP: 操作符TIME(us): 时间开销，单位为微秒PERCENT: 执行时间占总时间百分比RANK: 执行时间耗时排序SEQ: 执行计划节点号N_ENTER: 进入次数
（3）EXPLAIN
set autotrace on–达梦执行计划过于简单 set autotrace traace --语句也会被执行
区别：ET和EXPLAIN
et 语句已经执行， 才能看到执行号，才能调用 et。 explain 语句并没有真正执行， 利用 CBO（优化器） 进行判断出的一个执行计划（执行计划不一定准确）.
常见操作符：
BLKUP2 定位查找 CSCN2 聚簇索引扫描（全表扫描） HASH2 INNER JOIN hash 内连接 NEST LOOP FULL JOIN2 嵌套连接 MERGE INNER JOIN3 归并连接 NSET2 结果集 PRJT2 投影 SSCN 二级索引扫描 SSEK2 二级索引数据定位 CSEK2 聚簇索引扫描
/*+ */ hint 强制执行某种规则
常见格式： SELECT /+ HINT1 [HINT2]/ 列名 FROM 表名 WHERE_CLAUSE ; UPDATE 表名 /+ HINT1 [HINT2]/ SET 列名 =变量 WHERE_CLAUSE ; DELETE FROM 表名 /+ HINT1 [HINT2]/ WHERE_CLAUSE ; 需要注意的是：如果HINT的语法没有写对或指定的值不正确，DM并不会报错，而是直接忽略HINT继续执行。 嵌套 nest loop 哈希 hash join 归并排序 meger sort join（有排序，性能 不好，表是索引，索引是表） 嵌套和哈希有驱动表和被驱动表，一般情况下使用小表作为驱动表。
构造环境：
CREATE TABLE T1(Q1 INT ,Q2 CHAR(1),Q3 VARCHAR(10) ,Q4 VARCHAR(10) );
CREATE TABLE T2(Q1 INT ,Q2 CHAR(1),Q3 VARCHAR(10) ,Q4 VARCHAR(10) );
INSERT INTO T1
SELECT LEVEL Q1,CHR(65+MOD(LEVEL,57)) Q2,'TEST',NULL FROM DUAL CONNECT BY LEVEL<=10000;
INSERT INTO T2
SELECT LEVEL Q1,CHR(65+MOD(LEVEL,57)) Q2,'TEST',NULL FROM DUAL CONNECT BY LEVEL<=100;
explain select /*+use_nl(t1,t2)*/ * from t1 join t2 on t1.q1=t2.q1 where t1.q2='A';
explain select /*+use_hash(t1,t2)*/ t1.q1,t2.q1 from t1 inner join t2 on t1.q1=t2.q1 where t2.q2='b';
2、统计信息
第一次使用这DBMS_STATS包需要初始化一下。
sp_create_system_packages(1,'DBMS_STATS')
相关更多统计信息语法参考官方文档
收集统计信息：
– 对于表，只搜集表的总行数、总的页数、已经使用的页数等基本信息
DBMS_STATS.GATHER_TABLE_STATS('模式名','表名','分区表名',100,TRUE,'FOR ALL COLUMNS SIZE AUTO'...);
–索引
dbms_stats.gather_index_stats('模式名','索引名','分区索引名',...)
–模式
dbms_stats.gather_schema_stats('模式名','收集百分比',...)
–收集指定用户下所有表所有列的统计信息：
DBMS_STATS.GATHER_SCHEMA_STATS('username',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
–收集指定用户下所有索引的统计信息：
DBMS_STATS.GATHER_SCHEMA_STATS('usename',1.0,TRUE,'FOR ALL INDEXED SIZE AUTO');
查看统计信息：
–用于经过 GATHER_TABLE_STATS、GATHER_INDEX_STATS 或 GATHER_SCHEMA_STATS 收集之后展示。
dbms_stats.table_stats_show('模式名','表名');
– 用于经过 GATHER_TABLE_STATS、GATHER_INDEX_STATS 或 GATHER_SCHEMA_STATS 收集之后展示。 返回两个结果集：一个是索引的统计信息；另一个是直方图的统计信息。
dbms_stats.index_stats_show('模式名','索引名');
–用于经过 GATHER_TABLE_STATS、GATHER_INDEX_STATS 或 GATHER_SCHEMA_STATS 收集之后展示。
dbms_stats.COLUMN_STATS_SHOW('模式名','表名','列名')
更新统计信息：
–更新已有统计信息
DBMS_STATS.UPDATE_ALL_STATS（）
删除统计信息：
–表
DBMS_STATS.DELETE_TABLE_STATS('模式名','表名','分区名',...)
–模式
DBMS_STATS.DELETE_SCHMA_STATS('模式名','','',...)
–索引
DBMS_STATS.DELETE_INDEX_STATS('模式名','索引名','分区表名',...)
–字段
DBMS_STATS.DELETE_COLUMN_STATS('模式名','表名','列名','分区表名',...)
需注意 以下对象不支持统计信息：
外部表、 DBLINK 远程表、动态视图表、记录类型数组所用的临时表。所在表空间为 OFFLINE 的对象。位图索引，位图连接索引、虚索引、 全文索引、空间索引、数组索引、 无效的索引。BLOB、 IMAGE、 LONGVARBINARY、 CLOB、 TEXT、 LONGVARCHAR、自定义类型 列和空间类型列等列类型。
3、内存参数修改方法
第一种：
1 、
SF_GET_PARA_VALUE (scope int, paraname varchar(256))
配置参数的值类型为数值类型时使用该函数来获取当前值。SCOPE 参数为1表示获取INI文件中配置参数的值，为2表示获取内存中配置参数的值。
2 、
SF_GET_PARA_DOUBLE_VALUE(scope int, paraname varchar(8187))
配置参数的值类型为浮点型时使用该函数来获取当前值。SCOPE参数为1表示获取INI文件中配置参数的值，为2表示获取内存中配置参数的值。
3 、
SF_GET_PARA_STRING_VALUE(scope int, paraname varchar(8187))
配置参数的值为字符串类型时用该系统函数来获取当前值。SCOPE 参数为1表示获取INI 文件中配置参数的值，为2表示获取内存中配置参数的值。
4 、
SP_SET_PARA_VALUE (scope int, paraname varchar(256), value int64)
该过程用于修改整型静态配置参数和动态配置参数。SCOPE参数为1表示在内存和INI文件中都修改参数值，此时只能修改动态的配置参数。参数为2表示只在 INI 文件中修改配置参数，此时可用来修改静态配置参数和动态配置参数。当SCOPE等于1，试图修改静态配置参数时服务器会返回错误信息。
5 、
SP_SET_PARA_DOUBLE_VALUE(scope int,paraname varchar(8187), alue double)
该过程用于修改浮点型静态配置参数和动态配置参数。SCOPE 参数为 1表示在内存和INI文件中都修改参数值，此时只能修改动态的配置参数。参数为2表示只在INI文件中修改配置参数，此时可用来修改静态配置参数和动态配置参数。当SCOPE等于1，试图修改静态配置参数时服务器会返回错误信息。
6 、
SF_SET_SYSTEM_PARA_VALUE(paraname varchar(256),value int64\double\varchar（256）,deferred int,scope int64)
该过程用于修改系统整型、 double、 varchar 的静态配置参数或动态配置参数。DEFERRED 参数，为0表示当前session修改的参数立即生效，为1表示当前 session不生效，后续再生效，默认为0。 SCOPE 参数为1表示在内存和INI文件中都修改参数值，此时只能修改动态的配置参数。参数为2表示只在INI文件中修改配置参数，此时可用来修改静态配置参数和动态配置参数。
7 、
SF_SET_SESSION_PARA_VALUE (paraname varchar(8187), value bigint)
设置某个会话级 INI 参数的值，设置的参数值只对本会话有效。
8 、
SP_RESET_SESSION_PARA_VALUE (paraname varchar(8187))
重置某个会话级 INI 参数的值，使得这个 INI 参数的值和系统 INI 参数的值保持一致。
9 、
SF_GET_SESSION_PARA_VALUE (paraname varchar(8187))
获得当前会话的某个会话级 INI 参数的值。
第二种：
修改系统参数，注意与Oracle修改方法的区别，没有了“SCOPE=”。 ALTER SYSTEM SET ‘<参数名称>’ =<参数值> [DEFERRED] [MEMORY|BOTH|SPFILE]; 例子：ALTER SYSTEM SET ‘MTAB_MEM_SIZE’ =1200 spfile;–静态参数修改 ALTER SESSION SET ‘<参数名称>’ =<参数值> [PURGE];–PURGE关键字指是否清理执行计划 例子：ALTER SESSION SET ‘HAGR_HASH_SIZE’ =2000000;–修改当前会话参数
第三种：修改INI文件，重启生效。
参数分为：静态、动态、手动 v$paprameter中type类型：
type参数sys ,session动态参数in file静态参数read only手动参数
参数描述静态可以被动态修改，需重启服务器生效动态可以被动态修改，修改后即时生效；动态分为会话级和系统级； 会话级：新参数值只影响新创建的会话，之前的会话不受影响； 系统级：修改后会影响所有会话手动不能动态修改，只能修改dm.ini然后重启
静态：可以被动态修改，需重启服务器生效
动态：可以被动态修改，修改后即时生效； 会话级：新参数值只影响新创建的会话，之前的会话不受影响 系统级：修改后会影响所有会话
手动：不能动态修改，只能修改dm.ini然后重启
可以通过v$dm_ini或v$parameter查询参数值。
更多参考达梦技术社区：https://eco.dameng.com
好文阅读
您阅读本篇文章共花了：
发表评论