郑州做公司网站的,文化传媒建设网站,注册公司最好用老年人,有没有免费学编程的网站真实SQL优化案例 为避免项目隐私泄露#xff1b; 本篇文章所有表名、字段名#xff0c;包括执行计划内的对象名称都做了处理。 本篇文章是将速度再10秒左右的SQL优化到1.5s左右#xff1b; 因为没有优化到1s以下#xff0c;所以可能还存在更优优化方法#xff1b; 但其中… 真实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.9GIMO大小是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.IDIMO.ID)4 - filter((IMO.ID IS NOT NULL AND IMO.SOURCE_ID IS NULL AND IMO.STATUS_ID080 AND IMO.STATUS_ID085))id1SORT AGGREGATE
因为上述SQL中有COUNT()所以 出现了 SORT AGGREGATE 。SORT AGGREGATE并不做真正的排序不会用到排序空间。所以上述SQL的真正性能问题不在此处。
id2HASH JOIN
两张大表关联且需要查询大量数据时需要走hash连接。结合上面的分析所以此处执行计划我认为是没有问题的。
id3 与 id4
这里大家看不到索引对应的表这个 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、....... LA10IMO表也会根据连接列进行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 ALLSELECT
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 ALLSELECT
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.IDIMO.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_ID080 AND IMO.STATUS_ID085)) 优化总结
/* parallel(n) use_hash(LA,IMO) pq_distribute(IMO hash,hash)*/
parallel(n)中的这个n并不是越大就越好。
这种并行查询方法只会在表连接查询消耗PGA大小合适的时候才能发挥最大作用
它其实对于几十G这种远远超过PGA大小的表连接时就不好使了对于这种情况还有其他优化方法。 这个SQL应该还有其他优化方法毕竟我只优化到了1.5s左右。 但我现在还是太愚笨了只能想到这个方法优化。 如果后续我找到其他优化方法会再和大家分享。 谢谢您的阅读
