版权说明：本文档由用户提供并上传，收益归属内容提供方，若内容存在侵权，请进行举报或认领
文档简介
1、一个哥们QQ问我，这个SQL怎么优化，它要跑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千多万的数据，CM_USER也是一个大表，有3千多万的数据 其他表都是小表注意观察第9行，CBO认为它返回166k的数据，回表的时候又过滤有filter过滤，这个时候CBO认为它返回46行，先不管这46行 CBO计算是对是错，单单就是索引扫描返回166k到表CM_BUSI_201108 去做16
92、6k次应该也很耗费时间。所以给出优化建议 对表CM_BUSI_201108进行分区，可以根据SO_DATE做range分区，另外SO_COUNTRY_CODE可以查看值多不多，如果不多可以做 range-list分区他最终只做了range分区，并且让他创建了一个本地有前缀的组合索引(他最开始创建的是global索引，没有起到优化效果)create index YI_XXX ON CM_BUSI_201108(SO_DATE,SO_COUNTRY_CODE) LOCAL执行计划如下：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") 分区之后，需要过滤的数据量大大减少，这样嵌套循环执行的次数也大大减少，最终SQL能在4秒左右跑完，其实这个优化方案并不是最优的，由于不能连接到他的数据库，这个SQL的优化就暂时告一段落。分区对SQL的优化还是非常有帮助的。帮网友调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; 执行计划如下：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. 本站的文档不包含任何第三方提供的附件图纸等，如果需要附件，请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸，网页内容里面会有图纸预览，若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间，仅对用户上传内容的表现方式做保护处理，对用户上传分享的文档内容本身不做任何修改或编辑，并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容，请与我们联系，我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 厂房拆迁合同范例工厂企业
- 印刷制作宣传合同范本
- 俱乐部选手签约合同范本
- 单位购买用车服务合同范本
- 保温工程收购合同范本
- 医疗体系用工合同范本
- 厂家销售任务合同范本
- 养殖场劳动合同范本
- 上海景观园林养护合同范本
- 农村买土地合同范本
- 财务部绩效考核评分规则及绩效考核评分表
- 放射诊疗设备清单
- 供应链中的社会责任
- HDPE缠绕-B型结构壁管施工方案
- 早期教育概论(高职学前教育专业)全套教学课件
- 《AutoCAD 中文版实例教程（AutoCAD 2020） （微课版）（第 2 版）》课件 马连志 第3、4章 基本绘图操作、高级绘图操作
- 幼儿教师职业道德(高职学前教育专业)全套教学课件
- 汽车发动机构造与维修中职PPT完整全套教学课件
- 养老院管理-考核考评
- 苏科版八年级生物下册全册完整课件
- 第四单元复习教学设计 部编版语文七年级上册
评论
0/150
提交评论