真实SQL优化案例
为避免项目隐私泄露；
本篇文章所有表名、字段名，包括执行计划内的对象名称都做了处理。
本篇文章是将速度再10秒左右的SQL优化到1.5s左右；
因为没有优化到1s以下，所以可能还存在更优优化方法；
但其中涉及的优化技巧，可以供您赏析。
目录
项目场景
SQL分析
优化方案
优化总结
项目场景
甲方反应如下SQL执行缓慢，需要10秒左右才能执行完。
SELECT
COUNT(1) AS CNT
FROM LA
LEFT JOIN IMO ON LA.ID = IMO.ID
WHERE IMO.SOURCE_ID IS NULL
AND IMO.STATUS_ID NOT IN ('080','085')该SQL的查询结果是 2498900 。
SQL分析
- SQL本身逻辑分析
Ⅰ：大表
上述SQL的查询结果是 2498900 ；说明两张表本身是大表。经过如下查询，确实是两张大表，LA大小是0.9G，IMO大小是2.16G。
也就是说，这是两张大表进行关联查询。
--0.9G
select
round(ds.bytes/1024/1024/1024,2) as tablesize,
ds.*
from dba_segments ds where segment_name = 'LA'
--2.16G
select
round(ds.bytes/1024/1024/1024,2) as tablesize,
ds.*
from dba_segments ds where segment_name = 'IMO'Ⅱ：WHERE条件
WHERE条件中只有两个谓词字段：
IMO.SOURCE_ID IS NULL 、IMO.STATUS_ID，这两个其实在业务逻辑上只是作废状态判断。也就是说过滤掉的数据不会超过总数据量的20%，相当于会统计LA表的80%数据。
IMO.STATUS_ID NOT IN ('080','085')，这个条件使用了NOT IN，也就意味着即使在IMO.STATUS_ID 字段上建立了索引，也不会再走索引了。
- 执行计划分析
以下是上述SQL的执行计划，已剔除无关部分。
Plan hash value: 3381251447
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 30538 (100)| | 1 |00:00:10.03 | 73476 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 27 | | | | 1 |00:00:10.03 | 73476 | | | |
|* 2 | HASH JOIN | | 1 | 5265K| 135M| 67M| 30538 (1)| 00:00:02 | 2498K|00:00:09.59 | 73476 | 148M| 17M| 161M (0)|
| 3 | INDEX FAST FULL SCAN| UK_20230901210804_1007994 | 1 | 2608K| 37M| | 2770 (1)| 00:00:01 | 2625K|00:00:00.80 | 11138 | | | |
|* 4 | INDEX FAST FULL SCAN| IDX_IMO_3 | 1 | 6647K| 76M| | 16837 (1)| 00:00:01 | 6162K|00:00:03.13 | 62338 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LA"."ID"="IMO"."ID")
4 - filter(("IMO"."ID" IS NOT NULL AND "IMO"."SOURCE_ID" IS NULL AND "IMO"."STATUS_ID"<>'080' AND "IMO"."STATUS_ID"<>'085'))
id=1（SORT AGGREGATE ）
因为上述SQL中有COUNT()，所以 出现了 SORT AGGREGATE 。SORT AGGREGATE并不做真正的排序，不会用到排序空间。所以上述SQL的真正性能问题不在此处。
id=2（HASH JOIN）
两张大表关联，且需要查询大量数据时，需要走hash连接。结合上面的分析，所以此处执行计划，我认为是没有问题的。
id=3 与 id=4
这里大家看不到索引对应的表，这个 UK_20230901210804_1007994 其实是LA表的索引，上文表述过了，LA表的大小小于IMO表，所以理论上LA表应该是hash连接中的驱动表。执行计划中离hash关键字最近的表就是驱动表，所以这里的驱动表没有问题。
且我们可以发现 Used-Mem是161M，说明hash连接消耗了PGA 161M的内存。
根据这个161M需要再进一步和大家分享说明一下hash连接的算法：两表等值关联，返回大量数据，将较小的表作为驱动表。将驱动表的select字段和连接字段读入PGA中，然后对驱动表的连接字段进行hash运算生成hash table；当驱动表的所有数据都读入PGA后，再读取被驱动表，对被驱动表的连接列也进行hash运算；然后在PGA中探测hash table，找到数据就关联上。
所以这就解释了两个大小过G的表只消耗了161M的PGA。
除了驱动表与PGA大小，我们可以看到两张表都走了索引。索引扫描是单块读，全表扫描是多块读，读取大量数据时，选择全表扫描更合适，且全表扫描也不会发生回表操作。但因为SELECT 字段只是一个count计数，Oracle的CBO优化器算法可能认为数据量还不够大，且此时也不需要回表，所以走了索引扫描，我认为问题也不是很大。
综上所述，我认为执行计划本身不存在什么太大问题，那怎么优化呢？
- 调优技巧：并行查询
这里有一个查询调优技巧：开启并行查询。
启用并行查询，说的比较白话一点就是将hash运算拆成n份。
例如对本文SQL启用10个并行查询，LA表会根据连接列进行hash运算然后拆成10份：LA1、LA2、....... LA10；IMO表也会根据连接列进行hash运算然后拆成10份：IMO1、IMO2、..... IMO10。相当于改写成如下SQL：
SELECT
COUNT(1) AS CNT
FROM LA1
LEFT JOIN IMO1 ON LA1.ID = IMO1.ID
WHERE IMO1.SOURCE_ID IS NULL
AND IMO1.STATUS_ID NOT IN ('080','085')
UNION ALL
SELECT
COUNT(1) AS CNT
FROM LA2
LEFT JOIN IMO2 ON LA2.ID = IMO2.ID
WHERE IMO2.SOURCE_ID IS NULL
AND IMO2.STATUS_ID NOT IN ('080','085')
......
UNION ALL
SELECT
COUNT(1) AS CNT
FROM LA10
LEFT JOIN IMO10 ON LA10.ID = IMO10.ID
WHERE IMO10.SOURCE_ID IS NULL
AND IMO10.STATUS_ID NOT IN ('080','085')优化方案
那么如何开启并行查询呢？这就需要写HINT，如下代码所示。
SELECT
/*+ parallel(10) use_hash(LA,IMO) pq_distribute(IMO hash,hash)*/
COUNT(1) AS CNT
FROM LA
LEFT JOIN IMO ON LA.ID = IMO.ID
WHERE IMO.SOURCE_ID IS NULL
AND IMO.STATUS_ID NOT IN ('080','085')我把HINT单独摘出来，这里添加的hint是pq_distribute(被驱动表 hash hash) ；
其中use_hash(驱动表，被驱动表)的用法是走hash连接，LA是驱动表，IMO是被驱动表，顺序不要错。
/*+ parallel(10) use_hash(LA,IMO) pq_distribute(IMO hash,hash)*/此时执行完上述SQL，需要1.5秒左右。速度提升了几倍。
下面是优化后SQL的执行计划
大家可以看到各种资源消耗和ATIME都下降非常多。
SQL_ID 9uwrm2pp44xvp, child number 0
-------------------------------------
SELECT /*+ parallel(10) use_hash(LA,IMO) pq_distribute(IMO hash,hash)*/
COUNT(1) CNT FROM INPORD.LAB_APPLY LA LEFT JOIN INPORD.MEDICAL_ORDER
IMO ON LA.LAB_APPLY_FLOW = IMO.RELATION_KEY WHERE IMO.ORDER_SOURCE_CODE
IS NULL AND IMO.ORDER_STATUS NOT IN ('080','085')
Plan hash value: 4058815446
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2182 (100)| | | | | 1 |00:00:00.66 | 24 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 27 | | | | | | 1 |00:00:00.66 | 24 | | | |
| 2 | PX COORDINATOR | | 1 | | | | | | | | 10 |00:00:00.66 | 24 | 73728 | 73728 | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 0 | 1 | 27 | | | Q1,02 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 | | | |
| 4 | SORT AGGREGATE | | 0 | 1 | 27 | | | Q1,02 | PCWP | | 0 |00:00:00.01 | 0 | | | |
|* 5 | HASH JOIN | | 0 | 4917K| 126M| 2182 (1)| 00:00:01 | Q1,02 | PCWP | | 0 |00:00:00.01 | 0 | 148M| 17M| 15M (0)|
| 6 | PX RECEIVE | | 0 | 2608K| 37M| 308 (1)| 00:00:01 | Q1,02 | PCWP | | 0 |00:00:00.01 | 0 | | | |
| 7 | PX SEND HYBRID HASH | :TQ10000 | 0 | 2608K| 37M| 308 (1)| 00:00:01 | Q1,00 | P->P | HYBRID HASH| 0 |00:00:00.01 | 0 | | | |
| 8 | STATISTICS COLLECTOR | | 0 | | | | | Q1,00 | PCWC | | 0 |00:00:00.01 | 0 | | | |
| 9 | PX BLOCK ITERATOR | | 0 | 2608K| 37M| 308 (1)| 00:00:01 | Q1,00 | PCWC | | 0 |00:00:00.01 | 0 | | | |
|* 10 | INDEX FAST FULL SCAN | UK_20230901210804_1007994 | 0 | 2608K| 37M| 308 (1)| 00:00:01 | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 | | | |
| 11 | PX RECEIVE | | 0 | 6207K| 71M| 1871 (1)| 00:00:01 | Q1,02 | PCWP | | 0 |00:00:00.01 | 0 | | | |
| 12 | PX SEND HYBRID HASH (SKEW)| :TQ10001 | 0 | 6207K| 71M| 1871 (1)| 00:00:01 | Q1,01 | P->P | HYBRID HASH| 0 |00:00:00.01 | 0 | | | |
| 13 | PX BLOCK ITERATOR | | 0 | 6207K| 71M| 1871 (1)| 00:00:01 | Q1,01 | PCWC | | 0 |00:00:00.01 | 0 | | | |
|* 14 | INDEX FAST FULL SCAN | IDX_IMO_3 | 0 | 6207K| 71M| 1871 (1)| 00:00:01 | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("LA"."ID"="IMO"."ID")
10 - access(:Z>=:Z AND :Z<=:Z)
14 - access(:Z>=:Z AND :Z<=:Z)
filter(("IMO"."ID" IS NOT NULL AND "IMO"."SOURCE_ID" IS NULL AND "IMO"."STATUS_ID"<>'080' AND "IMO"."STATUS_ID"<>'085'))
优化总结
/*+ parallel(n) use_hash(LA,IMO) pq_distribute(IMO hash,hash)*/
parallel(n)中的这个n并不是越大就越好。
这种并行查询方法只会在表连接查询消耗PGA大小合适的时候才能发挥最大作用，
它其实对于几十G这种远远超过PGA大小的表连接时，就不好使了；对于这种情况还有其他优化方法。
这个SQL应该还有其他优化方法，毕竟我只优化到了1.5s左右。
但我现在还是太愚笨了，只能想到这个方法优化。
如果后续我找到其他优化方法，会再和大家分享。
谢谢您的阅读！