达梦SQL执行耗时异常问题排查

在系统运行的过程中经常遇到通过客户端在数据库执行SQL语句很快，但是在程序中执行耗时长的问题，针对该类问题我们一般从如下两方面分析：
本章内容已在如下环境上测试数据库：
达梦数据库DM8；

1 网络情况排查

需要确认网络上是否存在不稳定的因素，因为程序远程访问数据库后，会获取结果，如果网络不稳定，获取结果耗时可能就会出现不稳定的问题，导致程序获取结果集耗时长。此时可以通过ping命令看下网络是否存在丢包问题或者返回时间过长。可以通过在不同机器上的客户端执行相同的语句来进行判断，存在网络问题的服务器上的耗时长，其他机器上耗时短，一般就可判定是网络问题导致。

2 计划缓存问题

2.1 查看是否开启计划缓存

这个是我们今天关注的重点，数据库默认开启了计划缓存，可以通过如下语句查看是否开启计划缓存：
select PARA_NAME,PARA_VALUE
from v$dm_ini
where para_name like ‘USE_PLN_POOL’;

该参数设置的为非0，即开启了计划缓存。
参数值解释：
0：禁止执行计划的重用；  
1：启用执行计划的重用功能 ；
2：对不包含显式参数的语句进行常量参数化优化；
3：即使包含显式参数的语句也进行常量参数化优化
开启计划缓存功能后，在SQL初次执行后会将对应执行计划存储到缓冲区，下次执行相同的SQL或者常量值不同的SQL会优先从计划缓存池中查找是否存在已缓存计划，这样可以减少语句分析优化过程，提高执行效率。当计划缓存池满了，根据LRU算法进行计划的淘汰。通过如下语句可以查询计划缓存的情况:

2.2 构造数据

create table test(c1 int, c2 varchar(100), c3 int, primary key(c1));
begin
for i in 1…100000 loop
insert into test values(i, ‘aaaaaaa’, i);
end loop;
commit;
end;
/

2.3 执行查询语句

select * from test where c3=3;

2.4 查看执行计划缓存表

select cache_item,sqlstr
from v$cachepln
where sqlstr like ‘%select * from test where c3=3%’;

–或者通过以下语句获取sql的计划缓存
select cache_item,sqlstr
from v$cachepln
where sqlstr like ‘%SQL语句%’;

2.5 根据cache_item生成日志文件

查询到对应的语句的计划缓存信息后，通过如下命令生成语句计划到本地文件：
mkdir /home/dmdba/dbdata/

alter session set events ‘immediate trace name plndump level 查出的cache_item,dump_file ‘’/home/dmdba/dbdata/plan0117.log ‘’’;

alter session set events ‘immediate trace name plndump level 140460258129960,dump_file ‘’/home/dmdba/dbdata/plan0117.log’‘’;

注：其中的文件路径需要单引号引起，由于路径是在外层单引号内，所以使用单引号做了转义，两个单引号代表一个单引号。
根据获取到的计划，我们可以确认计划是否准确，如果计划缓存中的计划存在问题，那后续的SQL在缓存中找到对应的计划后依然使用执行效率不高的计划。如果相关计划一直没有被淘汰，就有可能出现程序执行耗时很长的问题。
那为什么在达梦管理工具中执行时，速度就很快呢？这是因为SQL语句在执行后，在进行计划缓存匹配时，是严格按照执行SQL的字符进行匹配的。数据库会对执行的SQL生成对应的哈希值，SQL语句多一个空格，对于优化器来说都是不同的SQL，这就出现了上述描述的问题。

2.6 查看后成的日志文件

cat /home/dmdba/dbdata/plan0117.log

如果仅查看执行计划可以用explain sql语句查看，或者使用manager管理工具里的查看执行计划按钮查看

2.7 根据缓存表里的CACHE_ITEM列手动淘汰该计划

当遇到该种问题时，我们可以尝试清理计划缓存来丢弃执行效率不高的计划，然后让优化器根据最新的统计信息生成新的执行计划，清理的方法如下：
–清理计划缓存
call SP_CLEAR_PLAN_CACHE(cache_item);
call SP_CLEAR_PLAN_CACHE(140460258129960);

清理完毕后，可以通过程序确认对应的操作执行效率是否正常。

3 小结

数据库为什么在计划缓存中生成了非最优的计划呢？达梦数据库的优化器是基于代价的优化器，精确的统计信息有助于CBO生成更优的计划，但是统计信息并不会实时更新。所以，统计信息的不准确，就导致CBO生成了非最优的计划。统计信息只有在我们手动更新的时候才会生成，所以当我们发现一些业务表的相关语句存在因为数据的变化而导致语句执行效率差异变大时，我们可以通过设定定时作业对表进行统计信息的收集。
在DM8的新版本中，已经支持了DBMS_STATS.SET_TABLE_PREFS方法，通过开启INI参数AUTO_STAT_OBJ来监控用户表的增删改行数，利用DBMS_STATS系统包中SET_TABLE_PREFS方法对表进行设定，然后利用事件触发器完成自动更新表统计信息的功能。这部分在后面找机会向大家介绍。

达梦社区地址：https://eco.dameng.com