利用Merge into 改写Update SQL 一例
2024-08-24 05:23:51
前言
客户说，生产系统最近CPU使用率经常达到100%，请DBA帮忙调查一下。
根据客户提供的情况描述及对应时间段，我导出AWR，发现如下问题：
11v41vaj06pjd ：每次执行消耗2,378,874.14 buffer 约等于18g 内存
bsfrz471nh9s4：每次执行消耗1,545,875.18 buffer 约等于12g 内存
非常大的内存消耗，而且执行频率高。
所以就断定这两条sql就是cpu使用率高的祸源，只要优化这两条sql，cpu必然而然的降下来。
优化前
这两条sql的结构是一样的，只是表连接有所不同，所以优化方法都是一致的。
update mm_writeoutstatus_to s
set s.status = '00'
where s.status = '0Z'
and s.id in (select distinct t.id
from mm_writeout_to t, mm_paymentin_events_td p
where exists (select 1
from mm_paymentin_events_td m,
mm_paymentin_events_td m1
where m.newno = 1420467997
and m.fatherno = m1.listno
and m1.sonno = p.listno)
and t.businessno = p.newno);
Execution Plan
----------------------------------------------------------
Plan hash value: 393324829
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 21 | 4437 (1)| 00:00:54 |
| 1 | UPDATE | MM_WRITEOUTSTATUS_TO | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | MM_WRITEOUTSTATUS_TO | 789 | 16569 | 96 (0)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | IDX_WRITEOUTSTATUS_TEST | 789 | | 6 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 52 | 11 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 38 | 9 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 27 | 7 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| MM_WRITEOUT_TO | 1 | 18 | 3 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_MM_WRITEOUT_TO | 1 | | 2 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| MM_PAYMENTIN_EVENTS_TD | 1 | 9 | 4 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IDX_PAYMENTINE_08 | 4 | | 2 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | MM_PAYMENTIN_EVENTS_TD | 1 | 11 | 2 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_MM_PAYMENTIN_EVENTS_TD | 1 | | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID | MM_PAYMENTIN_EVENTS_TD | 1 | 14 | 2 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_MM_PAYMENTIN_EVENTS_TD | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "MM_PAYMENTIN_EVENTS_TD" "P","MM_WRITEOUT_TO"
"T","MM_PAYMENTIN_EVENTS_TD" "M1","MM_PAYMENTIN_EVENTS_TD" "M" WHERE "M"."NEWNO"=1420467997 AND
"M"."FATHERNO" IS NOT NULL AND "M"."FATHERNO"="M1"."LISTNO" AND "M1"."SONNO" IS NOT NULL AND
"T"."ID"=:B1 AND "M1"."SONNO"="P"."LISTNO" AND "P"."NEWNO"=TO_NUMBER("T"."BUSINESSNO")))
4 - access("S"."STATUS"='0Z')
9 - access("T"."ID"=:B1)
10 - filter("M"."FATHERNO" IS NOT NULL)
11 - access("M"."NEWNO"=1420467997)
12 - filter("M1"."SONNO" IS NOT NULL)
13 - access("M"."FATHERNO"="M1"."LISTNO")
14 - filter("P"."NEWNO"=TO_NUMBER("T"."BUSINESSNO"))
15 - access("M1"."SONNO"="P"."LISTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1830312 consistent gets
154 physical reads
0 redo size
830 bytes sent via SQL*Net to client
1240 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
分析
执行计划中有走filter关键字，且有两个子级，我们都知道，走这种连接方式是非常耗费性能的，主表返回多少行，被驱动表就得被扫描多少次。
利用merge into 可以等价改写update语句。
优化后
merge into mm_writeoutstatus_to s
using (select distinct t.id
from mm_writeout_to t, mm_paymentin_events_td p
where exists (select 1
from mm_paymentin_events_td m,
mm_paymentin_events_td m1
where m.newno = 1420467997
and m.fatherno = m1.listno
and m1.sonno = p.listno)
and t.businessno = p.newno)b
on (s.id = b.id)
when matched then
update set s.status = '00' where s.status = '0Z'
Execution Plan
----------------------------------------------------------
Plan hash value: 1386952490
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | 59 | 9822 (1)| 00:01:58 |
| 1 | MERGE | MM_WRITEOUTSTATUS_TO | | | | |
| 2 | VIEW | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | MM_WRITEOUTSTATUS_TO | 1 | 53 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 66 | 9822 (1)| 00:01:58 |
| 5 | VIEW | | 1 | 13 | 9819 (1)| 00:01:58 |
| 6 | SORT UNIQUE | | 1 | 52 | 9819 (1)| 00:01:58 |
|* 7 | HASH JOIN | | 1 | 52 | 9818 (1)| 00:01:58 |
| 8 | NESTED LOOPS | | 1 | 34 | 9 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 20 | 7 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| MM_PAYMENTIN_EVENTS_TD | 1 | 9 | 5 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IDX_PAYMENTINE_08 | 4 | | 3 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID| MM_PAYMENTIN_EVENTS_TD | 1 | 11 | 2 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_MM_PAYMENTIN_EVENTS_TD | 1 | | 1 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | MM_PAYMENTIN_EVENTS_TD | 1 | 14 | 2 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_MM_PAYMENTIN_EVENTS_TD | 1 | | 1 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | MM_WRITEOUT_TO | 1124K| 19M| 9797 (1)| 00:01:58 |
|* 17 | INDEX RANGE SCAN | IDX_WRITEOUTSTATUS_1 | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("P"."NEWNO"=TO_NUMBER("T"."BUSINESSNO"))
10 - filter("M"."FATHERNO" IS NOT NULL)
11 - access("M"."NEWNO"=1420467997)
12 - filter("M1"."SONNO" IS NOT NULL)
13 - access("M"."FATHERNO"="M1"."LISTNO")
15 - access("M1"."SONNO"="P"."LISTNO")
17 - access("S"."ID"="B"."ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
54083 consistent gets
0 physical reads
0 redo size
832 bytes sent via SQL*Net to client
1281 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
------------------------------------------------------------------------------------------------------------------------------优化前每次执行需要1830312 次逻辑读，优化后每次执行需要54083 次逻辑读，性能提升33.8倍
最新文章
- STM32下载调试驱动问题
- apache和tomcat有什么不同，为什么要整合apache 和tomcat？
- Oracle笔记 六、PL/SQL简单语句块、变量定义
- 2013 长沙网络赛J题
- HTML 学习网站
- C++中重载、重写（覆盖）和隐藏的区别实例分析
- jQuery弹出窗口完整代码
- Telerik 控件 RadGridview实现绑定
- UNIX环境高级编程——标准I/O库
- Java架构师技能发展脑图
- JAVA的8种基本数据类型和类型转换
- LeetCode #003# Longest Substring Without Repeating Characters（js描述）
- css 如何“画”一个抽奖转盘
- javascript 函数的4种调用模式
- node.js中express模块创建服务器和http模块客户端发请求
- IIS隐藏版本号教程（Windows Server 2003）
- AngularJS理论知识
- 洛谷P1387 最大正方形
- RecyclerView中实现headerView，footerView功能
- 【MacOS】brew－python3
热门文章
- 类似查询mysql数据库的查询XML的JS类
- Servlet，jsp，jsp的9大内置对象
- SpringMVC传递multiple类型select后台Controller的接收方法
- logstsh | logstash-input-jdbc 启动错误收集
- poj 2083 Fractal 递归 图形打印
- [ZPG TEST 116] 最小边权和【生成树相关】
- Service官方教程(1)Started与Bound的区别、要实现的函数、声明service
- Spring MVC 结合Velocity视图出现中文乱码的解决方案
- angular.module 参数的意思
- WPF学习08：MVVM 预备知识之COMMAND