【SQL优化案例】关于wm_concat的一次优化

发表: 2015-10-09 浏览: 6615

性能优化

前几天有2个同学发来SQL优化求助。

第一段比较简单，BI系统的merge语句要执行半小时，原因是没有做表分析，1亿数据量的表分区却走了全表扫描，且没有使用并行（分析系统终极优化法则：分区+并行）

第二段SQL属于OLTP交易系统，无法开并行，否则在业务洪峰的并发期服务器会down。

如下是这位兄台的SQL，说在数据库了10分钟出不来，自己停掉了

select *

  from (SELECT wm_concat(T1.BILLID) AS BILLID,

               MAX(T1.ACCTID) AS ACCTID,

               MAX(T2.CUSTID) AS CUSTID,

               MAX(T4.NAME) AS CUSTNAME,

               MAX(loan.capobj) as CAPOBJ,

               MAX(T4.MOBILEPHONE) AS MOBILEPHONE,

               MAX(T2.LOANAPPNO) AS LOANAPPNO,

               MAX(T2.LOANCONTRACTNO) AS LOANCONTRACTNO,

               MAX(T2.PAYBANKACCTNAME) AS PAYBANKACCTNAME,

               MAX(T2.PAYBANKNAME) AS PAYBANKNAME,

               MAX(T2.PAYBANKACCTNO) AS PAYBANKACCTNO,

               MAX(T2.JRLCONTRACTNO) AS JRLCONTRACTNO,

               MAX(T2.AGREEPAYBRANCHID) AS AGREEPAYBRANCHID,

               MAX(T2.COLLOPRID) AS COLLOPRID,

               MAX(TO_CHAR(T2.COLLDATE, 'YYYY-MM-DD')) AS COLLDATE,

               MAX(TO_CHAR(T2.SENDLOANDATE, 'YYYY-MM-DD')) AS SENDLOANDATE,

               MAX(T2.LOANMONEY) AS LOANMONEY,

               MAX(TO_CHAR(T1.PAYDATE, 'YYYY-MM-DD')) AS PAYDATE,

               MAX(TRUNC(sysdate - T1.PAYDATE)) as OVERDUEDAY,

               MAX(T1.TOTALPERIODS) AS TOTALPERIODS,

               MAX(T1.CURRENTPERIOD) AS CURRENTPERIOD,

               SUM(T1.MONEY) AS MONEY,

               MAX(T8.ELE_NAME) AS ELE_NAME,

               MAX(T11.C_NAME) AS PROJECTNAME,

               MAX(T12.CITY) AS CITY,

               MAX(T1.FIELDB) AS XMLSTATUS,

               MAX(T1.FIELDC) AS JRLSTATUS,

               MAX(T1.SENDBANKBATCHNO) AS BATCHO,

               MAX(T1.FEESTATUS) AS FEESTATUS,

               MAX(TO_CHAR(nvl(T18.subdate, t1.realpaydate), 'YYYY-MM-DD')) AS REALPAYDATE,

               MAX(TO_CHAR(nvl(T18.Confirm_Date, t1.realpaydate),

                           'YYYY-MM-DD')) AS SETWRKDATE,

               MAX(t2.fieldd) AS hangUp,

               MAX(t2.hangup_date) AS hangupDate,

               MAX(t2.hangup_reason) AS hangupReason,

               MAX(t2.hanguptype) AS hangupType,

               MAX(t2.prepaymentstat) AS prepaymentstat,

               MAX(t2.loandelaystat) AS loandelaystat

         FROM T_ACCT_CUSTBILLDTL T1

          Inner JOIN T_ACCT_CUSTACCOUNT T2

            ON T1.ACCTID = T2.ACCTID

          LEFT JOIN T_APP_LOANAPP loan

            on T2.appid = loan.appid

          LEFT JOIN T_COMBIZ_PRODINFO T3

            ON T2.PRODUCTNO = T3.PRODCODE

          LEFT JOIN T_CRMSRV_CUSTINFO T4

            ON T1.CUSTID = T4.CUSTID

         INNER JOIN (SELECT T6.ELE_CODE, T6.ELE_NAME

                      FROM TB_APP_ELEMENT T6

                      LEFT JOIN TB_APP_ELEMENT_GROUP T7

                        ON T6.ELE_GROUP_ID = T7.ID

                     WHERE T7.ELE_GROUP_NAME = 'FEE_STATUS') T8

            ON T1.FEESTATUS = T8.ELE_CODE

          LEFT JOIN B_PROJECT T11

            ON T2.APPPROJECTNO = T11.PROJECT_ID

          LEFT JOIN B_ADDRESS T12

            ON T11.ADDRESS_ID = T12.ADDRESS_ID

          left join t_acct_custbill_jrl_main T18

            ON (t1.sendbankbatchno = t18.batno and T18.isenabled = '1')

         WHERE T1.ISENABLED = '1'

           AND T1.INOROUT = 'IN'

           AND T2.ACCTSTATUS in ('0', '2')

           AND T1.Money > 0

           And t1.acctid=2041317

           and T1.FEETYPE in ('LOAN_PRINCIPAL',

                              'MONTH_INTEREST',

                              'PENALTY_INTEREST',

                              'ONCE_INTEREST')

           and (T2.Fielde is null or T2.Fielde = '0')

         GROUP BY T1.ACCTID,

                  T1.CURRENTPERIOD,

                  TO_CHAR(T1.PAYDATE, 'YYYY-MM-DD'),

                  T1.FIELDE) a

 where 1 = 1

 ORDER BY a.SENDLOANDATE;

 Plan hash value: 959570136

 

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 473 | 4771 (1)| 00:00:58 |

| 1 | SORT ORDER BY | | 1 | 473 | 4771 (1)| 00:00:58 |

| 2 | SORT GROUP BY | | 1 | 473 | 4771 (1)| 00:00:58 |

|* 3 | HASH JOIN OUTER | | 3 | 1419 | 4769 (1)| 00:00:58 |

| 4 | NESTED LOOPS OUTER | | 3 | 1368 | 4735 (1)| 00:00:57 |

| 5 | NESTED LOOPS OUTER | | 3 | 1278 | 4732 (1)| 00:00:57 |

| 6 | NESTED LOOPS OUTER | | 3 | 1188 | 4729 (1)| 00:00:57 |

| 7 | NESTED LOOPS OUTER | | 3 | 1125 | 4726 (1)| 00:00:57 |

| 8 | NESTED LOOPS | | 3 | 1080 | 4723 (1)| 00:00:57 |

|* 9 | HASH JOIN | | 3 | 459 | 4720 (1)| 00:00:57 |

| 10 | NESTED LOOPS | | 6 | 306 | 5 (0)| 00:00:01 |

| 11 | TABLE ACCESS BY INDEX ROWID| TB_APP_ELEMENT_GROUP | 1 | 22 | 1 (0)| 00:00:01 |

|* 12 | INDEX UNIQUE SCAN | GROUP_NAME_UQ | 1 | | 0 (0)| 00:00:01 |

| 13 | TABLE ACCESS BY INDEX ROWID| TB_APP_ELEMENT | 6 | 174 | 4 (0)| 00:00:01 |

|* 14 | INDEX RANGE SCAN | INX_UNI | 6 | | 1 (0)| 00:00:01 |

|* 15 | TABLE ACCESS FULL | T_ACCT_CUSTBILLDTL | 18 | 1836 | 4715 (1)| 00:00:57 |

|* 16 | TABLE ACCESS BY INDEX ROWID | T_ACCT_CUSTACCOUNT | 1 | 207 | 1 (0)| 00:00:01 |

|* 17 | INDEX UNIQUE SCAN | PK_T_ACCT_CUSTACCOUNT | 1 | | 0 (0)| 00:00:01 |

| 18 | TABLE ACCESS BY INDEX ROWID | T_APP_LOANAPP | 1 | 15 | 1 (0)| 00:00:01 |

|* 19 | INDEX UNIQUE SCAN | PK_T_APP_LOANAPP | 1 | | 0 (0)| 00:00:01 |

|* 20 | TABLE ACCESS BY INDEX ROWID | T_ACCT_CUSTBILL_JRL_MAIN | 1 | 21 | 3 (0)| 00:00:01 |

|* 21 | INDEX RANGE SCAN | IDX_JRL_MAIN_BATCHNO | 1 | | 2 (0)| 00:00:01 |

| 22 | TABLE ACCESS BY INDEX ROWID | T_CRMSRV_CUSTINFO | 1 | 30 | 1 (0)| 00:00:01 |

|* 23 | INDEX UNIQUE SCAN | PK_T_CRMSRV_CUSTINFO | 1 | | 0 (0)| 00:00:01 |

| 24 | TABLE ACCESS BY INDEX ROWID | B_PROJECT | 1 | 30 | 1 (0)| 00:00:01 |

|* 25 | INDEX RANGE SCAN | IDX_PROJECT_ID | 1 | | 0 (0)| 00:00:01 |

| 26 | TABLE ACCESS FULL | B_ADDRESS | 4507 | 76619 | 34 (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   3 - access("T11"."ADDRESS_ID"="T12"."ADDRESS_ID"(+))

   9 - access("T1"."FEESTATUS"="T6"."ELE_CODE")

  12 - access("T7"."ELE_GROUP_NAME"='FEE_STATUS')

  14 - access("T6"."ELE_GROUP_ID"="T7"."ID")

  15 - filter(TO_NUMBER("T1"."ACCTID")=2041317 AND "T1"."INOROUT"='IN' AND "T1"."ISENABLED"='1' AND 

              ("T1"."FEETYPE"='LOAN_PRINCIPAL' OR "T1"."FEETYPE"='MONTH_INTEREST' OR "T1"."FEETYPE"='ONCE_INTEREST' OR 

              "T1"."FEETYPE"='PENALTY_INTEREST') AND "T1"."MONEY">0)

  16 - filter(("T2"."FIELDE" IS NULL OR "T2"."FIELDE"='0') AND ("T2"."ACCTSTATUS"='0' OR 

              "T2"."ACCTSTATUS"='2'))

  17 - access("T1"."ACCTID"="T2"."ACCTID")

  19 - access("T2"."APPID"="LOAN"."APPID"(+))

  20 - filter("T18"."ISENABLED"(+)='1')

  21 - access("T1"."SENDBANKBATCHNO"="T18"."BATNO"(+))

  23 - access("T1"."CUSTID"="T4"."CUSTID"(+))

  25 - access("T11"."PROJECT_ID"(+)=TO_NUMBER("T2"."APPPROJECTNO"))

表记录数:

T_ACCT_CUSTBILLDTL 925729

T_CRMSRV_CUSTINFO 94653

T_ACCT_CUSTBILL_JRL_MAIN 78254

T_APP_LOANAPP 46293

T_ACCT_CUSTACCOUNT 39280

B_PROJECT 9361

B_ADDRESS 4507

TB_APP_ELEMENT 855

TB_APP_ELEMENT_GROUP 143

看了半天，纳闷标黄部分为啥没走唯一索引（ID字段是有unique index的），于是质问他是否也跟第一位兄台一样没做表分析，然后。。。然后。。。

然后这哥们又发给我一段，说发错了

如下是去掉And t1.acctid=2041317后的执行计划：

Plan hash value: 319752913

 

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

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 100K| 45M| | 32921 (1)| 00:06:36 |

| 1 | SORT ORDER BY | | 100K| 45M| 48M| 32921 (1)| 00:06:36 |

| 2 | SORT GROUP BY | | 100K| 45M| 48M| 32921 (1)| 00:06:36 |

|* 3 | HASH JOIN RIGHT OUTER | | 100K| 45M| | 12772 (1)| 00:02:34 |

| 4 | TABLE ACCESS FULL | B_ADDRESS | 4507 | 76619 | | 34 (0)| 00:00:01 |

|* 5 | HASH JOIN RIGHT OUTER | | 100K| 43M| | 12738 (1)| 00:02:33 |

| 6 | TABLE ACCESS FULL | B_PROJECT | 9366 | 274K| | 105 (0)| 00:00:02 |

|* 7 | HASH JOIN RIGHT OUTER | | 100K| 40M| | 12632 (1)| 00:02:32 |

| 8 | TABLE ACCESS FULL | T_APP_LOANAPP | 46533 | 681K| | 1238 (1)| 00:00:15 |

|* 9 | HASH JOIN | | 100K| 39M| 8184K| 11393 (1)| 00:02:17 |

|* 10 | TABLE ACCESS FULL | T_ACCT_CUSTACCOUNT | 38238 | 7729K| | 965 (1)| 00:00:12 |

|* 11 | HASH JOIN RIGHT OUTER | | 104K| 20M| 3888K| 8958 (1)| 00:01:48 |

| 12 | TABLE ACCESS FULL | T_CRMSRV_CUSTINFO | 94653 | 2773K| | 1614 (1)| 00:00:20 |

|* 13 | HASH JOIN RIGHT OUTER | | 104K| 17M| 2528K| 6229 (1)| 00:01:15 |

|* 14 | TABLE ACCESS FULL | T_ACCT_CUSTBILL_JRL_MAIN | 78252 | 1604K| | 551 (1)| 00:00:07 |

|* 15 | HASH JOIN | | 104K| 15M| | 4734 (1)| 00:00:57 |

| 16 | NESTED LOOPS | | 6 | 306 | | 5 (0)| 00:00:01 |

| 17 | TABLE ACCESS BY INDEX ROWID| TB_APP_ELEMENT_GROUP | 1 | 22 | | 1 (0)| 00:00:01 |

|* 18 | INDEX UNIQUE SCAN | GROUP_NAME_UQ | 1 | | | 0 (0)| 00:00:01 |

| 19 | TABLE ACCESS BY INDEX ROWID| TB_APP_ELEMENT | 6 | 174 | | 4 (0)| 00:00:01 |

|* 20 | INDEX RANGE SCAN | INX_UNI | 6 | | | 1 (0)| 00:00:01 |

|* 21 | TABLE ACCESS FULL | T_ACCT_CUSTBILLDTL | 708K| 69M| | 4727 (1)| 00:00:57 |

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

 

Predicate Information (identified by operation id):

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

 

   3 - access("T11"."ADDRESS_ID"="T12"."ADDRESS_ID"(+))

   5 - access("T11"."PROJECT_ID"(+)=TO_NUMBER("T2"."APPPROJECTNO"))

   7 - access("T2"."APPID"="LOAN"."APPID"(+))

   9 - access("T1"."ACCTID"="T2"."ACCTID")

  10 - filter(("T2"."FIELDE" IS NULL OR "T2"."FIELDE"='0') AND ("T2"."ACCTSTATUS"='0' OR "T2"."ACCTSTATUS"='2'))

  11 - access("T1"."CUSTID"="T4"."CUSTID"(+))

  13 - access("T1"."SENDBANKBATCHNO"="T18"."BATNO"(+))

  14 - filter("T18"."ISENABLED"(+)='1')

  15 - access("T1"."FEESTATUS"="T6"."ELE_CODE")

  18 - access("T7"."ELE_GROUP_NAME"='FEE_STATUS')

  20 - access("T6"."ELE_GROUP_ID"="T7"."ID")

  21 - filter("T1"."INOROUT"='IN' AND "T1"."ISENABLED"='1' AND ("T1"."FEETYPE"='LOAN_PRINCIPAL' OR 

              "T1"."FEETYPE"='MONTH_INTEREST' OR "T1"."FEETYPE"='ONCE_INTEREST' OR "T1"."FEETYPE"='PENALTY_INTEREST') AND 

              "T1"."MONEY">0)

从这位兄台口中得知，不加wm_concat，只需要13秒，加上就出不来了，按照他的说法，他截了个图：

Clipboard Image.png

妈蛋，这哪是去掉wm_concat，这明显把所有的聚合函数都去掉了好吗？！

不过这也说明了2个问题：

1. 它使用了聚合函数 + group by（虽然不知道为啥要group by）

2. wm_concat性能略差

由于wm_concat这个函数非常不稳定，10g中返回值是varchar，到了11g返回值成了clob，到了12c直接没这个函数了，所以针对第二点直接让他用listagg替换掉，1分多钟出数。

至此并没有完结，

可以看出他的执行计划中（第二段，请忽略第一段）

在SQL的一个内联视图的查询中：

INNER JOIN (SELECT T6.ELE_CODE, T6.ELE_NAME

                      FROM TB_APP_ELEMENT T6

                      LEFT JOIN TB_APP_ELEMENT_GROUP T7

                        ON T6.ELE_GROUP_ID = T7.ID

                     WHERE T7.ELE_GROUP_NAME = 'FEE_STATUS') T8

这里出现了问题，从执行计划16-20行看得出，用了nest loop的关联方式：

| 16 | NESTED LOOPS | | 6 | 306 | | 5 (0)| 00:00:01 |

| 17 | TABLE ACCESS BY INDEX ROWID| TB_APP_ELEMENT_GROUP | 1 | 22 | | 1 (0)| 00:00:01 |

|* 18 | INDEX UNIQUE SCAN | GROUP_NAME_UQ | 1 | | | 0 (0)| 00:00:01 |

| 19 | TABLE ACCESS BY INDEX ROWID| TB_APP_ELEMENT | 6 | 174 | | 4 (0)| 00:00:01 |

|* 20 | INDEX RANGE SCAN | INX_UNI | 6 | | | 1 (0)| 00:00:01 |

而正确的执行计划应该走Hash Join，所以发给他一个hint， /*+ use_hash(T6,T7)*/ 加在T6和T7关联的视图中，

再次查询执行计划如下，耗时30s：

由于这位哥们没有截出详细的统计信息，所以也不确定能否继续优化，优化后的结果他自己也比较满意，所以此次优化就到此为止。

【优化点1】

wm_concat函数性能较差，11g下返回值为clob，修改为listagg后性能提升，时间至少缩短1/10（原SQL跑10分被这货中断了，能不能有点耐心！）。

【优化点2】

为什么这里的NL要改成Hash？

是因为他的SQL中有聚合函数+group by，在这里NL的连接方式没有任何优势，因为聚合是要等数据全部出来后再做聚合，很明显若使用Hash连接，则会更优。修改为Hash后性能提升，时间缩短1/2

受梁勇邀请开通博客并发表文章，后面有时间会经常写一些经验和心得，若有不对的地方，还请大婶们指正：）

16 个评论

天涯浪子

学习下，Oracle这块感兴趣，多出点优化，管理方面的文档。

老头子回复天涯浪子

不做DBA，管理的东西不是我的强项。。不过我考了OCP - -

rmyymr

不错呀，对Oracle底层了解挺多的。

老头子回复 rmyymr

略懂略懂。。话说回复还要20字

rmyymr 回复老头子

求帮忙啊，老头子 sql数据库创建触发器，向oracle数据库中某个表插入数据，有人写过吗？ http://www.flybi.net/question/13326 刚好遇到个问题，帮我看下，感谢。

BAO胖子

赞，不过建议贴图的时候把人名字（CUSTNAME）隐去，被人看到不太好

老头子回复 BAO胖子

呃，这是我写的第一篇博客，原发在ITPUB，没什么经验，呵呵

BAO胖子回复老头子

有的时候容易惹麻烦，你得这么想，万一火了呢，看的人转发的人就多了。

老头子回复 BAO胖子

嗯，一会修改下

BAO胖子

嗯，我遇到过被逼离职的选手，就是论坛发个帖子，被客户无意中翻到，然后找茬。。。还是小心为上

梁勇回复 BAO胖子

还是邵哥，有经验些，我才开始也是不懂，有一次，一个朋友有个文档，我帮忙发布啊，里面有客户的机器名，被他们项目经理看到啊，然后。。。。。

BAO胖子回复梁勇

项目都有保密协议，中国这东西管的不严，曾经做过的某非国内项目，很多东西都是打印出来看，看完立马锁柜子里，过程还得走批复流程以及签字盖章....这种东西还是小心为上。

悟回复 BAO胖子

这也是最基本的职业素养

BAO胖子回复悟

从业时间短，或者对这方面无意识的小伙伴也有，还是需要建立这种意识。而甲方有的时候矫枉过正，你给我打印100页的EXCEL不让我用电脑分析，有个毛用啊......

flzhang132

文章写的很好，但这么多连接，您是怎么一下就看出子查询那要用hash连接的。
SELECT T6.ELE_CODE, T6.ELE_NAME
FROM TB_APP_ELEMENT T6
LEFT JOIN TB_APP_ELEMENT_GROUP T7
ON T6.ELE_GROUP_ID = T7.ID
WHERE T7.ELE_GROUP_NAME = 'FEE_STATUS'
子查询没有group+聚合，为何说这里用hash连接好呢？？

老头子回复 flzhang132

外层有group by 也一样要等内层数据全部出来才能做group by操作

要回复文章请先登录或注册