oracle Exadata x7 上进行SQL优化从1小时优化到毫秒级

1.背景

2024-12-02日，上班第一件事情就是巡检，巡检发现CPU 达到100%，经过排查是开发厂家跑一个定时任务，定时任务开了64个并发。
备注：表名已脱敏
SQL如下：
SELECT COUNT(1) FROM YK02_ZT YK02, WC61, YK01_ZT YK01 WHERE WC61.AYK001 = YK01.AYK999 AND WC61.AAA325 = :B3 AND WC61.AAE140 = :B2 AND YK02.AAE140 = :B1 AND YK01.AYK001 = YK02.AYK001 AND YK02.AYK008 = '4' AND NOT EXISTS (SELECT 1 FROM YE32_ZT YE32 WHERE YE32.AAZ777 = YK01.AYK001) AND ROWNUM < 2

2.处理过称

2.1 获取绑定变量的值

select dbms_sqltune.extract_bind(w.bind_data, 1).value_string 
||'-'|| dbms_sqltune.extract_bind(w.bind_data, 2).value_string 
||'-'|| dbms_sqltune.extract_bind(w.bind_data, 3).value_strin
||'-'|| dbms_sqltune.extract_bind(w.bind_data, 4).value_string 
||'-'|| dbms_sqltune.extract_bind(w.bind_data, 5).value_string
||'-'|| dbms_sqltune.extract_bind(w.bind_data, 6).value_string 
bind_data ,q.SQL_TEXT 
FROM   sys.wrh$_sqlstat w,v$sql q
WHERE w.sql_id=q.SQL_ID 
AND  w.sql_id = '620g972n1n06s'; 

2.2 通过10053 查看真实的执行计划

结论：NOT EXISTS (SELECT 1 FROM YE32_ZT YE32 WHERE YE32.AAZ777 = YK01.AYK001) 这步导致的

2.3 优化思路

（1）NOT EXISTS 改成not in 适用场景：子查询内数据量特别小才合适，
（2）NOT EXISTS 改成 LEFT JOIN ... WHERE ... IS NULL 适合场景:左边返回少量数据
     根据每个表的数据量和返回的结果数，本案例适合用left join

2.4 SQL优化成果如下

select  COUNT(1) from (select YK01.AYK001 FROM YK02_ZT YK02,WC61, YK01_ZT YK01
WHERE WC61.AYK001 = YK01.AYK999
AND WC61.AAA325 = 'XXX'
AND WC61.AAE140 = 'X'
AND YK02.AAE140 = 'XX'
AND YK01.AYK001 = YK02.AYK001
AND YK02.AYK008 = '4'
AND ROWNUM < 2) tt left join YE32_ZT YE32 on tt.AYK001=YE32.AAZ777 where YE32.AAZ777 is null

3.运行结果

SQL从1小时优化成1毫秒