查看: 4340|回复: 3

[性能调整] 一个SQL性能优化分享---利用with as 优化SQL

[复制链接]
论坛徽章:
5
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51数据库板块每日发贴之星
日期:2011-07-22 01:01:02蜘蛛蛋
日期:2011-08-24 14:10:13ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042012新春纪念徽章
日期:2012-01-04 11:54:26
发表于 2011-11-10 23:11 | 显示全部楼层 |阅读模式
马上就要单身节了,正在想今年我去祸害谁家的姑娘,突然QQ好友发来信息,说能否帮忙优化一个SQL,SQL调优做得实在太多了,都已经麻木了,反正优化一个SQL也就几秒钟到几分钟的事情。
哥们说下面的SQL要跑5个多小时
SELECT
               B.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_ID
        FROM   DW_CHANNEL      C,
               DW_CRM_DAY_USER B,
               DW_BO_ORDER     A
        WHERE  A.PROD_ID = B.PROD_ID AND
               A.CHANNEL_ID = C.CHANNEL_ID AND
               A.SO_STAFF_ID LIKE '36%' AND
               A.BO_ACTION_NAME IN ('新装','移机','资费变更') AND
               B.PROD_SPEC IN ('普通电话', 'ADSL','LAN', '手机',
                               'E8 - 2S','E6移动版', 'E9版1M(老版)',
                               '普通E9','普通新版E8',
                               '全省_紧密融合型E9套餐产品规格',
                               '(新) 全省_紧密融合型E9套餐产品规格',
                               '新春欢乐送之E8套餐',
                               '新春欢乐送之E6套餐') AND
              NOT  EXISTS (SELECT  *
                FROM   DW_BO_ORDER D
                WHERE  D.STAFF_ID LIKE '36%' AND
                       A.PARTY_ID = D.PARTY_ID AND
                       A.BO_ID != D.BO_ID AND
                       A.PROD_ID != D.PROD_ID AND
                       A.BO_ACTION_NAME IN
                       ('新装', '移机','资费变更') AND
                       A.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT);
下面是执行计划以及表信息
SQL> select count(*) from dw_bo_order;

  COUNT(*)
----------
   2282548

SQL> select count(*) from dw_crm_day_user;

  COUNT(*)
----------
    420918

SQL> select count(*) from dw_channel;

  COUNT(*)
----------
     48031
     
Plan hash value: 2142862569

----------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   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 |     1 |     5 |
|*  5 |      TABLE ACCESS FULL | DW_BO_ORDER     |  1979 |   108K|  9168   (2)| 00:01:51 |     1 |     5 |
|*  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 |     1 |     5 |
|* 10 |     TABLE ACCESS FULL  | DW_BO_ORDER     |     1 |    29 |  9147   (2)| 00:01:50 |     1 |     5 |
----------------------------------------------------------------------------------------------------------

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"='资费变更') AND TO_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性能问题出在这里
NOT  EXISTS (SELECT  *
                FROM   DW_BO_ORDER D
                WHERE  D.STAFF_ID LIKE '36%' AND
                       A.PARTY_ID = D.PARTY_ID AND
                       A.BO_ID != D.BO_ID AND
                       A.PROD_ID != D.PROD_ID AND
                       A.BO_ACTION_NAME IN
                       ('新装', '移机','资费变更') AND
                       A.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT);
你一定要注意看,前面的NOT EXISTS 里面套了 2个 !=  尼玛,坑爹啊,神马业务逻辑啊,这个SQL太坑爹了,由于有!=的存在,CBO不能选择 HASH_AJ join的方式,只能走FILTER,哈哈,走FILTER绝对搞死人,不是吗?因为它要反复扫描 DW_BO_ORDER 非常多次,那么我建议那哥们把SQL改了,把里面的!=拆分,不过可惜的是,不管他怎么拆分,SQL业务逻辑总是不对,尼玛谁叫我们写SQL水平菜呢(自我批评一下)
于是建议他用下面的方法改写SQL
with D as (select  /*+ materialize */  PARTY_ID,BO_ID,PROD_ID from DW_BO_ORDER where STAFF_ID LIKE '36%')         
SELECT
               B.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_ID
        FROM   DW_CHANNEL      C,
               DW_CRM_DAY_USER B,
               DW_BO_ORDER     A
        WHERE  A.PROD_ID = B.PROD_ID AND
               A.CHANNEL_ID = C.CHANNEL_ID AND
               A.SO_STAFF_ID LIKE '36%' AND
               A.BO_ACTION_NAME IN ('新装','移机','资费变更') AND
               B.PROD_SPEC IN ('普通电话', 'ADSL','LAN', '手机',
                               'E8 - 2S','E6移动版', 'E9版1M(老版)',
                               '普通E9','普通新版E8',
                               '全省_紧密融合型E9套餐产品规格',
                               '(新) 全省_紧密融合型E9套餐产品规格',
                               '新春欢乐送之E8套餐',
                               '新春欢乐送之E6套餐') AND
              NOT  EXISTS (SELECT  *
                FROM  D
                WHERE  D.STAFF_ID LIKE '36%' AND
                       A.PARTY_ID = D.PARTY_ID AND
                       A.BO_ID != D.BO_ID AND
                       A.PROD_ID != D.PROD_ID AND
                       A.BO_ACTION_NAME IN
                       ('新装', '移机','资费变更') AND
                       A.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT);
执行计划和SQL执行时间如下:
SQL> set timi on
SQL> WITH D AS
  2   (SELECT /*+ materialize */
  3     PARTY_ID,
  4     BO_ID,
  5     PROD_ID,
  6     COMPLETE_DT
  7    FROM   DW_BO_ORDER
  8    WHERE  STAFF_ID LIKE '36%' AND
  9           BO_ACTION_NAME IN ('新装',
10                                '移机',
11                                '资费变更'))
12  SELECT
13                 B.AREA_ID,
14                 A.PARTY_ID,
15                 B.AREA_NAME,
16                 C.NAME           CHANNEL_NAME,
17                 B.NAME           PARTY_NAME,
18                 B.ACCESS_NUMBER,
19                 B.PROD_SPEC,
20                 B.START_DT,
21                 A.BO_ACTION_NAME,
22                 A.SO_STAFF_ID,
23                 A.ATOM_ACTION_ID,
24                 A.PROD_ID
25          FROM   DW_CHANNEL      C,
26                 DW_CRM_DAY_USER B,
27                 DW_BO_ORDER     A
28          WHERE  A.PROD_ID = B.PROD_ID AND
29                 A.CHANNEL_ID = C.CHANNEL_ID AND
30                 A.SO_STAFF_ID LIKE '36%' AND
31                 A.BO_ACTION_NAME IN ('新装','移机','资费变更') AND
32                 B.PROD_SPEC IN ('普通电话', 'ADSL','LAN', '手机',
33                                 'E8 - 2S','E6移动版', 'E9版1M(老版)',
34                                 '普通E9','普通新版E8',
35                                 '全省_紧密融合型E9套餐产品规格',
36                                 '(新) 全省_紧密融合型E9套餐产品规格',
37                                 '新春欢乐送之E8套餐',
38                                 '新春欢乐送之E6套餐') AND
39                NOT  EXISTS (SELECT  *
40                  FROM  D
41                  WHERE  A.PARTY_ID = D.PARTY_ID AND
42                         A.BO_ID != D.BO_ID AND
43                         A.PROD_ID != D.PROD_ID AND
44                         A.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT);
已选择49245行。

已用时间:  00: 00: 12.37
执行计划
--------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2591883460

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |   905 |   121K| 62428   (2)| 00:12:30 |       |       |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |       |       |
|   2 |   LOAD AS SELECT           | DW_BO_ORDER                 |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ALL     |                             |   114K|  3228K|  9127   (2)| 00:01:50 |     1 |     5 |
|*  4 |     TABLE ACCESS FULL      | DW_BO_ORDER                 |   114K|  3228K|  9127   (2)| 00:01:50 |     1 |     5 |
|*  5 |   FILTER                   |                             |       |       |            |          |       |       |
|*  6 |    HASH JOIN               |                             |   905 |   121K| 12616   (2)| 00:02:32 |       |       |
|*  7 |     HASH JOIN              |                             |   905 | 99550 | 12448   (2)| 00:02:30 |       |       |
|   8 |      PARTITION RANGE ALL   |                             |  1979 |   108K|  9168   (2)| 00:01:51 |     1 |     5 |
|*  9 |       TABLE ACCESS FULL    | DW_BO_ORDER                 |  1979 |   108K|  9168   (2)| 00:01:51 |     1 |     5 |
|* 10 |      TABLE ACCESS FULL     | DW_CRM_DAY_USER             |   309K|    15M|  3277   (2)| 00:00:40 |       |       |
|  11 |     TABLE ACCESS FULL      | DW_CHANNEL                  | 48425 |  1276K|   168   (1)| 00:00:03 |       |       |
|* 12 |    FILTER                  |                             |       |       |            |          |       |       |
|* 13 |     VIEW                   |                             |   114K|  6791K|    90   (3)| 00:00:02 |       |       |
|  14 |      TABLE ACCESS FULL     | SYS_TEMP_0FD9D662E_D625B872 |   114K|  3228K|    90   (3)| 00:00:02 |       |       |
--------------------------------------------------------------------------------------------------------------------------

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"=:B4 AND
              "D"."BO_ID"<>:B5 AND "D"."PROD_ID"<>:B6 AND "D"."COMPLETE_DT">:B7-INTERVAL'+07 00: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) TO SECOND(0))   

统计信息
----------------------------------------------------------
          2  recursive calls
         29  db block gets
     110506  consistent gets
         22  physical reads
        656  redo size
    2438096  bytes sent via SQL*Net to client
        449  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49245  rows processed
现在SQL 12秒可以跑完了,这个SQL优化到这里就行了,不能连接他的DB,我业务逻辑也不清楚,奶奶的,神马时候帮别人优化一个SQL 一行一元。
更多的SQL优化案例 http://blog.csdn.net/robinson1988/article/category/621201 欢迎点评
论坛徽章:
57
SQL极客
日期:2013-12-09 14:13:35秀才
日期:2016-01-21 13:42:39秀才
日期:2016-01-13 12:14:26SQL大赛参与纪念
日期:2016-01-13 10:32:19SQL数据库编程大师
日期:2016-01-13 10:30:43秀才
日期:2015-12-14 14:47:54秀才
日期:2015-10-19 15:50:392015年新春福章
日期:2015-03-06 11:58:18懒羊羊
日期:2015-03-04 14:52:11优秀写手
日期:2014-11-08 06:00:14
发表于 2011-11-11 09:33 | 显示全部楼层
mark一下,周末好好研究一下。

使用道具 举报

回复
认证徽章
论坛徽章:
1
弗兰奇
日期:2018-10-31 15:32:03
发表于 2016-2-29 12:43 | 显示全部楼层
mark ths

使用道具 举报

回复
认证徽章
论坛徽章:
1
ITPUB15周年纪念
日期:2019-07-01 11:29:26
发表于 2019-6-30 10:20 | 显示全部楼层
感谢分享

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表