使用USE_HASH Hint調優一個SQL語句

abstractcyj發表於2016-09-12

開發的同事寫了個很長的SQL，執行效率低下，於是找到我：
SQL如下：

點選(此處)摺疊或開啟

SELECT w.wareid,
w.warename,
w.warespec,
w.wareunit,
w.factoryid,
w.bandcode,
w.lastpurprice,
ws.saleprice,
CASE
WHEN ws.saleprice = 0 THEN
0
ELSE
round((ws.saleprice - w.lastpurprice) / ws.saleprice, 4)
END mll,
fl.fl,
zkc.xl1,
s2.xl2,
zkc.xl5,
zkc.xl3,
zkc.xl4,
zbkc.zbkc,
CASE
WHEN nvl(xl4, 0) = 0 THEN
0
ELSE
round(zbkc.zbkc / xl4, 4)
END cxb,
zkc.zkc,
CASE
WHEN xl4 = 0 THEN
0
ELSE
round(zkc.zkc / xl4, 4)
END zcxb,
round(CASE
WHEN zkc.zkc = 0 THEN
0
ELSE
(zkc.zkc - zbkc.zbkc) / zkc.zkc
END,
4) fdkcbfb,
nvl(xl4, 0) * xs.xs - zbkc.zbkc bhl,
w.midqty,
v.cashtype,
nvl(h.sumpendingqty, 0) sumpendingqty,
s6.zdxl,
s6.zdxsje,
w.purstatus,
w.salestatus,
zkc.xsje,
zkc.zkcje,
zbkc.zbkcje,
w.purtax,
wc.classcode jx,
wb.fileno,
w.inittime,
we.we_num03,
we.we_lchar03,
w.store_req,
we.we_lchar02,
w.warningdays,
CASE
WHEN wc2.classcode = '3302' THEN
'是'
ELSE
'否'
END otc,
wc3.classcode,
w.waremark,
fl.vendorno1 gysbh,
fl.vendorno1,
w.lastvencusno,
fl.vendorno2,
1000 fdbh
FROM t_ware w
JOIN d_abcfl fl
ON w.wareid = fl.wareid
JOIN t_ware_class_base wc
ON w.wareid = wc.wareid
AND wc.classgroupno = '87'
JOIN d_bhxs xs
ON wc.classcode = xs.classcode
AND xs.abc = fl.fl
LEFT JOIN (SELECT h.compid,
h.wareid,
SUM(h.sumqty) zkc,
SUM(h.sumqty * h.storepurprice) zkcje,
SUM(h.lastyqty) xl1,
SUM(h.lastm3qty) xl5,
SUM(h.lastm2qty) xl3,
SUM(h.lastmqty) xl4,
SUM(h.lastmqty * h.storepurprice) xsje
FROM t_store_h h
GROUP BY h.compid, h.wareid) zkc
ON w.wareid = zkc.wareid
AND w.compid = zkc.compid
LEFT JOIN (SELECT h.compid,
h.wareid,
SUM(h.sumqty) zbkc,
SUM(h.sumqty * h.storepurprice) zbkcje
FROM t_store_h h
WHERE h.busno = 1000
GROUP BY h.compid, h.wareid) zbkc
ON w.compid = zbkc.compid
AND w.wareid = zbkc.wareid
LEFT JOIN (SELECT rs.compid, rs.wareid, SUM(rs.wareqty) xl2
FROM t_rpt_sale rs
WHERE rs.accdate >= trunc(add_months(SYSDATE, -12) + 31)
AND rs.accdate <= trunc(add_months(SYSDATE, -12) + 60)
GROUP BY rs.compid, rs.wareid) s2
ON w.compid = s2.compid
AND w.wareid = s2.wareid
LEFT JOIN (SELECT rs.compid,
rs.wareid,
SUM(rs.wareqty) zdxl,
SUM(rs.netsum) zdxsje
FROM t_rpt_sale rs
WHERE rs.accdate >= to_date('2016/8/8', 'yyyy-mm-dd')
AND rs.accdate <= to_date('2016/9/7', 'yyyy-mm-dd')
GROUP BY rs.compid, rs.wareid) s6
ON w.compid = s6.compid
AND w.wareid = s6.wareid
LEFT JOIN t_vencus v
ON w.lastvencusno = v.vencusno
AND w.compid = v.compid
LEFT JOIN t_store_h h
ON w.compid = h.compid
AND w.wareid = h.wareid
AND h.busno = 1000, t_ware_saleprice ws, t_ware_base wb,
t_ware_ext we, t_ware_class_base wc2, t_ware_class_base wc3
WHERE ws.salegroupid = 1
AND w.compid = ws.compid
AND w.wareid = ws.wareid
AND w.wareid = wb.wareid
AND w.wareid = we.wareid
AND w.compid = we.compid
AND w.wareid = wc2.wareid
AND wc2.classgroupno = '33'
AND w.wareid = wc3.wareid
AND wc3.classgroupno = '01'
AND w.compid = 2

auto trace結果如下：
| 0 | SELECT STATEMENT | | 397
| 172K| 3934 (1)| 00:00:48 |

|* 1 | HASH JOIN | | 397
| 172K| 3934 (1)| 00:00:48 |

|* 2 | HASH JOIN | | 332
| 140K| 3780 (1)| 00:00:46 |

| 3 | NESTED LOOPS | | 298
| 122K| 3626 (1)| 00:00:44 |

| 4 | NESTED LOOPS | | 298
| 122K| 3626 (1)| 00:00:44 |

| 5 | NESTED LOOPS OUTER | | 255
| 101K| 3116 (1)| 00:00:38 |

|* 6 | HASH JOIN | | 255
| 99195 | 3116 (1)| 00:00:38 |

|* 7 | HASH JOIN | | 255
| 93585 | 3064 (1)| 00:00:37 |

|* 8 | HASH JOIN RIGHT OUTER | | 255
| 89760 | 2994 (1)| 00:00:36 |

|* 9 | TABLE ACCESS FULL | T_VENCUS | 558
| 6138 | 18 (0)| 00:00:01 |

| 10 | NESTED LOOPS OUTER | | 255
| 86955 | 2976 (1)| 00:00:36 |

|* 11 | HASH JOIN OUTER | | 255
| 83130 | 2540 (1)| 00:00:31 |

| 12 | NESTED LOOPS OUTER | | 255
| 53295 | 1018 (1)| 00:00:13 |

|* 13 | HASH JOIN | | 255
| 45645 | 253 (1)| 00:00:04 |

| 14 | TABLE ACCESS FULL | D_ABCFL | 5225
| 36575 | 8 (0)| 00:00:01 |

|* 15 | HASH JOIN RIGHT OUTER | | 1445
| 242K| 245 (1)| 00:00:03 |

| 16 | VIEW | | 1
| 52 | 0 (0)| 00:00:01 |

| 17 | HASH GROUP BY | | 1
| 61 | 0 (0)| 00:00:01 |

|* 18 | TABLE ACCESS BY INDEX ROWID| T_RPT_SALE | 1
| 61 | 0 (0)| 00:00:01 |

| 19 | INDEX FULL SCAN | I_U_SALE_TWO | 1
| | 0 (0)| 00:00:01 |

|* 20 | HASH JOIN | | 1445
| 169K| 244 (1)| 00:00:03 |

|* 21 | HASH JOIN | | 1445
| 33235 | 158 (1)| 00:00:02 |

| 22 | TABLE ACCESS FULL | D_BHXS | 30
| 330 | 4 (0)| 00:00:01 |

|* 23 | TABLE ACCESS FULL | T_WARE_CLASS_BASE | 5631
| 67572 | 154 (1)| 00:00:02 |

|* 24 | TABLE ACCESS FULL | T_WARE | 9860
| 934K| 86 (0)| 00:00:02 |

| 25 | VIEW PUSHED PREDICATE | | 1
| 30 | 3 (0)| 00:00:01 |

| 26 | SORT GROUP BY | | 1
| 19 | 3 (0)| 00:00:01 |

|* 27 | FILTER | |
| | | |

| 28 | TABLE ACCESS BY INDEX ROWID | T_STORE_H | 1
| 19 | 3 (0)| 00:00:01 |

|* 29 | INDEX UNIQUE SCAN | PK_T_STORE_H | 1
| | 2 (0)| 00:00:01 |

| 30 | VIEW | | 4942
| 564K| 1523 (2)| 00:00:19 |

| 31 | HASH GROUP BY | | 4942
| 115K| 1523 (2)| 00:00:19 |

|* 32 | TABLE ACCESS FULL | T_STORE_H | 487
K| 11M| 1510 (1)| 00:00:19 |

| 33 | TABLE ACCESS BY INDEX ROWID | T_STORE_H | 1
| 15 | 2 (0)| 00:00:01 |

|* 34 | INDEX UNIQUE SCAN | PK_T_STORE_H | 1
| | 1 (0)| 00:00:01 |

|* 35 | TABLE ACCESS FULL | T_WARE_EXT | 9823
| 143K| 69 (0)| 00:00:01 |

| 36 | TABLE ACCESS FULL | T_WARE_BASE | 9860
| 211K| 52 (0)| 00:00:01 |

| 37 | VIEW PUSHED PREDICATE | | 1
| 17 | 0 (0)| 00:00:01 |

| 38 | SORT GROUP BY | | 1
| 48 | 0 (0)| 00:00:01 |

|* 39 | FILTER | |
| | | |

|* 40 | TABLE ACCESS BY INDEX ROWID | T_RPT_SALE | 1
| 48 | 0 (0)| 00:00:01 |

|* 41 | INDEX RANGE SCAN | I_U_SALE_TWO | 1
| | 0 (0)| 00:00:01 |

|* 42 | INDEX UNIQUE SCAN | PK_T_WARE_SALEPRICE | 1
| | 1 (0)| 00:00:01 |

| 43 | TABLE ACCESS BY INDEX ROWID | T_WARE_SALEPRICE | 1
| 15 | 2 (0)| 00:00:01 |

|* 44 | TABLE ACCESS FULL | T_WARE_CLASS_BASE | 9447
| 110K| 154 (1)| 00:00:02 |

|* 45 | TABLE ACCESS FULL | T_WARE_CLASS_BASE | 10191
| 119K| 154 (1)| 00:00:02 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("W"."WAREID"="WC2"."WAREID")
2 - access("W"."WAREID"="WC3"."WAREID")
6 - access("W"."WAREID"="WB"."WAREID")
7 - access("W"."WAREID"="WE"."WAREID" AND "W"."COMPID"="WE"."COMPID")
8 - access("W"."COMPID"="V"."COMPID"(+) AND "W"."LASTVENCUSNO"="V"."VENCUSNO"
(+))

9 - filter("V"."COMPID"(+)=2)
11 - access("W"."COMPID"="ZKC"."COMPID"(+) AND "W"."WAREID"="ZKC"."WAREID"(+))
13 - access("XS"."ABC"="FL"."FL" AND "W"."WAREID"="FL"."WAREID")
15 - access("W"."WAREID"="S6"."WAREID"(+) AND "W"."COMPID"="S6"."COMPID"(+))
18 - filter("RS"."COMPID"=2 AND "RS"."ACCDATE">=TO_DATE(' 2016-08-08 00:00:00'
, 'syyyy-mm-dd

hh24:mi:ss') AND "RS"."ACCDATE"<=TO_DATE(' 2016-09-07 00:00:00', '
syyyy-mm-dd hh24:mi:ss'))

20 - access("W"."WAREID"="WC"."WAREID")
21 - access("WC"."CLASSCODE"="XS"."CLASSCODE")
23 - filter("WC"."CLASSCODE" IS NOT NULL AND "WC"."CLASSGROUPNO"='87')
24 - filter("W"."COMPID"=2)
27 - filter("W"."COMPID"=2)
29 - access("H"."COMPID"=2 AND "H"."BUSNO"=1000 AND "H"."WAREID"="W"."WAREID")
32 - filter("H"."COMPID"=2)
34 - access("H"."COMPID"(+)=2 AND "H"."BUSNO"(+)=1000 AND "W"."WAREID"="H"."WA
REID"(+))

35 - filter("WE"."COMPID"=2)
39 - filter(TRUNC(ADD_MONTHS(SYSDATE@!,-12)+60)>=TRUNC(ADD_MONTHS(SYSDATE@!,-1
2)+31) AND "W"."COMPID"=2)

40 - filter("RS"."COMPID"=2 AND "RS"."ACCDATE">=TRUNC(ADD_MONTHS(SYSDATE@!,-12
)+31) AND

"RS"."ACCDATE"<=TRUNC(ADD_MONTHS(SYSDATE@!,-12)+60))
41 - access("RS"."WAREID"="W"."WAREID")
42 - access("WS"."COMPID"=2 AND "WS"."SALEGROUPID"=1 AND "W"."WAREID"="WS"."WA
REID")

44 - filter("WC3"."CLASSGROUPNO"='01')
45 - filter("WC2"."CLASSGROUPNO"='33')

統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
91720 consistent gets
0 physical reads
0 redo size
784821 bytes sent via SQL*Net to client
4348 bytes received via SQL*Net from client
350 SQL*Net roundtrips to/from client
10450 sorts (memory)
0 sorts (disk)
5224 rows processed

這個SQL幾個表都是全表掃描，由於是查詢的所有商品的資訊（零售行業），所以調整表掃描方式等途徑想提升效率提升會很有限。

從這個執行計劃來看，子檢視zkc實際有幾萬條記錄，T_WARE資料也有幾千條。CBO選擇了NESTED LOOP進行表連線。由於資料量較大，這裡採用HASH JOIN應當效率較高

強制CBO選擇HASH JOIN
SELECT /*+ USE_HASH(zkc) */ ****

hint加入之後，效果很顯著，原來需要6分多才能執行完成，採用HASH JOIN之後，15秒不到就執行完成

前後資料記錄有差別是因為寫這個部落格的時候，環境已經變化。
但是可以看到，邏輯讀的數量已經不是一個數量級的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8520577/viewspace-2124846/，如需轉載，請註明出處，否則將追究法律責任。

使用hint來調優sql語句
2014-11-27
SQL
透過使用hint unnest調優sql語句
2015-02-24
SQL
通過使用hint unnest調優sql語句
2015-02-06
SQL
一個SQL語句的優化
2016-04-15
SQL優化
sql調優一例---索引排序hint
2012-04-23
SQL索引排序
【sql調優之執行計劃】使用hint(五)Hint for parallel
2010-09-25
SQLParallel
SQL語句優化方法用hint的30種方法
2009-01-11
SQL優化
一條sql語句的建議調優分析
2015-06-19
SQL
使用SQL調整顧問進行語句優化
2014-02-12
SQL優化
SQL Server調優系列玩轉篇三（利用索引提示（Hint）引導語句最大優化執行）
2015-03-19
SQLServer索引優化
SQL Server調優系列玩轉篇（如何利用查詢提示（Hint）引導語句執行）
2015-01-21
SQLServer
ORACLE常用SQL最佳化hint語句
2007-11-12
OracleSQL
oracle 通過sql profile為sql語句加hint
2015-04-26
OracleSQL
【sql調優之執行計劃】使用hint(一)Hints for Optimization Approaches and Goals
2010-08-25
SQLAPPGo
【sql調優之執行計劃】使用hint(四)Hints for Join
2010-09-25
SQL
ORACLE 通過SPM為SQL語句加HINT
2016-11-20
OracleSQL
一條sql語句的優化
2005-04-13
SQL優化
【sql調優之執行計劃】使用hint(三)Hints for Query Transformations
2010-09-08
SQLORM
【sql調優之執行計劃】使用hint(二)Hints for Access Paths
2010-09-01
SQL
SQL語句優化
2019-05-08
SQL優化
SQL語句使用總結(一)
2012-09-08
SQL
【sql調優之執行計劃】使用hint(六) append and noappend
2010-10-08
SQLAPP
MYSQL SQL語句優化
2019-02-22
MySql優化
sql語句效能優化
2021-01-03
SQL優化
SQL語句的優化
2017-11-14
SQL優化
求助：SQL語句優化
2009-03-03
SQL優化
生產環境大型sql語句調優實戰第一篇（一）
2014-11-26
SQL
一個使用SQL語句解決的小問題
2012-06-26
SQL
SQL Server優化之SQL語句優化
2016-01-12
SQLServer優化
MySQL 52個SQL效能優化策略SQL語句彙總
2022-01-17
MySql優化
一條SQL語句的優化過程
2004-09-07
SQL優化
一次sql語句優化的反思
2007-05-08
SQL優化
調整oracle的一個pga引數,讓一個sql語句執行飛快
2016-12-03
OracleSQL
soar-PHP - SQL 語句優化器和重寫器的 PHP 擴充套件包、方便框架中 SQL 語句調優
2019-07-09
PHPSQL優化套件框架
MySQL之SQL語句優化
2022-05-25
MySql優化
sql語句的優化分析
2014-04-25
SQL優化
SQL語句優化（轉載）
2013-09-01
SQL優化
常用SQL語句優化技巧
2015-07-22
SQL優化

使用USE_HASH Hint調優一個SQL語句

相關文章