DMSQL优化基础

SQL性能分析工具ET

ET是DM自带的分析工具，能统计SQL每个操作符的时间花费，从而定位到有性能问题的操作，指导用户去优化。

老版本INI参数ENABLE_MONITOR=3时，ET才能使用。

新版本ENABLE_MONITOR=1时可以动态打开

SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1);

SP_SET_PARA_VALUE(1,'MONITOR_TIME',1);

实验如下：

SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1); create table t1 as select level c1,level||'abc' c2 from dual connect by level<=10000; select * from t1 where c2 like '5000%'; SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1); Call ET(1034);

给出了如下建议

 

优化建议：

c2字段创建索引，更新统计信息，消除全表扫描。

CREATEINDEXIDX_C2_T1ONT1(C2);

SP_COL_STAT_INIT_EX('SYSDBA','T1','C2',100)

 

 

优化后的执行计划走索引耗时明显缩短

DM 执行计划解读

SQL 方式查看执行计划

在待查看执行计划的 SQL 语句前加 explain 执行 SQL 语句即可查看执行计划

 

执行计划的每行即为一个计划节点，主要包含三部分信息。

第一部分 NEST2、PRJT2、CSCN2 为操作符及数据库具体执行了什么操作。

第二部分的三元组为该计划节点的执行代价，具体含义为[代价，记录行数，字节数]。

第三部分为操作符的补充信息。

例如：第三个计划节点：操作符是 CSCN2 即全表扫描，代价估算是 1 ms，扫描的记录行数是 830 行，输出字节数是 396 个

常见操作符解读

使用以下实验来解读各操作符

DROP TABLE T1;  DROP TABLE T2;  CREATE TABLE T1(C1 INT ,C2 CHAR(1),C3 VARCHAR(10) ,C4 VARCHAR(10) );  CREATE TABLE T2(C1 INT ,C2 CHAR(1),C3 VARCHAR(10) ,C4 VARCHAR(10) );  INSERT INTO T1 SELECT LEVEL C1,CHR(65+MOD(LEVEL,57)) C2,'TEST',NULL FROM DUAL CONNECT BY LEVEL<=10000;  INSERT INTO T2 SELECT LEVEL C1,CHR(65+MOD(LEVEL,57)) C2,'TEST',NULL  FROM DUAL CONNECT BY LEVEL<=10000;  CREATE INDEX IDX_C1_T1 ON T1(C1);  SP_INDEX_STAT_INIT(USER,'IDX_C1_T1');

NSET：结果集收集

 

用于结果集收集的操作符，一般是查询计划的顶层节点，优化工作中无需对该操作符过多关注，一般没有优化空间。

PRJT：投影

关系的【投影】 (project) 运算，用于选择表达式项的计算。广泛用于查询，排序，函数索引创建等。优化工作中无需对该操作符过多关注，一般没有优化空间。

SLCT：选择

 

关系的【选择】运算，用于查询条件的过滤。可比较返回结果集与代价估算中是否接近，如相差较大可考虑收集统计信息。若该过滤条件过滤性较好，可考虑在条件列增加索引。

AAGR：简单聚集

 

用于没有 GROUP BY 的 COUNT、SUM、AGE、MAX、MIN 等聚集函数的计算。

FAGR：快速聚集

 

用于没有过滤条件时从表或索引快速获取 MAX、MIN、COUNT 值。

HAGR：HASH 分组聚集

 

用于分组列没有索引只能走全表扫描的分组聚集，该示例中 C2 列没有创建索引。

SAGR：流分组聚集

 

用于分组列是有序的情况下，可以使用流分组聚集，C1 列上已经创建了索引，SAGR2 性能优于 HAGR2。

BLKUP：二次扫描 (回表)

 

先使用二级索引索引定位 rowid，再根据表的主键、聚集索引、rowid 等信息获取数据行中其它列。

CSCN：全表扫描

 

CSCN2 是 CLUSTER INDEX SCAN 的缩写即通过聚集索引扫描全表，全表扫描是最简单的查询，如果没有选择谓词，或者没有索引可以利用，则系统一般只能做全表扫描。全表扫描 I/O 开销较大，在一个高并发的系统中应尽量避免全表扫描。

SSEK、CSEK、SSCN：索引扫描

-- 创建所需索引

CREATE CLUSTER INDEX IDX_C1_T2  ON T2(C1);

CREATE  INDEX IDX_C1_C2_T1  ON T1(C1,C2);

EXPLAIN SELECT * FROM T1 WHERE C1=10;

 

SSEK2 是二级索引扫描即先扫描索引，再通过主键、聚集索引、rowid 等信息去扫描表。

 

CSEK2 是聚集索引扫描只需要扫描索引，不需要扫描表，即无需 BLKUP 操作，如果 BLKUP 开销较大时，可考虑创建聚集索引。

SSCN 是索引全扫描，不需要扫描表。

NEST LOOP：嵌套循环连接

 

嵌套循环连接最基础的连接方式，将一张表（驱动表）的每一个值与另一张表（被驱动表）的所有值拼接，形成一个大结果集，再从大结果集中过滤出满足条件的行。驱动表的行数就是循环的次数，将在很大程度上影响执行效率。

适用场景：

• 驱动表有很好的过滤条件
• 表连接条件能使用索引
• 结果集比较小

HASH JOIN：哈希连接

 

哈希连接是在没有索引或索引无法使用情况下大多数连接的处理方式。哈希连接使用关联列去重后结果集较小的表做成 HASH 表，另一张表的连接列在 HASH 后向 HASH 表进行匹配，这种情况下匹配速度极快，主要开销在于对连接表的全表扫描以及 HASH 运算。

MERGE JOIN：归并排序连接

-- 对连接列创建索引
CREATE INDEX IDX_T1_C1C2 ON T1(C1,C2);

 

 

归并排序连接需要两张表的连接列都有索引，对两张表扫描索引后按照索引顺序进行归并。

使用 DMDBG调试存储过程

确认启用启用DEBUG SYSTEM系统包

 

创建存储过程TEST_DMBUG CREATE OR REPLACE PROCEDURE "SYSDBA"."TEST_DMBUG" AUTHID DEFINER as aa int; begin  for i in  1..3 LOOP  aa=1/(3-i);  print aa;  end loop; end;

图形化工具的调试这里不做陈述。利用 DM 提供的命令行调试工具 dmdbg也可以进行存储过程调试

 

根据help提示信息选取调试的操作和步骤，b 0 设置断点从头开始， r 开始执行调试，s 进入循环单步执行 直到报除0错误。下图为具体调试示例：

 

统计信息收集

收集统计信息的方法如下所示

--收集指定用户下所有表所有列的统计信息： 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'); --或 收集单个索引统计信息 DBMS_STATS.GATHER_INDEX_STATS('username','IDX_T2_X'); --收集指定用户下某表统计信息 DBMS_STATS.GATHER_TABLE_STATS('username','table_name',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO'); --收集某表某列的统计信息：  STAT 100 ON table_name(column_name);

自动收集统计信息

DM 数据库支持统计信息的自动收集，当全表数据量变化超过设定阈值后可自动更新统计信息

--打开表数据量监控开关，参数值为 1 时监控所有表，2 时仅监控配置表SP_SET_PARA_VALUE(1,'AUTO_STAT_OBJ',2); --设置 SYSDBA.T 表数据变化率超过 15% 时触发自动更新统计信息DBMS_STATS.SET_TABLE_PREFS('SYSDBA','T','STALE_PERCENT',15); --配置自动收集统计信息触发时机 SP_CREATE_AUTO_STAT_TRIGGER(1, 1, 1, 1,'14:36', '2020/3/31',60,1);

--函数各参数介绍

SP_CREATE_AUTO_STAT_TRIGGER(

TYPE                    INT,    --间隔类型，默认为天   

FREQ_INTERVAL          INT,    --间隔频率，默认 1   

FREQ_SUB_INTERVAL      INT,    --间隔频率，与 FREQ_INTERVAL 配合使用    FREQ_MINUTE_INTERVAL  INT,    --间隔分钟，默认为 1440   

STARTTIME              VARCHAR(128), --开始时间，默认为 22:00    DURING_START_DATE     VARCHAR(128), --重复执行的起始时间，默认 1900/1/1    MAX_RUN_DURATION     INT,    --允许的最长执行时间(秒)，默认不限制   

ENABLE                  INT     --0 关闭，1 启用  --默认为 1);

更多资讯请上达梦技术社区了解： https://eco.dameng.com