ITPub博客

首页 > 数据库 > Oracle > 【SQL优化案例】关于wm_concat的一次优化

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

原创 Oracle 作者:azzotest 时间:2015-09-01 00:33:38 0 删除 编辑
今天开始小假期的休息,晚上闲来无事,在落落大神的优化班里胡侃,有2个同学发来SQL优化求助,本是同根生,相煎何太急,反正明天不上班,就拿来看看。
第一段比较简单,BI系统的merge语句要执行半小时,原因是没有做表分析,1亿数据量的表分区却走了全表扫描,且没有使用并行(分析系统终极优化法则:分区+并行

第二段SQL属于OLTP交易系统,无法开并行,否则在业务洪峰的并发期服务器会down。
如下是这位兄台的SQL,说在数据库了10分钟出不来,自己停掉了

点击(此处)折叠或打开

  1. select *
  2.   from (SELECT wm_concat(T1.BILLID) AS BILLID,
  3.                MAX(T1.ACCTID) AS ACCTID,
  4.                MAX(T2.CUSTID) AS CUSTID,
  5.                MAX(T4.NAME) AS CUSTNAME,
  6.                MAX(loan.capobj) as CAPOBJ,
  7.                MAX(T4.MOBILEPHONE) AS MOBILEPHONE,
  8.                MAX(T2.LOANAPPNO) AS LOANAPPNO,
  9.                MAX(T2.LOANCONTRACTNO) AS LOANCONTRACTNO,
  10.                MAX(T2.PAYBANKACCTNAME) AS PAYBANKACCTNAME,
  11.                MAX(T2.PAYBANKNAME) AS PAYBANKNAME,
  12.                MAX(T2.PAYBANKACCTNO) AS PAYBANKACCTNO,
  13.                MAX(T2.JRLCONTRACTNO) AS JRLCONTRACTNO,
  14.                MAX(T2.AGREEPAYBRANCHID) AS AGREEPAYBRANCHID,
  15.                MAX(T2.COLLOPRID) AS COLLOPRID,
  16.                MAX(TO_CHAR(T2.COLLDATE, 'YYYY-MM-DD')) AS COLLDATE,
  17.                MAX(TO_CHAR(T2.SENDLOANDATE, 'YYYY-MM-DD')) AS SENDLOANDATE,
  18.                MAX(T2.LOANMONEY) AS LOANMONEY,
  19.                MAX(TO_CHAR(T1.PAYDATE, 'YYYY-MM-DD')) AS PAYDATE,
  20.                MAX(TRUNC(sysdate - T1.PAYDATE)) as OVERDUEDAY,
  21.                MAX(T1.TOTALPERIODS) AS TOTALPERIODS,
  22.                MAX(T1.CURRENTPERIOD) AS CURRENTPERIOD,
  23.                SUM(T1.MONEY) AS MONEY,
  24.                MAX(T8.ELE_NAME) AS ELE_NAME,
  25.                MAX(T11.C_NAME) AS PROJECTNAME,
  26.                MAX(T12.CITY) AS CITY,
  27.                MAX(T1.FIELDB) AS XMLSTATUS,
  28.                MAX(T1.FIELDC) AS JRLSTATUS,
  29.                MAX(T1.SENDBANKBATCHNO) AS BATCHO,
  30.                MAX(T1.FEESTATUS) AS FEESTATUS,
  31.                MAX(TO_CHAR(nvl(T18.subdate, t1.realpaydate), 'YYYY-MM-DD')) AS REALPAYDATE,
  32.                MAX(TO_CHAR(nvl(T18.Confirm_Date, t1.realpaydate),
  33.                            'YYYY-MM-DD')) AS SETWRKDATE,
  34.                MAX(t2.fieldd) AS hangUp,
  35.                MAX(t2.hangup_date) AS hangupDate,
  36.                MAX(t2.hangup_reason) AS hangupReason,
  37.                MAX(t2.hanguptype) AS hangupType,
  38.                MAX(t2.prepaymentstat) AS prepaymentstat,
  39.                MAX(t2.loandelaystat) AS loandelaystat
  40.          FROM T_ACCT_CUSTBILLDTL T1
  41.           Inner JOIN T_ACCT_CUSTACCOUNT T2
  42.             ON T1.ACCTID = T2.ACCTID
  43.           LEFT JOIN T_APP_LOANAPP loan
  44.             on T2.appid = loan.appid
  45.           LEFT JOIN T_COMBIZ_PRODINFO T3
  46.             ON T2.PRODUCTNO = T3.PRODCODE
  47.           LEFT JOIN T_CRMSRV_CUSTINFO T4
  48.             ON T1.CUSTID = T4.CUSTID
  49.          INNER JOIN (SELECT T6.ELE_CODE, T6.ELE_NAME
  50.                       FROM TB_APP_ELEMENT T6
  51.                       LEFT JOIN TB_APP_ELEMENT_GROUP T7
  52.                         ON T6.ELE_GROUP_ID = T7.ID
  53.                      WHERE T7.ELE_GROUP_NAME = 'FEE_STATUS') T8
  54.             ON T1.FEESTATUS = T8.ELE_CODE
  55.           LEFT JOIN B_PROJECT T11
  56.             ON T2.APPPROJECTNO = T11.PROJECT_ID
  57.           LEFT JOIN B_ADDRESS T12
  58.             ON T11.ADDRESS_ID = T12.ADDRESS_ID
  59.           left join t_acct_custbill_jrl_main T18
  60.             ON (t1.sendbankbatchno = t18.batno and T18.isenabled = '1')
  61.          WHERE T1.ISENABLED = '1'
  62.            AND T1.INOROUT = 'IN'
  63.            AND T2.ACCTSTATUS in ('0', '2')
  64.            AND T1.Money > 0
  65.            And t1.acctid=2041317
  66.            and T1.FEETYPE in ('LOAN_PRINCIPAL',
  67.                               'MONTH_INTEREST',
  68.                               'PENALTY_INTEREST',
  69.                               'ONCE_INTEREST')
  70.            and (T2.Fielde is null or T2.Fielde = '0')
  71.          GROUP BY T1.ACCTID,
  72.                   T1.CURRENTPERIOD,
  73.                   TO_CHAR(T1.PAYDATE, 'YYYY-MM-DD'),
  74.                   T1.FIELDE) a
  75.  where 1 = 1
  76.  ORDER BY a.SENDLOANDATE;


  77.  Plan hash value: 959570136
  78.  
  79. ------------------------------------------------------------------------------------------------------------------
  80. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  81. ------------------------------------------------------------------------------------------------------------------
  82. | 0 | SELECT STATEMENT | | 1 | 473 | 4771 (1)| 00:00:58 |
  83. | 1 | SORT ORDER BY | | 1 | 473 | 4771 (1)| 00:00:58 |
  84. | 2 | SORT GROUP BY | | 1 | 473 | 4771 (1)| 00:00:58 |
  85. |* 3 | HASH JOIN OUTER | | 3 | 1419 | 4769 (1)| 00:00:58 |
  86. | 4 | NESTED LOOPS OUTER | | 3 | 1368 | 4735 (1)| 00:00:57 |
  87. | 5 | NESTED LOOPS OUTER | | 3 | 1278 | 4732 (1)| 00:00:57 |
  88. | 6 | NESTED LOOPS OUTER | | 3 | 1188 | 4729 (1)| 00:00:57 |
  89. | 7 | NESTED LOOPS OUTER | | 3 | 1125 | 4726 (1)| 00:00:57 |
  90. | 8 | NESTED LOOPS | | 3 | 1080 | 4723 (1)| 00:00:57 |
  91. |* 9 | HASH JOIN | | 3 | 459 | 4720 (1)| 00:00:57 |
  92. | 10 | NESTED LOOPS | | 6 | 306 | 5 (0)| 00:00:01 |
  93. | 11 | TABLE ACCESS BY INDEX ROWID| TB_APP_ELEMENT_GROUP | 1 | 22 | 1 (0)| 00:00:01 |
  94. |* 12 | INDEX UNIQUE SCAN | GROUP_NAME_UQ | 1 | | 0 (0)| 00:00:01 |
  95. | 13 | TABLE ACCESS BY INDEX ROWID| TB_APP_ELEMENT | 6 | 174 | 4 (0)| 00:00:01 |
  96. |* 14 | INDEX RANGE SCAN | INX_UNI | 6 | | 1 (0)| 00:00:01 |
  97. |* 15 | TABLE ACCESS FULL | T_ACCT_CUSTBILLDTL | 18 | 1836 | 4715 (1)| 00:00:57 |
  98. |* 16 | TABLE ACCESS BY INDEX ROWID | T_ACCT_CUSTACCOUNT | 1 | 207 | 1 (0)| 00:00:01 |
  99. |* 17 | INDEX UNIQUE SCAN | PK_T_ACCT_CUSTACCOUNT | 1 | | 0 (0)| 00:00:01 |
  100. | 18 | TABLE ACCESS BY INDEX ROWID | T_APP_LOANAPP | 1 | 15 | 1 (0)| 00:00:01 |
  101. |* 19 | INDEX UNIQUE SCAN | PK_T_APP_LOANAPP | 1 | | 0 (0)| 00:00:01 |
  102. |* 20 | TABLE ACCESS BY INDEX ROWID | T_ACCT_CUSTBILL_JRL_MAIN | 1 | 21 | 3 (0)| 00:00:01 |
  103. |* 21 | INDEX RANGE SCAN | IDX_JRL_MAIN_BATCHNO | 1 | | 2 (0)| 00:00:01 |
  104. | 22 | TABLE ACCESS BY INDEX ROWID | T_CRMSRV_CUSTINFO | 1 | 30 | 1 (0)| 00:00:01 |
  105. |* 23 | INDEX UNIQUE SCAN | PK_T_CRMSRV_CUSTINFO | 1 | | 0 (0)| 00:00:01 |
  106. | 24 | TABLE ACCESS BY INDEX ROWID | B_PROJECT | 1 | 30 | 1 (0)| 00:00:01 |
  107. |* 25 | INDEX RANGE SCAN | IDX_PROJECT_ID | 1 | | 0 (0)| 00:00:01 |
  108. | 26 | TABLE ACCESS FULL | B_ADDRESS | 4507 | 76619 | 34 (0)| 00:00:01 |
  109. ------------------------------------------------------------------------------------------------------------------
  110.  
  111. Predicate Information (identified by operation id):
  112. ---------------------------------------------------
  113.  
  114.    3 - access("T11"."ADDRESS_ID"="T12"."ADDRESS_ID"(+))
  115.    9 - access("T1"."FEESTATUS"="T6"."ELE_CODE")
  116.   12 - access("T7"."ELE_GROUP_NAME"='FEE_STATUS')
  117.   14 - access("T6"."ELE_GROUP_ID"="T7"."ID")
  118.   15 - filter(TO_NUMBER("T1"."ACCTID")=2041317 AND "T1"."INOROUT"='IN' AND "T1"."ISENABLED"='1' AND
  119.               ("T1"."FEETYPE"='LOAN_PRINCIPAL' OR "T1"."FEETYPE"='MONTH_INTEREST' OR "T1"."FEETYPE"='ONCE_INTEREST' OR
  120.               "T1"."FEETYPE"='PENALTY_INTEREST') AND "T1"."MONEY">0)
  121.   16 - filter(("T2"."FIELDE" IS NULL OR "T2"."FIELDE"='0') AND ("T2"."ACCTSTATUS"='0' OR
  122.               "T2"."ACCTSTATUS"='2'))
  123.   17 - access("T1"."ACCTID"="T2"."ACCTID")
  124.   19 - access("T2"."APPID"="LOAN"."APPID"(+))
  125.   20 - filter("T18"."ISENABLED"(+)='1')
  126.   21 - access("T1"."SENDBANKBATCHNO"="T18"."BATNO"(+))
  127.   23 - access("T1"."CUSTID"="T4"."CUSTID"(+))
  128.   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后的执行计划:

点击(此处)折叠或打开

  1. Plan hash value: 319752913
  2.  
  3. --------------------------------------------------------------------------------------------------------------------------
  4. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  5. --------------------------------------------------------------------------------------------------------------------------
  6. | 0 | SELECT STATEMENT | | 100K| 45M| | 32921 (1)| 00:06:36 |
  7. | 1 | SORT ORDER BY | | 100K| 45M| 48M| 32921 (1)| 00:06:36 |
  8. | 2 | SORT GROUP BY | | 100K| 45M| 48M| 32921 (1)| 00:06:36 |
  9. |* 3 | HASH JOIN RIGHT OUTER | | 100K| 45M| | 12772 (1)| 00:02:34 |
  10. | 4 | TABLE ACCESS FULL | B_ADDRESS | 4507 | 76619 | | 34 (0)| 00:00:01 |
  11. |* 5 | HASH JOIN RIGHT OUTER | | 100K| 43M| | 12738 (1)| 00:02:33 |
  12. | 6 | TABLE ACCESS FULL | B_PROJECT | 9366 | 274K| | 105 (0)| 00:00:02 |
  13. |* 7 | HASH JOIN RIGHT OUTER | | 100K| 40M| | 12632 (1)| 00:02:32 |
  14. | 8 | TABLE ACCESS FULL | T_APP_LOANAPP | 46533 | 681K| | 1238 (1)| 00:00:15 |
  15. |* 9 | HASH JOIN | | 100K| 39M| 8184K| 11393 (1)| 00:02:17 |
  16. |* 10 | TABLE ACCESS FULL | T_ACCT_CUSTACCOUNT | 38238 | 7729K| | 965 (1)| 00:00:12 |
  17. |* 11 | HASH JOIN RIGHT OUTER | | 104K| 20M| 3888K| 8958 (1)| 00:01:48 |
  18. | 12 | TABLE ACCESS FULL | T_CRMSRV_CUSTINFO | 94653 | 2773K| | 1614 (1)| 00:00:20 |
  19. |* 13 | HASH JOIN RIGHT OUTER | | 104K| 17M| 2528K| 6229 (1)| 00:01:15 |
  20. |* 14 | TABLE ACCESS FULL | T_ACCT_CUSTBILL_JRL_MAIN | 78252 | 1604K| | 551 (1)| 00:00:07 |
  21. |* 15 | HASH JOIN | | 104K| 15M| | 4734 (1)| 00:00:57 |
  22. | 16 | NESTED LOOPS | | 6 | 306 | | 5 (0)| 00:00:01 |
  23. | 17 | TABLE ACCESS BY INDEX ROWID| TB_APP_ELEMENT_GROUP | 1 | 22 | | 1 (0)| 00:00:01 |
  24. |* 18 | INDEX UNIQUE SCAN | GROUP_NAME_UQ | 1 | | | 0 (0)| 00:00:01 |
  25. | 19 | TABLE ACCESS BY INDEX ROWID| TB_APP_ELEMENT | 6 | 174 | | 4 (0)| 00:00:01 |
  26. |* 20 | INDEX RANGE SCAN | INX_UNI | 6 | | | 1 (0)| 00:00:01 |
  27. |* 21 | TABLE ACCESS FULL | T_ACCT_CUSTBILLDTL | 708K| 69M| | 4727 (1)| 00:00:57 |
  28. --------------------------------------------------------------------------------------------------------------------------
  29.  
  30. Predicate Information (identified by operation id):
  31. ---------------------------------------------------
  32.  
  33.    3 - access("T11"."ADDRESS_ID"="T12"."ADDRESS_ID"(+))
  34.    5 - access("T11"."PROJECT_ID"(+)=TO_NUMBER("T2"."APPPROJECTNO"))
  35.    7 - access("T2"."APPID"="LOAN"."APPID"(+))
  36.    9 - access("T1"."ACCTID"="T2"."ACCTID")
  37.   10 - filter(("T2"."FIELDE" IS NULL OR "T2"."FIELDE"='0') AND ("T2"."ACCTSTATUS"='0' OR "T2"."ACCTSTATUS"='2'))
  38.   11 - access("T1"."CUSTID"="T4"."CUSTID"(+))
  39.   13 - access("T1"."SENDBANKBATCHNO"="T18"."BATNO"(+))
  40.   14 - filter("T18"."ISENABLED"(+)='1')
  41.   15 - access("T1"."FEESTATUS"="T6"."ELE_CODE")
  42.   18 - access("T7"."ELE_GROUP_NAME"='FEE_STATUS')
  43.   20 - access("T6"."ELE_GROUP_ID"="T7"."ID")
  44.   21 - filter("T1"."INOROUT"='IN' AND "T1"."ISENABLED"='1' AND ("T1"."FEETYPE"='LOAN_PRINCIPAL' OR
  45.               "T1"."FEETYPE"='MONTH_INTEREST' OR "T1"."FEETYPE"='ONCE_INTEREST' OR "T1"."FEETYPE"='PENALTY_INTEREST') AND
  46.               "T1"."MONEY">0)

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




妈蛋,这哪是去掉wm_concat,这明显把所有的聚合函数都去掉了好吗?!


不过这也说明了2个问题:
1. 它使用了聚合函数 + group by(虽然不知道为啥要group by)
2. wm_concat性能略差

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

至此并没有完结,
可以看出他的执行计划中(第二段,请忽略第一段)
在SQL的一个内联视图的查询中:

点击(此处)折叠或打开

  1. INNER JOIN (SELECT T6.ELE_CODE, T6.ELE_NAME
  2.                       FROM TB_APP_ELEMENT T6
  3.                       LEFT JOIN TB_APP_ELEMENT_GROUP T7
  4.                         ON T6.ELE_GROUP_ID = T7.ID
  5.                      WHERE T7.ELE_GROUP_NAME = 'FEE_STATUS') T8



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

点击(此处)折叠或打开

  1. | 16 | NESTED LOOPS | | 6 | 306 | | 5 (0)| 00:00:01 |
  2. | 17 | TABLE ACCESS BY INDEX ROWID| TB_APP_ELEMENT_GROUP | 1 | 22 | | 1 (0)| 00:00:01 |
  3. |* 18 | INDEX UNIQUE SCAN | GROUP_NAME_UQ | 1 | | | 0 (0)| 00:00:01 |
  4. | 19 | TABLE ACCESS BY INDEX ROWID| TB_APP_ELEMENT | 6 | 174 | | 4 (0)| 00:00:01 |
  5. |* 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

潜水了这么久第一次申请博客并发表文章,后面有时间会经常写一些经验和心得,若有不对的地方,还请大婶们指正 :)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26224278/viewspace-1787991/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-08-21

  • 博文量
    22
  • 访问量
    46385