版權(quán)說明：本文檔由用戶提供并上傳，收益歸屬內(nèi)容提供方，若內(nèi)容存在侵權(quán)，請進行舉報或認(rèn)領(lǐng)
文檔簡介
1、一個哥們QQ問我，這個SQL怎么優(yōu)化，它要跑160秒 view plaincopy to clipboardprint?1. SQL> explain plan for select a.so_region_code so_region_code, 2. 2 a
2、.so_county_code so_county_code, 3. 3 a.so_org_id so_org_id, 4. 4 &
3、#160; _type_id org_type_id, 5. 5 a.op_id op_id, 6. 6
4、60; nvl(c.brand, 0) brand, 7. 7 e.res_code, 8. 8
5、 a.busi_code, 9. 9 a.so_nbr, 10. 10
6、; decode(a.isnormal, 11. 11 2, 12. 12
7、60; -count(distinct a.so_nbr), 13. 13 0, 14. 14
8、 count(distinct a.so_nbr), 15. 15
9、 0) so_amount, 16. 16 sum(decode(b.book_item_id, 23000002, item_total, 0) / 100 sim_fee, 17. 17
10、 sum(decode(b.book_item_id, 23000001, item_total, 0) / 100 sim_fee_add, 18. 18 &#
11、160; sum(decode(b.book_item_id, 27000003, item_total, 0) / 100 sim_fee_discount, 19. 19 sum(decode(b.book_item_id, 21000013,
12、 0, b.item_total) / 100 total_fee 20. 20 from zk.cm_busi_201108 a, 21. 21
13、60; zk.cm_busi_charge_201108 b, 22. 22 zk.cm_user
14、60; c, 23. 23 xg.sys_organizations d, 24. 24
15、60; zy.res_sim e 25. 25 where a.so_nbr = b.so_nbr(+) 26. 26
16、60; and a.serv_id = c.serv_id 27. 27 and c.sim_id = e.sim_id 28. 28
17、60; and a.so_org_id = _id 29. 29 and (b.book_item_id in (23000001, 23000002, 27000003) or 30. 30 &#
18、160; a.busi_code in (1, 31. 31
19、0; 2, 32. 32 4, 33. &#
20、160;33 5, 34. 34
21、0; 8, 35. 35 &#
22、160; 11, 36. 36
23、;14, 37. 37 15, 38. 38
24、; 17, 39. 39 &#
25、160; 18, 40. 40
26、; 19, 41. 41 21, 42. 42
27、; 24, 43. 43 &#
28、160; 25, 44. 44
29、; 28, 45. 45 99, 46. &
30、#160;46 101, 47. 47 &
31、#160; 104, 48. 48
32、60; 105, 49. 49
33、; 201, 50. 50 204, 51. 51
34、60; 205, 52. 52
35、; 206, 53. 53 &
36、#160; 2201, 54. 54 1023, 55. 55
37、60; 1006, 56. 56
38、0; 3312, 57. 57
39、; 2251) 58. 58 and a.op_id != 71010264 59. 59
40、0; and a.so_date >60. 60 and a.so_date <61. 61 and a.so_county_code =7111 62
41、. 62 and a.so_nbr is not null 63. 63 group by a.so_region_code, 64. 64
42、 a.so_county_code, 65. 65 a.so_org_id, 66. 66&
43、#160; _type_id, 67. 67 a.op_id, 68.
44、 68 c.brand, 69. 69 e.res_code, &#
45、160;70. 70 a.busi_code, 71. 71 a.so_nbr
46、, 72. 72 a.isnormal; 73. 74. 已解釋。 75. 76. 已用時間: 00: 00: 00.03 77. SQL> &
47、#160; 78. SQL> select * from table(dbms_xplan.disp
48、lay); 79. 80. PLAN_TABLE_OUTPUT 81. - 82. 83. - 84. | Id | Operation
49、; | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop | 85. - &
50、#160;86. | 0 | SELECT STATEMENT | &#
51、160; | 59 | 11741 | 1703 (1)| | | 87. | 1 | SORT&
52、#160;GROUP BY |
53、; | 59 | 11741 | 1703 (1)| | | 88. |* 2 | FILTER &
54、#160; |
55、 | | | | | | 89. |* 3
56、;| HASH JOIN OUTER | &#
57、160; | | | | | | 90
58、. | 4 | TABLE ACCESS BY LOCAL INDEX ROWID | RES_SIM | 1 |
59、0; 26 | 32 (4)| | | 91. | 5 | NESTED LOOPS
60、; | | 46 |
61、7820 | 1670 (1)| | | 92. | 6 | NESTED LOOPS
62、60; | | 49 | 7056 |
63、;146 (2)| | | 93. | 7 | NESTED LOOPS &#
64、160; | | 46 | 5244 | 53 &#
65、160;(2)| | | 94. |* 8 | TABLE ACCESS BY INDEX ROWID | CM_BUSI_201108
66、0; | 46 | 4784 | 7 (15)| | | 95. |* 9 |
67、60; INDEX RANGE SCAN | DX_BUSI_SO_DATE_201108 | 166K| | 3
68、 (34)| | | 96. | 10 | TABLE ACCESS BY INDEX ROWID | SYS_ORGANIZATIO
69、NS | 1 | 10 | 2 (50)| | | 97. |* 11 |
70、; INDEX UNIQUE SCAN | PK_SYS_ORGANIZATIONS | 1 | | &
71、#160; | | | 98. | 12 | TABLE ACCESS BY GLOBAL INDEX ROWID|&#
72、160;CM_USER | 1 | 30 | 3 (34)| ROWID | ROW L | 99. |* 13
73、 | INDEX UNIQUE SCAN | PK_ZK_CM_USER |
74、;1 | | 2 (50)| | | 100. | 14 | PARTITION RANGE ALL
75、0; | | | &
76、#160; | | 1 | 10 | 101. |* 15 | INDEX RANGE SCAN
77、 | IDX_SIM_SIM | 1 | | 31
78、 (4)| 1 | 10 | 102. | 16 | TABLE ACCESS FULL
79、60;| CM_BUSI_CHARGE_201108 | 474 | 13746 | 32 (4)| | | 103. - 104. 105. Predicate Inf
80、ormation (identified by operation id): 106. - 107. 108. 2 - filter("B"."BOOK_ITEM_ID"=23000001 OR "B"."BOOK_ITEM_ID"=23000002 OR "B"."BOOK_ITEM
81、_ID"=27000 109. "A"."BUSI_CODE"=1 OR "A"."BUSI_CODE"=2 OR "A"."BUSI_CODE"=4 OR "A"."BUSI_CODE&q
82、uot;=5 OR "A"."BUSI 110. "A"."BUSI_CODE"=11 OR "A"."BUSI_CODE"=14 OR "A"."BUSI_CODE"=15 OR
83、;"A"."BUSI_CODE"=17 OR "A"." 111. "A"."BUSI_CODE"=19 OR "A"."BUSI_CODE"=21 OR "A"."BU
84、SI_CODE"=24 OR "A"."BUSI_CODE"=25 OR "A"." 112. "A"."BUSI_CODE"=99 OR "A"."BUSI_CODE"=101
85、OR "A"."BUSI_CODE"=104 OR "A"."BUSI_CODE"=105 OR "A 113. "A"."BUSI_CODE"=204 OR "A"."BUSI_
86、CODE"=205 OR "A"."BUSI_CODE"=206 OR "A"."BUSI_CODE"=1006 OR 114. "A"."BUSI_CODE"=2201 OR "A&qu
87、ot;."BUSI_CODE"=2251 OR "A"."BUSI_CODE"=3312) 115. 3 - access("A"."SO_NBR"="B"."SO_NBR"(+) 116. 8 - filter("A"."SO_COUNTY_CODE&
88、quot;=7111 AND "A"."OP_ID"<>71010264 AND "A"."SO_NBR" IS NOT NULL) 117. 9 - access("A"."SO_DATE">=TO_DATE(' 2011-08-31 00:00:00', '
89、;syyyy-mm-dd hh24:mi:ss') AND "A"."SO_D 118. 2011-08-31 23:59:59', 'syyyy-mm-dd hh24:mi:ss') 119. 11 - access(&
90、quot;A"."SO_ORG_ID"="D"."ORG_ID") 120. 13 - access("A"."SERV_ID"="C"."SERV_ID") 121. 15 - access("C"."SIM_ID"="E"."SIM_ID&qu
91、ot;) 122. 123. 已選擇40行。 124. 125. 已用時間: 00: 00: 00.20 CM_BUSI_201108 是大表，有3千多萬的數(shù)據(jù)，CM_USER也是一個大表，有3千多萬的數(shù)據(jù) 其他表都是小表注意觀察第9行，CBO認(rèn)為它返回166k的數(shù)據(jù)，回表的時候又過濾有filter過濾，這個時候CBO認(rèn)為它返回46行，先不管這46行 CBO計算是對是錯，單單就是索引掃描返回166k到表CM_BUSI_201108 去做16
92、6k次應(yīng)該也很耗費時間。所以給出優(yōu)化建議 對表CM_BUSI_201108進行分區(qū)，可以根據(jù)SO_DATE做range分區(qū)，另外SO_COUNTRY_CODE可以查看值多不多，如果不多可以做 range-list分區(qū)他最終只做了range分區(qū)，并且讓他創(chuàng)建了一個本地有前綴的組合索引(他最開始創(chuàng)建的是global索引，沒有起到優(yōu)化效果)create index YI_XXX ON CM_BUSI_201108(SO_DATE,SO_COUNTRY_CODE) LOCAL執(zhí)行計劃如下：view plaincopy to clipboardprint?1. - 2. |
93、0;Id | Operation | Name
94、; | Rows | Bytes | Cost (%CPU)| Pstart| Pstop | 3. - 4. | 0 | SELECT STATEMENT
95、0; | | 17 | 3264&
96、#160;| 635 (1)| | | 5. | 1 | TABLE ACCESS BY LOCAL INDEX ROWID | RES_SIM
97、60; | 1 | 26 | 32 (4)| | &#
98、160; | 6. | 2 | NESTED LOOPS | &
99、#160; | 17 | 3264 | 635 (1)| | |&
100、#160; 7. | 3 | NESTED LOOPS | &
101、#160; | 18 | 2988 | 75 (2)| | | 8.
102、 |* 4 | FILTER |
103、 | | | | &#
104、160;| | 9. | 5 | NESTED LOOPS OUTER | &
105、#160; | | | |
106、60; | | 10. | 6 | NESTED LOOPS |
107、 | 17 | 1870 | 24 (5)|
108、60; | | 11. |* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| CM_BUSI_201108 |
109、60; 17 | 1700 | 7 (15)| 6 | 6 | 12. |* 8 | INDEX RANGE SCAN
110、; | YI_XXX | 61917 | | &#
111、160; 3 (34)| 6 | 6 | 13. | 9 | TABLE ACCESS BY INDEX ROWID | SYS_ORGANIZATIO
112、NS | 1 | 10 | 2 (50)| | | 14. |* 10 |
113、; INDEX UNIQUE SCAN | PK_SYS_ORGANIZATIONS | 1 | &#
114、160; | | | | 15. |* 11 | INDEX RANGE SCAN
115、; | PK_CM_BUSI_CHARGE_201108 | 1 | 26 | 2 (50)| |
116、60; | 16. | 12 | TABLE ACCESS BY GLOBAL INDEX ROWID | CM_USER |
117、0; 1 | 30 | 3 (34)| ROWID | ROW L | 17. |* 13 | INDEX UNIQUE SCAN
118、 | PK_ZK_CM_USER | 1 | | 2 (50)|
119、 | | 18. | 14 | PARTITION RANGE ALL | &#
120、160; | | | |
121、0; 1 | 10 | 19. |* 15 | INDEX RANGE SCAN | IDX_SIM_SIM
122、 | 1 | | 31 (4)| 1 | 10 | 20.
123、 - 21. 22. Predicate Information (identified by operation id): 23. - 24. 25. 4 - filter("B"."BOOK_ITEM_ID"=23000001 OR "B"."BOOK_ITEM
124、_ID"=23000002 OR "B"."BOOK_ITEM_ID"=27000003) 26. 7 - filter("A"."OP_ID"<>71010264) 27. 8 - access("A"."SO_DATE">=TO_DATE(' 2011-
125、08-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."SO_COUNTY_CODE"=7111 AND 28. "A"."SO_DATE"<=TO_DATE('
126、;2011-08-31 23:59:59', 'syyyy-mm-dd hh24:mi:ss') 29. filter("A"."SO_COUNTY_CODE"=7111) 30. 10 - access("A"."SO_ORG_ID"="D"."ORG_ID&q
127、uot;) 31. 11 - access("A"."SO_NBR"="B"."SO_NBR"(+) 32. 13 - access("A"."SERV_ID"="C"."SERV_ID") 33. 15 - access("C&q
128、uot;."SIM_ID"="E"."SIM_ID") 分區(qū)之后，需要過濾的數(shù)據(jù)量大大減少，這樣嵌套循環(huán)執(zhí)行的次數(shù)也大大減少，最終SQL能在4秒左右跑完，其實這個優(yōu)化方案并不是最優(yōu)的，由于不能連接到他的數(shù)據(jù)庫，這個SQL的優(yōu)化就暫時告一段落。分區(qū)對SQL的優(yōu)化還是非常有幫助的。幫網(wǎng)友調(diào)SQL原SQL如下(要跑1個多小時)：view plaincopy to clipboardprint?1. SELECT * 2. FROM (SELECT
129、0; A.INVOICE_ID, 3. A.VENDOR_ID, 4. A.INVOICE_NUM, 5.
130、0; A.INVOICE_AMOUNT, 6. A.GL_DATE, 7. A
131、.INVOICE_CURRENCY_CODE, 8. SUM(NVL(B.PREPAY_AMOUNT_APPLIED, 0) PAID_AMOUNT, 9. A.INVOICE
132、_AMOUNT - SUM(NVL(B.PREPAY_AMOUNT_APPLIED, 0) REMAIN 10. FROM ap.AP_INVOICES_ALL A, APPS.AP_UNAPPLY_PREPAYS_V B 11. WHERE
133、60;A.INVOICE_ID = B.INVOICE_ID(+) 12. AND A.ORG_ID = 126 /*:B4*/ 13. AND A.SOURCE = 'OSM IMP
134、ORTED' /*:B3*/ 14. AND A.INVOICE_NUM BETWEEN NVL( /*:B2*/ null, A.INVOICE_NUM) AND 15.
135、60; NVL( /*:B1*/ null, A.INVOICE_NUM) 16. GROUP BY A.INVOICE_ID, 17. A.INVOICE_NUM,
136、60; 18. A.INVOICE_AMOUNT, 19. A.VENDOR_ID, 20.
137、0; A.GL_DATE, 21. A.INVOICE_CURRENCY_CODE) 22. WHERE REMAIN
138、;> 0 B是一個視圖，定義如下：view plaincopy to clipboardprint?1. CREATE OR REPLACE VIEW APPS.AP_UNAPPLY_PREPAYS_V AS 2. SELECT AID1.ROWID ROW_ID, 3. AID1.INVOICE_ID INVOICE_ID, &
139、#160;4. AID1.INVOICE_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID, 5. AID1.PREPAY_DISTRIBUTION_ID PREPAY_DISTRIBUTION_ID, 6. AID1.DISTRIBUTION_LINE
140、_NUMBER PREPAY_DIST_NUMBER, 7. (-1) * AID1.AMOUNT PREPAY_AMOUNT_APPLIED, 8. nvl(AID2.PREPAY_AMOUNT_REMAINING, AID2.AMOUNT) PREPAY_AMOUNT_REMAINING, 9. &#
141、160; AID1.DIST_CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID, 10. AID1.ACCOUNTING_DATE ACCOUNTING_DATE, 11. AID1.PERIOD_NAME PERIOD_NAME, &#
142、160;12. AID1.SET_OF_BOOKS_ID SET_OF_BOOKS_ID, 13. AID1.DESCRIPTION DESCRIPTION, 14. AID1.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID,
143、0;15. AID1.RCV_TRANSACTION_ID RCV_TRANSACTION_ID, 16. AID1.ORG_ID ORG_ID, 17. AI.INVOICE_NUM PREPAY_NUMBER, 18. &#
144、160; AI.VENDOR_ID VENDOR_ID, 19. AI.VENDOR_SITE_ID VENDOR_SITE_ID, 20. ATC.TAX_ID TAX_ID, 21. ATC.NAME&
145、#160;TAX_CODE, 22. PH.SEGMENT1 PO_NUMBER, 23. PV.VENDOR_NAME VENDOR_NAME, 24. PV.SEGMENT1 VENDOR_NUMBER, 25.
146、 PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE, 26. RSH.RECEIPT_NUM RECEIPT_NUMBER 27. FROM AP_INVOICES &
147、#160;AI, 28. AP_INVOICE_DISTRIBUTIONS AID1, 29. AP_INVOICE_DISTRIBUTIONS AID2, 30. AP_TAX_CODES
148、 ATC, 31. PO_VENDORS PV, 32. PO_VENDOR_SITES &#
149、160; PVS, 33. PO_DISTRIBUTIONS PD, 34. PO_HEADERS
150、60; PH, 35. PO_LINES PL, 36. PO_LINE_LOCATIONS
151、 PLL, 37. RCV_TRANSACTIONS RTXNS, 38. RCV_SHIPMENT_HEADERS RSH, 39.
152、0; RCV_SHIPMENT_LINES RSL 40. WHERE AID1.PREPAY_DISTRIBUTION_ID = AID2.INVOICE_DISTRIBUTION_ID 41. AND AI.INVOICE_ID = AID2.INVOICE_ID 42.
153、0; AND AID1.AMOUNT < 0 43. AND nvl(AID1.REVERSAL_FLAG, 'N') != 'Y' 44. AND AID1.TAX_CODE_ID = ATC.TAX_ID(+) 45. AND AID1.LINE_TY
154、PE_LOOKUP_CODE = 'PREPAY' 46. AND AI.VENDOR_ID = PV.VENDOR_ID 47. AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID 48. AND AID1.PO_DISTRIBUTION_ID = PD.PO_D
155、ISTRIBUTION_ID(+) 49. AND PD.PO_HEADER_ID = PH.PO_HEADER_ID(+) 50. AND PD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID(+) 51. AND PLPLL.PO_LINE_ID = PL.PO_LINE_ID(+) &#
156、160;52. AND AID1.RCV_TRANSACTION_ID = RTXNS.TRANSACTION_ID(+) 53. AND RTXNS.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID(+) 54. AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID(+);&
157、#160; 執(zhí)行計劃如下：view plaincopy to clipboardprint?1. SQL> $ORACLE_HOME/rdbms/admin/utlxpls 2. 3. PLAN_TABLE_OUTPUT 4. - 5. 6. - 7. | Id | Operation &
158、#160; | Name &#
159、160; | Rows | Bytes | Cost | 8. - 9. | 0 | SELECT STATEMENT &#
160、160; | | 1 | 69 |&
161、#160; 722 | 10. |* 1 | FILTER &
162、#160;| | | |
163、60; | 11. | 2 | SORT GROUP BY | &
164、#160; | 1 | 69 | 722 | 12. |
165、0;3 | NESTED LOOPS OUTER |
166、60; | 3 | 207 | 697 | 13. |* 4 | TABLE ACCESS FULL
167、; | AP_INVOICES_ALL | 3 | 153 |
168、 694 | 14. | 5 | VIEW PUSHED PREDICATE | AP_UNAPPLY_PREPAYS_V
169、60; | 1 | 18 | 1 | 15. | 6 | NESTED LOOPS &
170、#160; | |
171、; 1 | 372 | 3 | 16. | 7 | NESTED LOOPS
172、60; | | 1 | 368 |&
173、#160; 3 | 17. | 8 | NESTED LOOPS |
174、0; | 1 | 361 | 2 | 1
175、8. | 9 | NESTED LOOPS |
176、 | 1 | 347 | 1 | 19. | 10 | &
177、#160; NESTED LOOPS OUTER | &#
178、160; | 1 | 334 | 1 | 20. | 11 | NESTED LOOPS OUTER
179、; | |
180、60;1 | 321 | 1 | 21. | 12 | NESTED LOOPS OUTER &
181、#160;| | 1 | 295 | 1 |
182、60; 22. | 13 | NESTED LOOPS OUTER | &#
183、160; | 1 | 269 | 1 | 23. | 14 |
184、60; NESTED LOOPS OUTER | | 1 | 243 | 1 | 24. | 15 | &
溫馨提示
- 1. 本站所有資源如無特殊說明，都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
- 2. 本站的文檔不包含任何第三方提供的附件圖紙等，如果需要附件，請聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
- 3. 本站RAR壓縮包中若帶圖紙，網(wǎng)頁內(nèi)容里面會有圖紙預(yù)覽，若沒有圖紙預(yù)覽就沒有圖紙。
- 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
- 5. 人人文庫網(wǎng)僅提供信息存儲空間，僅對用戶上傳內(nèi)容的表現(xiàn)方式做保護處理，對用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯，并不能對任何下載內(nèi)容負(fù)責(zé)。
- 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容，請與我們聯(lián)系，我們立即糾正。
- 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時也不承擔(dān)用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。
最新文檔
- 商場泔水清運專項服務(wù)合同
- 二零二五年度寶石匠人珠寶店珠寶行業(yè)法律咨詢合同
- 廚衛(wèi)改造工程合同樣本
- 旅游規(guī)劃與設(shè)計行業(yè)智能化旅游目的地打造方案
- 電子通訊網(wǎng)絡(luò)工程指南
- 職業(yè)病診斷與鑒定作業(yè)指導(dǎo)書
- 三農(nóng)產(chǎn)品流通體系國際化與走出去戰(zhàn)略作業(yè)指導(dǎo)書
- 三農(nóng)田灌溉管理方案
- 多應(yīng)用臨時借款合同常用
- 房產(chǎn)歸男方無債務(wù)離婚協(xié)議書
- 福建省泉州市晉江市2024-2025學(xué)年七年級上學(xué)期期末生物學(xué)試題（含答案）
- 醫(yī)美注射類知識培訓(xùn)課件
- 2025年春新人教版物理八年級下冊課件 第十章 浮力 第4節(jié) 跨學(xué)科實踐：制作微型密度計
- 2025年廣電網(wǎng)絡(luò)公司工作計劃（3篇）
- 貨運車輛駕駛員服務(wù)標(biāo)準(zhǔn)化培訓(xùn)考核試卷
- 銀行行長2024年個人年終總結(jié)
- 財務(wù)BP經(jīng)營分析報告
- 三年級上冊體育課教案
- 2024高考物理二輪復(fù)習(xí)電學(xué)實驗專項訓(xùn)練含解析
- 暴發(fā)性心肌炎的診斷與治療
- 2024年全國統(tǒng)一高考英語試卷(新課標(biāo)Ⅰ卷)含答案
評論
0/150
提交評論