优化

在应用中发现执行较慢的 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 效率执行慢原因除了外键约束引用情况,还要综合分析触发器和主备数据同步之间影响等情况。

微信扫码
分享文档
扫一扫
联系客服