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