专栏首页杨建荣的学习笔记merge语句导致的CPU使用率过高的优化(二) (r7笔记第9天)

merge语句导致的CPU使用率过高的优化(二) (r7笔记第9天)

之前分享过一篇关于merge语句导致的CPU使用率过高优化的案例。http://blog.itpub.net/23718752/viewspace-1819471/ 后续的跟进没有补充,也“秀”一张图,红色的火焰是原来的系统负载,右边的部分是最近的逻辑读情况,不过惭愧的是,这个不是优化的效果,因为应用的高峰期 已经处理完了,后面的sql调用频率极低,所以感觉不到任何的压力。所以通过这个图也可以看出,给一张差别巨大的图也不一定是系统优化的效果,也可能是其 它外在因素。

那么既然要说跟进,后面的情节才够真实和现实,开发同学找到语句,修改花了些时间,今天突然联系到我,说已经修改完成了。我也从v$sql中抓取了几条语句,发现执行计划已经改变。 感觉这件事情就要告一段落,但是开发的同事过了一会找到我说,他们在应用端发现日志中出现了ORA-00001的错误。 ### Cause: java.sql.SQLException: ORA-00001: unique constraint (AXXXX.OPENPLATFORM_USER) violated 这个问题着实在意料之外,他们反馈出现问题后,立即回退了代码,但是日志保留了下来,让我看看是什么问题。找开发要绑定变量的值,貌似还比较困难,那就算了,自己分析吧。 比如还是简单模拟这个错误。 CREATE TABLE TEST(ID NUMBER,NAME VARCHAR(100)); ALTER TABLE TEST MODIFY(ID UNIQUE); INSERT INTO TEST VALUES(100,'BB_NOT_MATCHED'); INSERT INTO TEST VALUES(1000,'BBB_NOT_MATCHED'); SQL> select*from test; ID NAME ---------- -------------------- 1000 BB_NOT_MATCHED 100 BBB_NOT_MATCHED 我们已经插入了两条值,这个时候来尝试一下,update是否生效 SQL> MERGE INTO TEST t USING (SELECT ID from TEST where ID=1000 union select -1 id from dual ) tw ON (tw.ID=T.ID) WHEN MATCHED THEN UPDATE SET t.NAME='AAA_MATCHED' where ID= 1000 WHEN NOT MATCHED THEN INSERT(ID, NAME) VALUES(1000,'BBB_NOT_MATCHED') ; MERGE INTO TEST t * ERROR at line 1: ORA-00001: unique constraint (TEST.SYS_C0011234) violated 这个错误还是有些奇怪,本来预计的update变成了insert,结果还违反了唯一性约束。 来看看最初始版本的执行情况。 SQL> MERGE INTO TEST t USING (SELECT count(*) CNT from TEST where ID=1000 ) tw ON (tw.CNT>0) WHEN MATCHED THEN UPDATE SET t.NAME='AAA_MATCHED' where ID=1000 WHEN NOT MATCHED THEN INSERT(ID,NAME) VALUES(1000,'BBB_NOT_MATCHED'); 1 row merged. 发现确实是做了update,把id=1000的行,name列修改成了AAA_MATCHED SQL> select *from test; ID NAME ---------- -------------------- 1000 AAA_MATCHED 100 BBB_NOT_MATCHED 手工把数据改回来,继续测试。 SQL> update test set name='BBB_NOT_MATCHED' where id=1000; 1 row updated. SQL> select *from test; ID NAME ---------- -------------------- 1000 BBB_NOT_MATCHED 100 BBB_NOT_MATCHED 这个时候开发的同学突然给我反馈说,他们看如果把union all的字句取消就不报错了。原来他们也在debug,我告诉他们,不报错不代表没错,还是需要搞明白最根本的原因。 MERGE INTO TEST t USING (SELECT ID from TEST where ID=1000 ) tw ON (tw.ID=T.ID) WHEN MATCHED THEN UPDATE SET t.NAME='AAA_MATCHED' where ID= 1000 WHEN NOT MATCHED THEN INSERT(ID, NAME) VALUES(1000,'BBB_NOT_MATCHED') ; SQL> select *from test; ID NAME ---------- -------------------- 1000 AAA_MATCHED 100 BBB_NOT_MATCHED 这个时候update确实能够正常执行,似乎也是预期的结果,那么做一条insert,看看效果。 MERGE INTO TEST t USING (SELECT ID from TEST where ID=2000 ) tw ON (tw.ID=T.ID) WHEN MATCHED THEN UPDATE SET t.NAME='AAA_MATCHED' where ID= 2000 WHEN NOT MATCHED THEN INSERT(ID, NAME) VALUES(2000,'BBB_NOT_MATCHED') ; 0 rows merged. 可以看到,id=2000的行没有插入数据。这个我觉得也就是为什么开发的同学没有选用这个方法的根本原因。但是似乎他们没有找到更好的方法, 那么继续改进,就是我上次分享的,加入union all的部分。 MERGE INTO TEST t USING (SELECT ID from TEST where ID=2000 union all select -999 from dual ) tw ON (tw.ID=T.ID) WHEN MATCHED THEN UPDATE SET t.NAME='AAA_MATCHED' where ID= 2000 WHEN NOT MATCHED THEN INSERT(ID, NAME) VALUES(2000,'BBB_NOT_MATCHED') ; 1 row merged. SQL> select *from test; ID NAME ---------- -------------------- 1000 AAA_MATCHED 100 BBB_NOT_MATCHED 2000 BBB_NOT_MATCHED 这个时候问题来了,insert可以了,但是update有问题了。 SQL> MERGE INTO TEST t 2 USING (SELECT ID from TEST where ID=2000 union all select -999 from dual ) tw 3 ON (tw.ID=T.ID ) 4 WHEN MATCHED THEN UPDATE SET t.NAME='AAA_MATCHED' 5 where ID= 2000 6 WHEN NOT MATCHED THEN INSERT(ID, NAME) VALUES(2000,'BBB_NOT_MATCHED') ; MERGE INTO TEST t * ERROR at line 1: ORA-00001: unique constraint (TEST.SYS_C0011234) violated 如果你看晕了,我来整理一下思路。 ###加入union all的方案 不存在id=2000 可以insert 已存在id=2000 update 报ora-00001 ### 去掉union all子句 不存在id=2000 可以插入 已存在id=2000 update无法执行 那么来一个动态的条件,可以不? SQL> MERGE INTO TEST t USING (SELECT ID from TEST where ID=2000 union all select -999 from dual) tw ON (tw.ID=T.ID or tw.id=-999) WHEN MATCHED THEN UPDATE SET t.NAME='AAA_MATCHED' where ID= 2000 WHEN NOT MATCHED THEN INSERT(ID, NAME) VALUES(2000,'BBB_NOT_MATCHED') ; MERGE INTO TEST t * ERROR at line 1: ORA-30926: unable to get a stable set of rows in the source tables 所以这些思路都不同,但是根据id来决定Inert,update也算一个常规问题,吃完晚饭继续琢磨,总算找到了一个合适的方法。 SQL> MERGE INTO TEST t USING (SELECT 2000 id FROM dual ) tw ON (tw.ID=T.ID ) WHEN MATCHED THEN UPDATE SET t.NAME='AAA_MATCHED' where ID= 2000 WHEN NOT MATCHED THEN INSERT(ID, NAME) VALUES(2000,'BBB_NOT_MATCHED') ; 1 row merged. 这种情况下条件是唯一性匹配的,匹配与否就很清晰了。 当前数据情况如下: SQL> select *from test; ID NAME ---------- -------------------- 1000 AAA_MATCHED 100 BBB_NOT_MATCHED 2000 AAA_MATCHED 插入一条新数据 SQL> MERGE INTO TEST t USING (SELECT 3000 id FROM dual ) tw ON (tw.ID=T.ID ) WHEN MATCHED THEN UPDATE SET t.NAME='AAA_MATCHED' where ID= 3000 WHEN NOT MATCHED THEN INSERT(ID, NAME) VALUES(3000,'BBB_NOT_MATCHED') ; 1 row merged. SQL> select *from test where id=3000; ID NAME ---------- -------------------- 3000 BBB_NOT_MATCHED 更新一条记录 SQL> MERGE INTO TEST t USING (SELECT 3000 id FROM dual ) tw ON (tw.ID=T.ID ) WHEN MATCHED THEN UPDATE SET t.NAME='AAA_MATCHED' where ID= 3000 WHEN NOT MATCHED THEN INSERT(ID, NAME) VALUES(3000,'BBB_NOT_MATCHED') ; SQL> select *from test where id=3000; ID NAME ---------- -------------------- 3000 AAA_MATCHED 改进后的执行计划如下: Execution Plan ---------------------------------------------------------- Plan hash value: 3869333021 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 1 | 78 | 3 (0)| 00:00:01 | | 1 | MERGE | TEST | | | | | | 2 | VIEW | | | | | | | 3 | NESTED LOOPS OUTER | | 1 | 79 | 3 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 77 | 1 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | SYS_C0011234 | 1 | | 0 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - access("T"."ID"(+)=CASE WHEN (ROWID IS NOT NULL) THEN 3000 ELSE 3000 END ) 1 row merged. 所以正式环境的语句也是类似的思路。 MERGE INTO UC_OPENPLATFORM_USER t USING (SELECT :1 USER_ID,:2 PLATFORM from DUAL ) tw ON (tw.USER_ID=T.USER_ID and tw.PLATFORM=t.PLATFORM) WHEN MATCHED THEN UPDATE SET t.NAME=:3, t.UPDATE_DATE=SYSDATE where USER_ID=:4 and PLATFORM=:5 WHEN NOT MATCHED THEN INSERT(USER_ID, PLATFORM, NAME, CREATE_DATE, UPDATE_DATE) VALUES(:6, :7, :8, SYSDATE, SYSDATE) 改动之后还是需要再部署测试,相信大体就没有问题了。 通过这个案例可以发现,很多优化的时候从执行计划等情况确实有了很大的提升,一些瓶颈也得到了解决,但是还是要更周密的测试,别修复了一个错,引来更多的问题。而且sql上线也要评估,进行验收测试。尽可能把问题都解决在沟通层面,不用那么多的邮件来标注,说明。

本文分享自微信公众号 - 杨建荣的学习笔记(jianrong-notes),作者:杨建荣

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2015-11-04

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • merge语句导致的CPU使用率过高的优化(r7笔记第4天)

    今天有一个数据库有点反常,早上的时候报出了CPU使用率的警告。 警告内容如下: ZABBIX-监控系统: -----------------------...

    jeanron100
  • 数据同步中的动态调度

    动态调度的需求是怎样的呢?比如现在10:00,我需要10:30同步一次数据,那么10:30的时候同步时,我需要考虑现在的主从延迟,如果延迟较大,我需要把延迟的时...

    jeanron100
  • vi的补充学习(r4笔记第25天)

    今天突然发现vi虽然用了些日子了,但是常用的一些命令之外,还是有些命令比较生疏,简单总结了一下,然后自己在vi里面编辑了一把,效果还不错。 对于大家比较熟悉且常...

    jeanron100
  • 利用Cobra实现自动化代码审计的经验分享

    本文介绍了笔者通过一个简单的方法利用Cobra工具来实现自动化代码审计的经验,以及对Cobra工具代码的一些定制改动。

    FB客服
  • Google Chrome 正在走 Windows 的老路

    Google Chrome 将成为第二个Windows:人人都在使用,但人人都不喜欢

    纯洁的微笑
  • Chrome插件英雄榜(第二期)

    Chrome插件英雄榜, 为优秀的Chrome插件写一本中文说明书, 让Chrome插件英雄们造福人类~

    zhaoolee
  • 50张Gif动图演绎 Chrome插件英雄榜

    Google Results Previewer的功能简单实用, 也没有多余的设置, 属于新手友好型工具

    zhaoolee
  • 我们不再需要 Chrome?

    十年前,浏览器的主要市场还在被微软的 IE 牢牢占据着。IE 作为 Windows 的默认浏览器,基本上控制了绝大部分人访问互联网的方式。

    谭庆波
  • Chrome是一个好用的计算器

    我每周都要给网站做一个上周访问量的统计, 但百度只有最近7天, 没有上周, 这就非常尴尬, 所以只能手动算一下

    zhaoolee

扫码关注云+社区

领取腾讯云代金券