一个徒弟帮另外一个徒弟优化的案例

来源：互联网发布：凸优化 matlab 编辑：程序博客网时间：2024/06/10 18:40

2015 -03-13 更新

overflash 成功的斩获年薪 18w 的岗位，他是 90后！跟我学了大概6个多月吧今年23岁前途无量啊

太搞笑了

overflash  15:26:19
哈哈罗总今天帮群里哥们优化了一个案例，虽然说简单，但是神奇的是我尽然读懂执行计划了，然后定位到问题了    就是两个大表全表扫描走hash的问题
overflash  15:26:28
嘿嘿看来没白学啊
落落  15:27:00
...擦
落落  15:27:02
哈哈
落落  15:27:08
介绍过来学一下
落落  15:27:10
给推进非
落落  15:27:13
费
overflash  15:27:12
嘿嘿  学费没白交
落落  15:27:15

overflash  15:27:22
。。就咱们群里的
落落  15:27:29
哦
落落  15:27:31
2群？
overflash  15:27:35
比较激动
overflash  15:27:36
恩
overflash  15:27:42
第一次帮人优化啊
overflash  15:28:01
开了个并行 50分钟优化到4分钟。
overflash  15:28:09

落落  15:28:21
哦是谁啊
overflash  15:28:43
迷恋橙子
overflash  15:29:43
以前我看执行计划感觉云里雾里的今天用脱衣服大法+执行计划  就定位问题了嘿嘿
overflash  15:45:22
迷恋橙子是抢银行？
落落  15:45:41
恩
overflash  15:45:47
恩恩~就是他
overflash  15:49:21
让抢银行整理吧。。。。我这边也没办法跑执行计划。。。
落落  15:50:24
好吧
overflash  15:50:44
要不我先弄一版
overflash  16:05:43
罗总我整理好了

sql跑了50分钟出结果，sql和执行计划如下：--------------------------------------------------------------- SQL  BEGIN------------------------------------------------------------------SELECT AA.* FROM (SELECT        A.CLCT_DAY 时间,       A.SUBSCRIBE_ID 订单号,       A.SERIAL_NUMBER 手机号,       DECODE(ASSURE_TYPE,'02','保证金担保','04','无担保','05','社保卡担保','06','单位担保','07','银行信贷担保','08','银行保险担保','99','固网免预存担保','10','专业担保公司担保','11','集客专用银行存款担保（三方协议）','12','基于新险种的保险公司担保','省份担保类型') 担保方式,       nvl(A.RES_DESC, '-') 终端品牌,       nvl(A.MACHINE_TYPE_CODE, '-') 终端型号,       nvl(C.PRODUCT_NAME, '-') 套餐名称,       nvl(B.VALUEN1, 0) 套餐月费,       DECODE(A.ACTIVITY_PROTPER, '', '-', ACTIVITY_PROTPER) 协议期限,       NVL(a.storedfee, 0)预存款,       nvl(B.VALUEN2, 0) 入网返还,       nvl(B.VALUEN3, 0) 分月返还,       nvl(B.VALUEN4, 0) 押金,       nvl(TML_FEE, 0) 终端款,       nvl(VALUEN5, 0) 手机款公允法,       nvl(SALE_PRICE, 0) 零售价,       nvl(A.PURCHASE_PRICE, 0) 成本价,       nvl(B.VALUEN6, 0) 手机销售亏损剩余法,       nvl(B.VALUEN7, 0) 手机销售亏损公允法,       nvl(B.VALUEN8, 0) 当期销售亏损减少额,       nvl(B.VALUEN9, 0) 公允价值法后每月分摊金额,       B.VALUEN8-(B.VALUEN9*(MONTHS_BETWEEN(TO_DATE(SUBSTR(20141231, 1, 6),                                      'YYYYMM'),                              TO_DATE(SUBSTR(A.CLCT_DAY, 1, 6), 'YYYYMM')))) 截止20141231未摊销金额,        MONTHS_BETWEEN(TO_DATE(SUBSTR(20141231, 1, 6),                                      'YYYYMM'),                              TO_DATE(SUBSTR(A.CLCT_DAY, 1, 6), 'YYYYMM')) 截止20141231已摊销月份                      FROM TS_MX_SUBSCRIBE A,TS_UH_TRADE_FINANCE B,(SELECT DISTINCT C.PRODUCT_CODE,C.PRODUCT_NAME FROM TF_P_PRODUCT C) CWHERE A.CLCT_DAY >= '20140101'  AND A.CLCT_DAY <= '20141231'  AND B.CLCT_DAY >= '20140101'  AND B.CLCT_DAY <= '20141231'  AND A.SUBSCRIBE_ID = B.TRADE_ID  AND A.PRODUCT_CODE = C.PRODUCT_CODE  and A.ASSURE_TYPE in ('10', '11', '12', '06', '99', '09', '08', '02')  AND B.ACTIVITY_TYPE = '4'  AND A.CANCEL_ID IS NULL UNION ALL--N6SELECT        A.CLCT_DAY 时间,       A.SUBSCRIBE_ID 订单号,       A.SERIAL_NUMBER 手机号,       DECODE(ASSURE_TYPE,'02','保证金担保','04','无担保','05','社保卡担保','06','单位担保','07','银行信贷担保','08','银行保险担保','99','固网免预存担保','10','专业担保公司担保','11','集客专用银行存款担保（三方协议）','12','基于新险种的保险公司担保','省份担保类型') 担保方式,       nvl(A.RES_DESC, '-') 终端品牌,       nvl(A.MACHINE_TYPE_CODE, '-') 终端型号,       nvl(C.PRODUCT_NAME, '-') 套餐名称,       nvl(B.VALUEN1, 0) 套餐月费,       DECODE(A.ACTIVITY_PROTPER, '', '-', ACTIVITY_PROTPER) 协议期限,       NVL(a.storedfee, 0) 预存款,       nvl(B.VALUEN2, 0) 入网返还,       nvl(B.VALUEN3, 0) 分月返还,       nvl(B.VALUEN4, 0) 押金,       nvl(TML_FEE, 0) 终端款,       nvl(VALUEN5, 0) 手机款公允法,       nvl(SALE_PRICE, 0) 零售价,       nvl(A.PURCHASE_PRICE, 0) 成本价,       nvl(B.VALUEN6, 0) 手机销售亏损剩余法,       nvl(B.VALUEN7, 0) 手机销售亏损公允法,       nvl(B.VALUEN8, 0) 当期销售亏损减少额,       nvl(B.VALUEN9, 0) 公允价值法后每月分摊金额,       B.VALUEN8-(B.VALUEN9*(MONTHS_BETWEEN(TO_DATE(SUBSTR(20141231, 1, 6),                                      'YYYYMM'),                              TO_DATE(SUBSTR(B.CLCT_DAY, 1, 6), 'YYYYMM')))) 截止20141231未摊销金额,       MONTHS_BETWEEN(TO_DATE(SUBSTR(20141231, 1, 6),                                      'YYYYMM'),                              TO_DATE(SUBSTR(A.CLCT_DAY, 1, 6), 'YYYYMM')) 截止20141231已摊销月份  FROM TS_MX_SUBSCRIBE A,TS_UH_TRADE_FINANCE B,(SELECT DISTINCT C.PRODUCT_CODE,C.PRODUCT_NAME FROM TF_P_PRODUCT C) CWHERE A.CLCT_DAY >= '20140101'  AND A.CLCT_DAY <= '20141231'  AND B.CLCT_DAY >= '20140101'  AND B.CLCT_DAY <= '20141231'  AND A.SUBSCRIBE_ID = SUBSTR(B.TRADE_ID,3,16)  AND A.PRODUCT_CODE = C.PRODUCT_CODE  and A.ASSURE_TYPE in ('10', '11', '12', '06', '99', '09', '08', '02')  AND B.ACTIVITY_TYPE = '4'  AND A.CANCEL_ID IS NULL) AA,(SELECT SUBSCRIBE_ID FROM UCR_STA_CEN.TS_R_TRADE_102) B  --where  not exists (SELECT 1 FROM TS_R_TRADE_102 where aa.AA.订单号=SUBSCRIBE_ID)  --where AA.订单号 not in(SELECT SUBSCRIBE_ID FROM TS_R_TRADE_102)  WHERE AA.订单号 = B.SUBSCRIBE_ID(+)    and B.SUBSCRIBE_ID IS NULL;--------------------------------------------------------------- SQL  END--------------------------------------------------------------------------------------------------------------------------------- 执行计划------------------------------------------------------------------SQL> SELECT * FROM TABLE(DBMS_XPLAN.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 2061934862--------------------------------------------------------------------------------| Id  | Operation                        | Name                 | Rows  | Bytes--------------------------------------------------------------------------------|   0 | SELECT STATEMENT                 |                      |     1 |   381|   1 |  PX COORDINATOR                  |                      |       ||   2 |   PX SEND QC (RANDOM)            | :TQ10002             |     1 |   381|*  3 |    FILTER                        |                      |       ||*  4 |     HASH JOIN RIGHT OUTER        |                      |     1 |   381|   5 |      PX RECEIVE                  |                      |    20M|   193M|   6 |       PX SEND HASH               | :TQ10001             |    20M|   193M|   7 |        PX BLOCK ITERATOR         |                      |    20M|   193M|   8 |         INDEX FAST FULL SCAN     | IDX_TS_R_TRADE_102_2 |    20M|   193M|   9 |      BUFFER SORT                 |                      |       ||  10 |       PX RECEIVE                 |                      |    12M|  4284M|  11 |        PX SEND HASH              | :TQ10000             |    12M|  4284M|  12 |         VIEW                     |                      |    12M|  4284M|  13 |          UNION-ALL               |                      |       ||* 14 |           HASH JOIN              |                      |    11M|  2071M|  15 |            VIEW                  |                      | 15728 |   983K|  16 |             HASH UNIQUE          |                      | 15728 |   629K|  17 |              INDEX FAST FULL SCAN| IDX_TF_P_PRODUCT     | 15728 |   629K|* 18 |            HASH JOIN             |                      |  2704K|   314M|  19 |             PARTITION RANGE ALL  |                      |  2704K|   159M|* 20 |              TABLE ACCESS FULL   | TS_MX_SUBSCRIBE      |  2704K|   159M|* 21 |             TABLE ACCESS FULL    | TS_UH_TRADE_FINANCE  |  5083K|   290M|* 22 |           HASH JOIN              |                      |   427K|    75M|  23 |            VIEW                  |                      | 15728 |   983K|  24 |             HASH UNIQUE          |                      | 15728 |   629K|  25 |              INDEX FAST FULL SCAN| IDX_TF_P_PRODUCT     | 15728 |   629K|* 26 |            HASH JOIN             |                      | 99053 |    11M|  27 |             PARTITION RANGE ALL  |                      |  2704K|   159M|* 28 |              TABLE ACCESS FULL   | TS_MX_SUBSCRIBE      |  2704K|   159M|* 29 |             TABLE ACCESS FULL    | TS_UH_TRADE_FINANCE  |  5083K|   290M--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - filter("SUBSCRIBE_ID" IS NULL)PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------   4 - access("AA"."订单号"="SUBSCRIBE_ID"(+))  14 - access("A"."PRODUCT_CODE"="C"."PRODUCT_CODE")  18 - access("A"."SUBSCRIBE_ID"="B"."TRADE_ID")  20 - filter("A"."CLCT_DAY">='20140101' AND "A"."CANCEL_ID" IS NULL AND ("A"."A              OR "A"."ASSURE_TYPE"='09' OR "A"."ASSURE_TYPE"='10' OR "A"."ASSURE              "A"."CLCT_DAY"<='20141231')  21 - filter("B"."CLCT_DAY">='20140101' AND "B"."ACTIVITY_TYPE"='4' AND "B"."CL  22 - access("A"."PRODUCT_CODE"="C"."PRODUCT_CODE")  26 - access("A"."SUBSCRIBE_ID"=TO_NUMBER(SUBSTR(TO_CHAR("B"."TRADE_ID"),3,16))  28 - filter("A"."CLCT_DAY">='20140101' AND "A"."CANCEL_ID" IS NULL AND ("A"."A              OR "A"."ASSURE_TYPE"='09' OR "A"."ASSURE_TYPE"='10' OR "A"."ASSURE              "A"."CLCT_DAY"<='20141231')  29 - filter("B"."CLCT_DAY">='20140101' AND "B"."ACTIVITY_TYPE"='4' AND "B"."CL54 rows selected--------------------------------------------------------------- 执行计划------------------------------------------------------------------AA和B关联，AA和B返回结果很大，开并行HASH后依然很慢，所以定位内敛视图AA上，结果AA执行速度确实很慢用罗总牛逼犀利威武淫荡的脱衣服大法将AA扒衣服后，就剩TS_MX_SUBSCRIBE A,TS_UH_TRADE_FINANCE B,(SELECT DISTINCT C.PRODUCT_CODE,C.PRODUCT_NAME FROM TF_P_PRODUCT C) C 三个TS_MX_SUBSCRIBE  ,TS_UH_TRADE_FINANCE 过滤后都是150w条数据， TF_P_PRODUCT 数据少，但是和其他表是1:N关系，所以三个表走HASH但是执行计划里面20 12 和28 29 都是两个超级大表HASH，并且是全表扫描，这严重影响SQL效率这时候，应用罗总牛逼犀利威武淫荡的优化技巧：两个超级大表HASH，开并行并且不广播，成功将SQL从50分钟优化到4分钟！

这个是我班级的一个徒弟做的优化案例，发出来大家乐呵一下

0 0