遇到性能问题的sql如下:
sql1:
UPDATE amlclientlevel a
SET a.client_value = (SELECT l.client_value
FROM amlclientdynamiclevel l
WHERE l.inner_client_id = a.inner_client_id)
WHERE a.client_value = 0
AND a.need_value = '0'
AND a.client_status = '0'
AND EXISTS (SELECT 1
FROM amlclientdynamiclevel l
WHERE l.inner_client_id = a.inner_client_id);
此sql执行超过两个小时跑不出结果。两张表都是几百兆大小的小表,按照常理小表不应该产生性能问题。
执行计划1:
![](https://i-blog.csdnimg.cn/blog_migrate/e0d1ceab8bc92995348acc857a78abb4.png)
amlclientlevel 表上的索引
![](https://i-blog.csdnimg.cn/blog_migrate/4d51f97ee6d75b1e3ba000889ae592f3.png)
amlclientdynamiclevel 表上的索引
![](https://i-blog.csdnimg.cn/blog_migrate/91ebb4618ed62bcb077f606767b91a8c.png)
问题发生的原因有两点
第一点:上诉update的写法会导致amlclientlevel和amlclientdynamiclevel在两个地方产生关联,一处是EXISTS一处是set。
SET a.client_value = (SELECT l.client_value
FROM amlclientdynamiclevel l
WHERE l.inner_client_id = a.inner_client_id)
这样的set写法执行的过程其实是类比标量子查询的执行过程。对应update的每一个返回行都需要。
小知识点:
8 - filter(NVL("L"."INNER_CLIENT_ID",' ')=:B1)
sql本身没有绑定变量,在执行计划中 出现绑定变量值,应该是执行计划中出现了传值。应该留意这样的情况,传值可能导致严重的性能问题。
第二点:生成index hash join VIEW 。本身这样的执行步骤效率不高,结合set 引用的类比标量子查询的更新方式,导致数万次的index hash join VIEW生成及过滤数据成了整个sql的瓶颈。
执行计划2:
![](https://i-blog.csdnimg.cn/blog_migrate/a50d075b8b179c3dc8d9996096a6b952.png)
解决思路其实也是两点
第一点:改善set写法让其不再发生类比标量子查询的执行过程,减少一个关联。改写update为merge into的写法。
sql2:
MERGE /*+ GATHER_PLAN_STATISTICS J1 */ INTO AMLCLIENTLEVEL_BAK A
USING AMLCLIENTDYNAMICLEVEL_BAK L
ON (L.INNER_CLIENT_ID = A.INNER_CLIENT_ID)
WHEN MATCHED THEN
UPDATE SET A.CLIENT_VALUE = L.CLIENT_VALUE
WHERE A.CLIENT_VALUE = 0
AND A.NEED_VALUE = '0'
AND A.CLIENT_STATUS = '0';
执行计划3:
![](https://i-blog.csdnimg.cn/blog_migrate/9653cd5afee90455edfb293e7a7ef34b.png)
优化后的执行效率为13s。上面的执行计划缺少有效的数据过滤后再进行两表关联。
尝试改写merge让其先进行数据过滤
sql3:
MERGE /*+ GATHER_PLAN_STATISTICS J1 */ INTO (select * from AMLCLIENTLEVEL_BAK A WHERE A.CLIENT_VALUE = 0
AND A.NEED_VALUE = '0'
AND A.CLIENT_STATUS = '0') A
USING AMLCLIENTDYNAMICLEVEL_BAK L
ON (L.INNER_CLIENT_ID = A.INNER_CLIENT_ID)
WHEN MATCHED THEN
UPDATE SET A.CLIENT_VALUE = L.CLIENT_VALUE;
本次改写未起到先进行数据过滤作用,oracle内部选取的执行计划依旧是执行计划3。
实际上在表上添加了两个有效的索引后,sql3的执行计划(执行计划4)才是先进行数据过滤,效率已经提高。
create index AMLCLIENTLEVEL_BAK_IDX01 on AMLCLIENTLEVEL_BAK (CLIENT_VALUE, NEED_VALUE);
create index AMLCLIENTDYNAMICLEVEL_B_IDX1 on AMLCLIENTDYNAMICLEVEL_BAK (INNER_CLIENT_ID, CLIENT_VALUE);
执行计划4:
![](https://i-blog.csdnimg.cn/blog_migrate/4e2fd8e4a96b13daaf1b65f4ea82e6ec.png)
小知识点:
ON (L.INNER_CLIENT_ID = A.INNER_CLIENT_ID and A.CLIENT_VALUE = 0
AND A.NEED_VALUE = '0'
AND A.CLIENT_STATUS = '0')
SET中的字段不能出现在ON的关联条件中
第二点:优化index hash join VIEW,建立有效的索引,引导正确的sql的执行计划。
create index AMLCLIENTLEVEL_BAK_IDX01 on AMLCLIENTLEVEL_BAK (CLIENT_VALUE, NEED_VALUE);
create index AMLCLIENTDYNAMICLEVEL_B_IDX1 on AMLCLIENTDYNAMICLEVEL_BAK (INNER_CLIENT_ID, CLIENT_VALUE);
尝试建议索引不进行sql改写后的执行效率也是很高的。
执行计划5:
![](https://i-blog.csdnimg.cn/blog_migrate/ec26dbbfeb043cd72e1d2888327d6d66.png)
既然这样还有必要改写sql吗?
1、首先建立索引是比较危险的,可能导致其他sql出现性能问题,正确的方式是需要测试验证后决定。
2、针对不同业务系统数据处理情况决定,本次案例中数据量小,展示不出update和merge性能上的巨大差距。
3、如果数据量非常大,可能既需要sql改写又需要添加有效的索引。