【达梦数据库】-SQL调优思路

【达梦数据库】-SQL执行速度慢的调优思路

在处理客户问题的过程中，总会遇到客户跑过来问：这个SQL以前执行都很快，现在怎么执行这么慢？是不是你们数据库坏了，性能不稳定之类的话？作为一名运维人员，我们就会产生疑问，到底从哪里入手能快速定位问题，帮助客户打消这些疑虑？通过实践，我们建议从软硬件的角度来排查定位问题。

1、软件

1.1、确认当前慢SQL

这里当然涉及到开启达梦数据库sqllog(link)来收集慢SQL了，并使用 SQL日志分析工具(DMLOG)对达梦 SQL 日志进行统计分析，这里我们不再赘述，以下面的慢SQL为例：

--慢sql：
select * from test;

1.2、查看对象统计信息

对象统计信息描述了对象数据的分布特征。统计信息是优化器的代价计算的依据，可以帮助优化器较精确地估算成本，对执行计划的选择起着至关重要的作用。

--查看统计信息是否准确
select table_name,num_rows,blocks,last_analyzed from user_tables where table_name='表名';  

#通常情况下，生产环境每周六1点开始收集全库信息，以环境配置为准。

我们优先查看对象统计信息，如果不准确，能快速的帮助我们掌握全局情况。

1.3、查看执行计划

使用EXPLAIN语句可以查看sql的执行计划。

EXPLAIN select * from test;

但EXPLAIN只生成执行计划，并不会真正执行SQL语句，因此产生的执行计划有可能不准。我们需要获取真实的执行计划，参考: https://blog.csdn.net/qq_35273918/article/details/127208211，主要有以下方法：

1.3.1 通过AUTOTRACE功能获取执行计划

disql下执行set autotrace trace开启AUTOTRACE功能，执行SQL语句，并打印实际的执行计划。

SQL> set autotrace trace  --关闭命令：set autotrace off
SQL> select * from test;

行号     id          name age
---------- ----------- ---- -----------
1          1           Tom  23


1   #NSET2: [1, 1, 64]
2     #PRJT2: [1, 1, 64]; exp_num(4), is_atom(FALSE)
3       #CSCN2: [1, 1, 64]; INDEX33555464(TEST)

已用时间: 16.189(毫秒). 执行号:501.

优点：操作简单，方便
缺点：需要等待SQL执行完才能打印执行计划，对于超级慢的SQL不友好

1.3.2 通过v$cachepln视图获取执行计划

SQL> select cache_item, sqlstr from v$cachepln where sqlstr like 'select * from test;'; 

行号     cache_item           sqlstr
---------- -------------------- -------------------
1          140028454238272      select * from test;

已用时间: 1.580(毫秒). 执行号:513.

使用cache_item生成执行计划文件

SQL> alter session set events 'immediate trace name plndump level 140028454238272, dump_file ''/dmdata/sqlplntest.log''';

140028454238272就是获取到的cache_item
dump_file表示将获取到的结果打印到/dmdata/sqlplntest.log中,方便记录

sqlplntest.log详情如下：

PLN_CMD:
        0       savepoint
        6       dop_try_begin   0
        10      dop_try_begin   0
        14      sql 0 0
        24      nop
        26      jmp     67
        32      nop
        34      push    0
        40      swap
        42      sloc    1
        46      err_set 0
        50      rollback
        56      jmp     67
        62      nop
        64      throw dir       1
        67      exception end
        69      savepoint
        75      cop 'b'
        79      hlt 0

sqlnode[0]::::
1   #NSET2: [1, 1->1, 64]
2     #PRJT2: [1, 1->1, 64]; exp_num(4), is_atom(FALSE)
3       #CSCN2: [1, 1->1, 64]; INDEX33555464(TEST)

end dump the infos of pln[140028454238272].

优点：快速，方便
缺点：步骤多，遇到长SQL拼接不方便

1.4、通过ET配合查看执行计划

ET是达梦自带的系统存储过程，能统计SQL所有操作符的执行时间，从而定位到有性能问题的操作
我们从上面的执行计划中知道了该SQL的执行过程，以及每个操作符和计划节点的代价，不过这个代价看上去还是有些抽象，ET可以把这些代价转化为具体的时间，帮助我们更好的有针对性的做出优化。

1.4.1、 配置ET

以下操作在同一窗口执行

SQL> SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);  --用于打开或者关闭系统的监控功能。1：打开；0：关闭
SQL> SP_SET_PARA_VALUE(1,'MONITOR_TIME',1);   --最新版本没有这个参数
SQL> SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);   --操作符、虚拟机栈帧、执行计划节点的监控开关，会话级别。

--确认参数：
SQL> select name, type, value from v$parameter t where name in('ENABLE_MONITOR','MONITOR_SQL_EXEC','MONITOR_TIME');

行号     name             TYPE    VALUE
---------- ---------------- ------- -----
1          ENABLE_MONITOR   SYS     1
2          MONITOR_TIME     SYS     1
3          MONITOR_SQL_EXEC SESSION 1

已用时间: 3.280(毫秒). 执行号:514.

1.4.2、 确定会话ID

SQL> select * from test;

行号     id          name age
---------- ----------- ---- -----------
1          1           Tom  23

已用时间: 0.208(毫秒). 执行号:55011.

会话ID:55011

2.3 执行ET

SQL> ET(55011);

--结果：
行号     OP    TIME(US)             PERCENT RANK                 SEQ         N_ENTER
---------- ----- -------------------- ------- -------------------- ----------- -----------
1          PRJT2 2                    0.10%   4                    2           4
2          DLCK  49                   2.45%   3                    0           2
3          NSET2 111                  5.56%   2                    1           3
4          CSCN2 1835                 91.89%  1                    3           2

OP: 操作符
TIME(us): 时间开销，单位为微秒
PERCENT: 执行时间占总时间百分比
RANK: 执行时间耗时排序
SEQ: 执行计划节点号
N_ENTER: 进入次数

1.5、执行计划解读

各计划节点的执行顺序为：缩进越多的越先执行，同样缩进的上面的先执行，下面的后执行，上下的优先级高于内外，参考： 执行计划解读。

2、硬件

SQL执行的慢，还有一个原因，就是系统已经到瓶颈，系统状态，磁盘io读写、内存使用率、cpu、网络延时等因素都需要考量。

使用top命令查看系统状态，主要关注us,sy,id,wa等参数：

• us和sy是否比较高，id是否有空闲
• wa是否比较高，存在可能和磁盘IO有关系
  

在生产环境下，我们尽量不要使用dd或者fio命令测试，避免短时间的业务中断，可以使用v$sql_stat查看：

select SESSID,SESS_SEQ,SQL_TXT,SQL_ID,EXEC_TIME,IO_WAIT_TIME FROM v$sql_stat;

下图明显看到IO_WAIT_TIME占了整个SQL执行时间EXEC_TIME的99%，磁盘IO问题已经成为了SQL查询慢的主要原因

4、参考链接

【执行计划结合ET】参考链接: https://blog.csdn.net/yangeoooo/article/details/118575215