达梦SQL优化初体验

某项目从ORACLE数据库迁移到达梦，正在做上线前压力测试。测试结果达不到验收标准。让我进行调优。由于疫情居家办公，只能通过微信发出如下命令让现场人员执行：

with SQL_HISTORY
as
(
select a.*
from v$sql_history a,v$session_history b
where a.START_TIME > dateadd(hh,-1,sysdate)
and a.sess_id=b.sess_id and a.sess_seq=b.sess_seq
and b.user_name='业务用户' 
)
select sql_id,top_sql_text,sec_to_time(round(to_number(sum(time_used))/1000000,3)) sql_time_used,
count(*) sql_execs,sec_to_time(round(to_number(sum(time_used))/count(*)/1000000,5)) sql_time_per_exec,
round(to_number(sum(time_used)) * 100/(select sum(time_used) from SQL_HISTORY ),2) ratio
from SQL_HISTORY
group by sql_id,top_sql_text
order by 6 desc limit 20
；

上面命令可以统计出最近一个小时之内压力测试的TOP20语句，每条SQL的累计执行时间（注：每条语句都执行了多轮儿）、执行次数、单次执行时间，及该SQL累计时间在总时间中的百分占比并倒排序。

如果该业务用户还有其他会话做别的事情需要排除掉，那么“b.user_name='业务用户' ”后面可以增加“and b.clnt_ip=”或者“and b.clnt_type=”或者“and b.clnt_host=”或者“and b.appname=”等条件做出精确筛选。

上面的查询语句中涉及了许多转换函数，是因为达梦数据库不能像ORACLE那样处理隐式转换。比如ORACLE中1/2等于 0.5，而在达梦中等于0！

下面通过一个小技巧改写上面语句

with SQL_HISTORY
as
(
select a.*
from v$sql_history a,v$session_history b
where a.START_TIME > sysdate - 1.0/24
and a.sess_id=b.sess_id and a.sess_seq=b.sess_seq
and b.user_name='业务用户' 
)
select sql_id,top_sql_text,sec_to_time(round((sum(time_used)+0.0)/1000000,3)) sql_time_used,
count(*) sql_execs,sec_to_time(round((sum(time_used)+0.0)/count(*)/1000000,5)) sql_time_per_exec,
round((sum(time_used)+0.0) * 100/(select sum(time_used) from SQL_HISTORY ),2) ratio
from SQL_HISTORY
group by sql_id,top_sql_text
order by 6 desc limit 20
；

执行完上述统计语句后发现TOP1 SQL执行时间在总时间中的占比竟然超过了80%！也就是说优化好这条语句就可以整体提升性能一大截。抓住主要矛盾。优化其他19条语句意义不是很大。

下一步就是查看执行计划了。

先查询该SQL的执行计划地址：

select cache_item from v$cachepln  where sqlstr like '%xxxxxxxx%'；

上面命令中xxxxxx根据查出来的top语句替换，节选其中一部分即可，只要能唯一定位这条sql。

执行以下命令生成运行中的真实执行计划：

alter session set events 'immediate trace name plndump level yyyyyyy,dump_file ''/u01/dmbak/1.log''';

命令中yyyyyy用执行计划地址替换

cat /u01/dmbak/1.log

忽略sql语句内容，下面是执行计划

建议尝试不同的hint，以提高sql查询性能。

开发人员经过优化，这条最消耗CPU资源的SQL单次执行时间缩短了一倍以上。