merge语句导致的CPU使用率过高的优化(二)

简介: 之前分享过一篇关于merge语句导致的CPU使用率过高优化的案例。的http://blog.itpub.net/23718752/viewspace-1819471/ 后续的跟进没有补充,也“秀”一张图,红色的火焰是原来的系统负载,右边的部分是最近的逻辑读情况,不过惭愧的是,这个不是优化的效果,因为应用的高峰期已经处理完了,后面的sql调用频率极低,所以感觉不到任何的压力。
之前分享过一篇关于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上线也要评估,进行验收测试。尽可能把问题都解决在沟通层面,不用那么多的邮件来标注,说明。
目录
相关文章
|
12天前
|
编译器 Linux C语言
C++新特性“CPU优化对齐”
C++新特性“CPU优化对齐”
29 3
|
4月前
|
Shell
我来教你如何将cpu使用率up起来(shell脚本[含注释])
我来教你如何将cpu使用率up起来(shell脚本[含注释])
148 0
|
5月前
|
Linux
模拟Linux服务器高cpu使用率
模拟Linux服务器高cpu使用率
|
8天前
|
存储 机器学习/深度学习 测试技术
mnn-llm: 大语言模型端侧CPU推理优化
mnn-llm: 大语言模型端侧CPU推理优化
30 1
|
1月前
|
存储 缓存 算法
如何优化 CPU 通道的使用
如何优化 CPU 通道的使用
20 0
|
3月前
|
弹性计算 Linux 数据安全/隐私保护
Linux【问题记录 01】阿里云CPU使用率 100% ECS 同时连接数峰值 25k+ 问题排查无果(附阿里云重新初始化云盘详细步骤)
Linux【问题记录 01】阿里云CPU使用率 100% ECS 同时连接数峰值 25k+ 问题排查无果(附阿里云重新初始化云盘详细步骤)
79 0
|
3月前
|
存储 人工智能 缓存
探索AIGC未来:CPU源码优化、多GPU编程与中国算力瓶颈与发展
近年来,AIGC的技术取得了长足的进步,其中最为重要的技术之一是基于源代码的CPU调优,可以有效地提高人工智能模型的训练速度和效率,从而加快了人工智能的应用进程。同时,多GPU编程技术也在不断发展,大大提高人工智能模型的计算能力,更好地满足实际应用的需求。 本文将分析AIGC的最新进展,深入探讨以上话题,以及中国算力产业的瓶颈和趋势。
|
3月前
当Hologres实例的CPU使用率长期保持在100%时
当Hologres实例的CPU使用率长期保持在100%时
65 2
|
4月前
|
存储 缓存 Linux
高效利用CPU缓存一致性:优化技巧与策略分析
高效利用CPU缓存一致性:优化技巧与策略分析
|
5月前
|
缓存 数据库 OceanBase
CPU使用率却达到了100%
CPU使用率却达到了100%
149 1

热门文章

最新文章