2012 年，一朋友发来信息说以下 SQL 要跑 5 个小时，请求优化。
SELECTB.AREA_ID,A.PARTY_ID,B.AREA_NAME,C.NAME CHANNEL_NAME,B.NAME PARTY_NAME,B.ACCESS_NUMBER,B.PROD_SPEC,B.START_DT,A.BO_ACTION_NAME,A.SO_STAFF_ID,A.ATOM_ACTION_ID,A.PROD_IDFROM DW_CHANNEL C,DW_CRM_DAY_USER B,DW_BO_ORDER AWHERE A.PROD_ID = B.PROD_ID ANDA.CHANNEL_ID = C.CHANNEL_ID ANDA.SO_STAFF_ID LIKE '36%' ANDA.BO_ACTION_NAME IN ('新装'，'移机','资费变更') ANDB.PROD_SPEC IN ('普通电话', 'ADSL','LAN', '手机','E8 - 2S'，'E6 移动版'， 'E9 版 1M（老版）'，'普通 E9'，'普通新版 E8'，'全省_紧密融合型 E9 套餐产品规格'，'（新） 全省_紧密融合型 E9 套餐产品规格'，'新春欢乐送之 E8 套餐','新春欢乐送之 E6 套餐') ANDNOT EXISTS (SELECT *FROM DW_BO_ORDER DWHERE D.STAFF_ID LIKE '36%' ANDA.PARTY_ID = D.PARTY_ID ANDA.BO_ID != D.BO_ID ANDA.PROD_ID != D.PROD_ID ANDA.BO_ACTION_NAME IN('新装'， '移机'，'资费变更') ANDA.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT);
执行计划如下。
Plan hash value: 2142862569-------------------------------------------------------------------------------------| Id |Operation |Name | Rows | Bytes | Cost （%CPU）| Time |-------------------------------------------------------------------------------------| 0 |SELECT STATEMENT | | 905 | 121K| 4152K (2)| 13:50:32 ||* 1 | FILTER | | | | | ||* 2 | HASH JOIN | | 905 | 121K| 12616 (2)| 00:02:32 ||* 3 | HASH JOIN | | 905 | 99550 | 12448 (2)| 00:02:30 || 4 | PARTITION RANGE ALL| | 1979 | 108K| 9168 (2)| 00:01:51 ||* 5 | TABLE ACCESS FULL|DW_BO_ORDER | 1979 | 108K| 9168 (2)| 00:01:51 ||* 6 | TABLE ACCESS FULL |DW_CRM_DAY_USER| 309K| 15M| 3277 (2)| 00:00:40 || 7 | TABLE ACCESS FULL |DW_CHANNEL | 48425 | 1276K| 168 (1)| 00:00:03 ||* 8 | FILTER | | | | | || 9 | PARTITION RANGE ALL| | 1 | 29 | 9147 (2)| 00:01:50 ||*10 | TABLE ACCESS FULL |DW_BO_ORDER | 1 | 29 | 9147 (2)| 00:01:50 |-------------------------------------------------------------------------------------Predicate Information （identified by operation id）:---------------------------------------------------1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM 「DW_BO_ORDER」 「D」 WHERE （:B1='新装' OR :B2='移机'OR :B3='资费变更'） AND 「D」.「PARTY_ID」=:B4 AND TO_CHAR（「D」.「STAFF_ID」） LIKE'36%' AND 「D」.「COMPLETE_DT」>:B5-INTERVAL'+07 00:00:00' DAY(2) TO SECOND(0) AND「D」.「PROD_ID」<>:B6 AND 「D」.「BO_ID」<>:B7))2 - access（「A」.「CHANNEL_ID」=「C」.「CHANNEL_ID」）3 - access（「A」.「PROD_ID」=「B」.「PROD_ID」）5 - filter(「A」.「PROD_ID」 IS NOT NULL AND （「A」.「BO_ACTION_NAME」='新装' OR「A」.「BO_ACTION_NAME」='移机' OR 「A」.「BO_ACTION_NAME」='资费变更'） ANDTO_CHAR（「A」.「SO_STAFF_ID」） LIKE 』36%『)6 - filter(「B」.「PROD_SPEC」='（新） 全省_紧密融合型 E9 套餐产品规格' OR 「B」.「PROD_SPEC」 ='ADSL'OR 「B」.「PROD_SPEC」='E6 移动版' OR 「B」.「PROD_SPEC」='E8 - 2S' OR「B」.「PROD_SPEC」='E9 版 1M（老版）' OR 「B」.「PROD_SPEC」='LAN' OR 「B」. 「PROD_SPEC」='普通 E9' OR 「B」.「PROD_SPEC」='普通电话' OR 「B」.「PROD_SPEC」='普通新版 E8' OR「B」.「PROD_SPEC」='全省_紧密融合型 E9 套餐产品规格' OR 「B」.「PROD_SPEC」='手机' OR「B」.「PROD_SPEC」='新春欢乐送之 E6 套餐' OR 「B」.「PROD_SPEC」='新春欢乐送之 E8 套餐')8 - filter（:B1='新装' OR :B2='移机' OR :B3='资费变更'）10 - filter(「D」.「PARTY_ID」=:B1 AND TO_CHAR（「D」.「STAFF_ID」） LIKE '36%' AND「D」.「COMPLETE_DT」>:B2-INTERVAL'+07 00:00:00' DAY(2) TO SECOND(0) AND「D」.「PROD_ID」<>:B3 AND 「D」.「BO_ID」<>:B4)
优化 SQL，必须看表大小，表大小信息如下。
select count(*) from dw_bo_order； ----200 万行数据
COUNT(*)----------2282548
select count(*) from dw_crm_day_user； ----40 万行数据
COUNT(*)----------420918
select count(*) from dw_channel； ---4 万行数据
COUNT(*)----------48031
SQL 语句中最大表 DW_BO_ORDER 才 200 万行数据，但是 SQL 执行了 5 个多小时，显然执行计划有问题。执行计划中，Id=1 是 Filter，而且 Filter 对应的谓词信息有 EXISTS（子查询:B1），这说明该 Filter 类似嵌套循环。Id=2 和 Id=8 是 Id=1 的儿子，因为这里的 Filter 类似嵌套循环，Id=2 就相当于 NL 驱动表，Id=8 相当于 NL 被驱动表，Id=8 是全表扫描过滤后的数据，所以 Id=8 可以看作全表扫描。本书反复强调过，NL 被驱动表必须走索引。但是 Id=10 并没有走索引。Id=2 估算返回 905 行数据，一般情况下 Rows 会算少，这里就暂且认为 Id=2 返回 905 行数据，那么 Id=8 会被扫描 905 次，也就是说 DW_BO_ORDER 这个 200 万行大表会被扫描 905 次，而且每次都是全表扫描，这就是为什么 SQL 会执行 5 个多小时。
找到 SQL 的性能瓶颈之后，我们就可以想办法优化 SQL。本案例有两种优化思路，其一是让大表只被扫描一次，其二是不减少扫描次数，但是减少大表每次被扫描的体积。最优的解决方案是，想办法让 Id=2 和 Id=8 走 HASH 连接消除 Filter，这样就只需要扫描 1 次大表，因为当时数据库版本是 Oracle10g，where 子查询中有主表的过滤条件，在 not exists 子查询中添加 HINT：HASH_AJ 无法更改执行计划。我们可以将 not exists 改写为「外连接 + 子表连接列 is null」的形式，让其走 HASH 连接，但是当时没有采用这种改写方式。因为大表要被扫描 905 次，每次都是全表扫描，如果能减少扫描的体积，也能优化 SQL。我们可以在大表上建立一个组合索引，这样就能避免大表每次全表扫描，从而达到减少扫描体积的目的，但是当时朋友没权限建立索引。最终选择使用 with as 子句优化上述 SQL。
set timi onWITH D AS(SELECT /*+ materialize */PARTY_ID,BO_ID,PROD_ID,COMPLETE_DTFROM DW_BO_ORDERWHERE STAFF_ID LIKE '36%' ANDBO_ACTION_NAME IN ('新装'，'移机'，'资费变更'))SELECTB.AREA_ID,A.PARTY_ID,B.AREA_NAME,C.NAME CHANNEL_NAME,B.NAME PARTY_NAME,B.ACCESS_NUMBER,B.PROD_SPEC,B.START_DT,A.BO_ACTION_NAME,A.SO_STAFF_ID,A.ATOM_ACTION_ID,A.PROD_IDFROM DW_CHANNEL C,DW_CRM_DAY_USER B,DW_BO_ORDER AWHERE A.PROD_ID = B.PROD_ID ANDA.CHANNEL_ID = C.CHANNEL_ID ANDA.SO_STAFF_ID LIKE '36%' ANDA.BO_ACTION_NAME IN ('新装'，'移机'，'资费变更') ANDB.PROD_SPEC IN ('普通电话', 'ADSL'，'LAN', '手机','E8 - 2S','E6 移动版', 'E9 版 1M（老版）','普通 E9'，'普通新版 E8','全省_紧密融合型 E9 套餐产品规格'，'（新） 全省_紧密融合型 E9 套餐产品规格'，'新春欢乐送之 E8 套餐','新春欢乐送之 E6 套餐') ANDNOT EXISTS (SELECT *FROM DWHERE A.PARTY_ID = D.PARTY_ID ANDA.BO_ID != D.BO_ID ANDA.PROD_ID != D.PROD_ID ANDA.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT);
已选择 49 245 行。
已用时间：00: 00: 12.37。
执行计划如下。
-------------------------------------------------------------------------------------Plan hash value: 2591883460-------------------------------------------------------------------------------------| Id |Operation |Name |Rows |Bytes| Cost（%CPU）|-------------------------------------------------------------------------------------| 0 |SELECT STATEMENT | | 905| 121K| 62428 (2)|| 1 | TEMP TABLE TRANSFORMATION| | | | || 2 | LOAD AS SELECT |DW_BO_ORDER | | | || 3 | PARTITION RANGE ALL | | 114K|3228K| 9127 (2)||* 4 | TABLE ACCESS FULL |DW_BO_ORDER | 114K|3228K| 9127 (2)||* 5 | FILTER | | | | ||* 6 | HASH JOIN | | 905| 121K| 12616 (2)||* 7 | HASH JOIN | | 905|99550| 12448 (2)|| 8 | PARTITION RANGE ALL | | 1979| 108K| 9168 (2)||* 9 | TABLE ACCESS FULL |DW_BO_ORDER | 1979| 108K| 9168 (2)||*10 | TABLE ACCESS FULL |DW_CRM_DAY_USER | 309K| 15M| 3277 (2)|| 11 | TABLE ACCESS FULL |DW_CHANNEL |48425|1276K| 168 (1)||*12 | FILTER | | | | ||*13 | VIEW | | 114K|6791K| 90 (3)|| 14 | TABLE ACCESS FULL |SYS_TEMP_0FD9D662E_D625B872| 114K|3228K| 90 (3)|-------------------------------------------------------------------------------------Predicate Information （identified by operation id）:---------------------------------------------------4 - filter(TO_CHAR（「STAFF_ID」） LIKE 『36%』)5 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM (SELECT /*+ CACHE_TEMP_TABLE （「T1」） */ 「C0」「STAFF_ID」，「C1」 「PARTY_ID」，「C2」 「BO_ID」，「C3」 「PROD_ID」，「C4」 「COMPLETE_DT」 FROM「SYS」.「SYS_TEMP_0FD9D662E_D625B872」 「T1」) 「D」 WHERE （:B1=『新装』 OR :B2=『移机』 OR :B3=『资费变更』） AND TO_CHAR（「D」.「STAFF_ID」） LIKE 『36%』 AND 「D」.「PARTY_ID」=:B4AND「D」.「BO_ID」<>:B5 AND 「D」.「PROD_ID」<>:B6 AND 「D」.「COMPLETE_DT」>:B7-INTERVAL'+0700:00:00' DAY(2) TO SECOND(0)))6 - access（「A」.「CHANNEL_ID」=「C」.「CHANNEL_ID」）7 - access（「A」.「PROD_ID」=「B」.「PROD_ID」）9 - filter(「A」.「PROD_ID」 IS NOT NULL AND （「A」.「BO_ACTION_NAME」=』新装『 OR 「A」.「BO_ ACTION_NAME」=』移机『 OR 「A」.「BO_ACTION_NAME」=』资费变更『） AND TO_CHAR（「A」.「SO_STAFF_ID」） LIKE 』36%『)10 - filter(「B」.「PROD_SPEC」=』（新） 全省_紧密融合型 E9 套餐产品规格『 OR 「B」.「PROD_SPEC」= 』ADSL『 OR「B」.「PROD_SPEC」='E6 移动版' OR 「B」.「PROD_SPEC」='E8 - 2S' OR 「B」.「PROD_ SPEC」='E9 版1M（老版）' OR 「B」.「PROD_SPEC」='LAN' OR 「B」.「PROD_SPEC」=』普通 E9『 OR 「B」. 「PROD_SPEC」=』普通电话『 OR 「B」.「PROD_SPEC」=』普通新版 E8『 OR 「B」.「PROD_SPEC」=』全省_紧密融合型 E9 套餐产品规格『 OR 「B」.「PROD_SPEC」=』手机『 OR 「B」.「PROD_SPEC」=』新春欢乐送之 E6 套餐『 OR「B」.「PROD_SPEC」=』新春欢乐送之 E8 套餐『)12 - filter（:B1=』新装『 OR :B2=』移机『 OR :B3=』资费变更『）13 - filter(TO_CHAR（「D」.「STAFF_ID」） LIKE 』36%『 AND 「D」.「PARTY_ID」=:B1 AND 「D」.「BO_ID」<>:B2 AND「D」.「PROD_ID」<>:B3 AND 「D」.「COMPLETE_DT」>:B4-INTERVAL'+07 00:00:00' DAY(2) TOSECOND(0))统计信息----------------------------------------------------------2 recursive calls29 db block gets110506 consistent gets22 physical reads656 redo size2438096 bytes sent via SQL*Net to client449 bytes received via SQL*Net from client11 SQL*Net roundtrips to/from client0 sorts （memory）0 sorts （disk）49245 rows processed
使用 with as 子句将大表要被访问的字段查询出来，一共 4 个字段，然后过滤掉不需要的数据，添加 HINT:**MATERIALIZE** 将 with as 子句查询结果固化为临时表，这样就达到了减少扫描体积的目的。假设 200 万行的大表 DW_BO_ORDER 有占用 2GB 存储空间，表有 40 个字段，通过 with as 子句改写之后，只需要存储 4 个字段数据，这时只需 200MB 存储空间，而且 with as 子句中还有过滤条件，又可以过滤掉一部分数据，这时 with as 子句可能就只需要几十兆存储空间。虽然被扫描的次数没有改变，但是每次被扫描的体积大大减少，这样就解决了 SQL 查询性能。最终 SQL 可以在 12 秒左右跑完，一共返回 4.9 万行数据。
若有收获，就点个赞吧
0 人点赞
