优化 | 达梦技术文档

在应用中发现执行较慢的 SQL，在管理工具中执行很快

可通过查看系统视图，及时发现活动会话中的慢 SQL，将其在计划缓存中的执行计划打印到 trc 文件中，核查是否与在管理工具中查询到的执行计划一致，偏差较大则清理缓存中执行计划。

具体方法如下：

通过 PLNDUMP 来看对应缓存中的 SQL 执行计划

查找出活动会话中执行时间大于 1S 的 SQL

select * from (
select timestampdiff(second,s.last_recv_time,sysdate) t ,s.* 
from v$sessions s  where state='ACTIVE')
where t > 1

找到对应慢 SQL 对应的 cache_item 值。

select * from v$cachepln where upper(sqlstr) like '%SQL%'

在 trace 目录中生成对应 trc 文件

alter session set events 'immediate trace name plndump ,level cache_item'

对比管理工具的执行计划和 .trc 文件中的执行计划。
清理内存中执行计划缓存。

call sp_clear_plan_cache();
call sp_clear_plan_cache(pln号)；---不加 pln 就是清理所有 sql 缓存。

如何看某条sql语句每个操作符的执行时间？

创建测试表

DROP TABLE if EXISTS TEST;
CREATE TABLE TEST (NAME VARCHAR2(200),ENTRY_DATE DATE,COMPANY VARCHAR2(200));
INSERT INTO TEST VALUES('王强',date'2008-1-1','电信');
INSERT INTO TEST VALUES('王强',date'2009-1-1','移动');
INSERT INTO TEST VALUES('王强',date'2010-1-1','联通');
INSERT INTO TEST VALUES('李四',date'2008-1-1','电网');
INSERT INTO TEST VALUES('李四',date'2009-1-1','财政局');
INSERT INTO TEST VALUES('张三',date'2012-12-1','腾讯');
INSERT INTO TEST VALUES('张三',date'2013-12-1','阿里');
INSERT INTO TEST VALUES('张三',date'2014-12-1','美团');
INSERT INTO TEST VALUES('张三',date'2015-12-1','饿了么');
COMMIT;
--SELECT * FROM TEST; --查看数据

查看表数据

在管理工具中执行如下语句：

SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);  
--动态打开会话级参数MONITOR_SQL_EXEC，该设置只对本会话有效,其余会话无影响

动态修改参数

执行sql语句并获取操作符执行时间

SELECT
        A.NAME,
        LISTAGG(A.COMPANY, '->') WITHIN GROUP (
ORDER BY
        A.ENTRY_DATE)
FROM
        TEST A
GROUP BY
        A.NAME;

执行sql语句

依次点击“消息”，“590”，（或者执行 et(590) ）弹出如下的操作符执行时间对话框：

获取操作符执行时间

如上图所示，该sql语句共涉及6个操作符，按照执行的先后顺序分别是SAGR2、SORT3、NSET2、CSCN2、DLCK、PRJT2。

其中，耗时最长的操作符是SAGR2，耗时1072微秒，占总执行时间的63.47%；耗时排第二位的操作符是SORT3，耗时536微秒，占总执行时间的31.73%。

根据上述的分析，我们就需要针对这2个操作符进行针对性的优化。

disql 查看执行计划

【问题说明】：
disql中开启自动跟踪 set autotrace trace + sql ，对应的执行计划看不到统计信息是否失真的对比（估算和实际记录数），三元组中记录数只有一个数字。

【解决方法】：
该功能开启前提是ini参数：ENABLE_MONITOR、MONITOR_SQL_EXEC 已开启，故运行时先执行以下脚本设置参数

SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1);

分析完后，记得关闭，以免影响性能

SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',0);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',0);'

disql查看执行计划

大量的 DML 动作(经常做增删改查)导致表的碎片很多，最终导致查询该表性能很慢，如何解决

【问题解决】：

达梦的数据通过 B 树维护的。可对指定索引进行空间整理，重组数据，达到优化该表的目的。具体操作如下，以 test 为例介绍：

---首先查看 TEST 表的索引名
select name from sysobjects where pid=(select id from sysobjects where name='TEST' and type$='SCHOBJ')and subtype$='INDEX';
---重组 TEST 表的索引
SP_REORGANIZE_INDEX('SYSDBA','INDEX33555469');

执行大批量提交删除操作时，运行效率较低

【问题描述】：

执行大批量提交删除操作时，运行效率较低，定位到 sql: delete from m_object where oid=?; 并且负载过高。
检查数据库没有阻塞 sql，没有锁等待，没有触发器。检查该列 oid 为主键列且执行计划也走了索引。此表数据量不到两百万，主键列的引用表个数 37 张，经常检索的引用表数据量大约 30G 左右，单条执行删除操作大约在 2-3s。

【问题解决】：

由于 M_OBJECT 表中主键列被其他表的外键约束引用，导致删除数据时会检查关联表的外键所以删除较慢，建议把外键列都加索引，约束改成 with index。
例如：alter table "M_VALUE_POLICY" modify constraint "FK_VALUE_POLICY" to foreign key("OID") references "M_OBJECT"("OID") with index;

注意
sql 效率执行慢原因除了外键约束引用情况，还要综合分析触发器和主备数据同步之间影响等情况。