1、一个哥们 QQ 问我，这个 SQL 怎么优化，它要跑 160 秒 view plaincopy to clipboardprint?1. SQL explain plan for select a.so_region_code so_region_code, 2. 2 a.so_county_code so_county_code, 3. 3 a.so_org_id so_org_id, 4. 4 d.org_type_id org_type_id, 5. 5 a.op_id op_id, 6. 6 nvl(c.brand, 0) brand, 7. 7 e.res_code, 8. 8 a.
2、busi_code, 9. 9 a.so_nbr, 10. 10 decode(a.isnormal, 11. 11 2, 12. 12 -count(distinct a.so_nbr), 13. 13 0, 14. 14 count(distinct a.so_nbr), 15. 15 0) so_amount, 16. 16 sum(decode(b.book_item_id, 23000002, item_total, 0) / 100 sim_fee, 17. 17 sum(decode(b.book_item_id, 23000001, item_total, 0) / 100 s
3、im_fee_add, 18. 18 sum(decode(b.book_item_id, 27000003, item_total, 0) / 100 sim_fee_discount, 19. 19 sum(decode(b.book_item_id, 21000013, 0, b.item_total) / 100 total_fee 20. 20 from zk.cm_busi_201108 a, 21. 21 zk.cm_busi_charge_201108 b, 22. 22 zk.cm_user c, 23. 23 xg.sys_organizations d, 24. 24 z
4、y.res_sim e 25. 25 where a.so_nbr = b.so_nbr(+) 26. 26 and a.serv_id = c.serv_id 27. 27 and c.sim_id = e.sim_id 28. 28 and a.so_org_id = d.org_id 29. 29 and (b.book_item_id in (23000001, 23000002, 27000003) or 30. 30 a.busi_code in (1, 31. 31 2, 32. 32 4, 33. 33 5, 34. 34 8, 35. 35 11, 36. 36 14, 37
5、. 37 15, 38. 38 17, 39. 39 18, 40. 40 19, 41. 41 21, 42. 42 24, 43. 43 25, 44. 44 28, 45. 45 99, 46. 46 101, 47. 47 104, 48. 48 105, 49. 49 201, 50. 50 204, 51. 51 205, 52. 52 206, 53. 53 2201, 54. 54 1023, 55. 55 1006, 56. 56 3312, 57. 57 2251) 58. 58 and a.op_id != 71010264 59. 59 and a.so_date =
6、to_date(20110831000000, yyyymmddhh24miss) 60. 60 and a.so_date 78. SQL select * from table(dbms_xplan.display); 79. 80. PLAN_TABLE_OUTPUT 81. - 82. 83. - 84. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop | 85. - 86. | 0 | SELECT STATEMENT | | 59 | 11741 | 1703 (1)| | | 87. | 1
7、| SORT GROUP BY | | 59 | 11741 | 1703 (1)| | | 88. |* 2 | FILTER | | | | | | | 89. |* 3 | HASH JOIN OUTER | | | | | | | 90. | 4 | TABLE ACCESS BY LOCAL INDEX ROWID | RES_SIM | 1 | 26 | 32 (4)| | | 91. | 5 | NESTED LOOPS | | 46 | 7820 | 1670 (1)| | | 92. | 6 | NESTED LOOPS | | 49 | 7056 | 146 (2)| |
8、| 93. | 7 | NESTED LOOPS | | 46 | 5244 | 53 (2)| | | 94. |* 8 | TABLE ACCESS BY INDEX ROWID | CM_BUSI_201108 | 46 | 4784 | 7 (15)| | | 95. |* 9 | INDEX RANGE SCAN | DX_BUSI_SO_DATE_201108 | 166K| | 3 (34)| | | 96. | 10 | TABLE ACCESS BY INDEX ROWID | SYS_ORGANIZATIONS | 1 | 10 | 2 (50)| | | 97. |* 1
9、1 | INDEX UNIQUE SCAN | PK_SYS_ORGANIZATIONS | 1 | | | | | 98. | 12 | TABLE ACCESS BY GLOBAL INDEX ROWID| CM_USER | 1 | 30 | 3 (34)| ROWID | ROW L | 99. |* 13 | INDEX UNIQUE SCAN | PK_ZK_CM_USER | 1 | | 2 (50)| | | 100. | 14 | PARTITION RANGE ALL | | | | | 1 | 10 | 101. |* 15 | INDEX RANGE SCAN | ID
10、X_SIM_SIM | 1 | | 31 (4)| 1 | 10 | 102. | 16 | TABLE ACCESS FULL | CM_BUSI_CHARGE_201108 | 474 | 13746 | 32 (4)| | | 103. - 104. 105. Predicate Information (identified by operation id): 106. - 107. 108. 2 -filter(“B“.“BOOK_ITEM_ID“=23000001 OR “B“.“BOOK_ITEM_ID“=23000002 OR “B“.“BOOK_ITEM_ID“=27000
11、109. “A“.“BUSI_CODE“=1 OR “A“.“BUSI_CODE“=2 OR “A“.“BUSI_CODE“=4 OR “A“.“BUSI_CODE“=5 OR “A“.“BUSI 110. “A“.“BUSI_CODE“=11 OR “A“.“BUSI_CODE“=14 OR “A“.“BUSI_CODE“=15 OR “A“.“BUSI_CODE“=17 OR“A“.“ 111. “A“.“BUSI_CODE“=19 OR “A“.“BUSI_CODE“=21 OR “A“.“BUSI_CODE“=24 OR “A“.“BUSI_CODE“=25 OR“A“.“ 112.
12、“A“.“BUSI_CODE“=99 OR “A“.“BUSI_CODE“=101 OR “A“.“BUSI_CODE“=104 OR “A“.“BUSI_CODE“=105OR “A 113. “A“.“BUSI_CODE“=204 OR “A“.“BUSI_CODE“=205 OR “A“.“BUSI_CODE“=206 OR “A“.“BUSI_CODE“=1006 OR 114. “A“.“BUSI_CODE“=2201 OR “A“.“BUSI_CODE“=2251 OR “A“.“BUSI_CODE“=3312) 115. 3 - access(“A“.“SO_NBR“=“B“.“
13、SO_NBR“(+) 116. 8 - filter(“A“.“SO_COUNTY_CODE“=7111 AND “A“.“OP_ID“=TO_DATE( 2011-08-31 00:00:00, 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(“A“.“SO_ORG_ID“=“D“.“ORG_ID“) 120. 13 - access(“A“.“SERV_ID“=“C“.“SERV_ID“) 121. 15 - access(“C“
14、.“SIM_ID“=“E“.“SIM_ID“) 122. 123. 已选择 40 行。 124. 125. 已用时间: 00: 00: 00.20 SQL explain plan for select a 2 a.so_county3 a.so_org_id 4 d.org_type_5 a.op_id op_CM_BUSI_201108 是大表，有 3 千多万的数据，CM_USER 也是一个大表，有 3 千多万的数据 其他表都是小表注意观察第 9 行，CBO 认为它返回 166k 的数据，回表的时候又过滤有 filter 过滤，这个时候 CBO 认为它返回 46 行，先不管这 46 行 C
15、BO 计算是对是错，单单就是索引扫描返回 166k 到表CM_BUSI_201108 去做 166k 次应该也很耗费时间。所以给出优化建议 对表 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执行计
16、划如下：view plaincopy to clipboardprint?1. - 2. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop | 3. - 4. | 0 | SELECT STATEMENT | | 17 | 3264 | 635 (1)| | | 5. | 1 | TABLE ACCESS BY LOCAL INDEX ROWID | RES_SIM | 1 | 26 | 32 (4)| | | 6. | 2 | NESTED LOOPS | | 17 | 3264 | 635 (1)| |
17、| 7. | 3 | NESTED LOOPS | | 18 | 2988 | 75 (2)| | | 8. |* 4 | FILTER | | | | | | | 9. | 5 | NESTED LOOPS OUTER | | | | | | | 10. | 6 | NESTED LOOPS | | 17 | 1870 | 24 (5)| | | 11. |* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| CM_BUSI_201108 | 17 | 1700 | 7 (15)| 6 | 6 | 12. |* 8 | INDEX RANGE SCAN | YI_
18、XXX | 61917 | | 3 (34)| 6 | 6 | 13. | 9 | TABLE ACCESS BY INDEX ROWID | SYS_ORGANIZATIONS | 1 | 10 | 2 (50)| | | 14. |* 10 | INDEX UNIQUE SCAN | PK_SYS_ORGANIZATIONS | 1 | | | | | 15. |* 11 | INDEX RANGE SCAN | PK_CM_BUSI_CHARGE_201108 | 1 | 26 | 2 (50)| | | 16. | 12 | TABLE ACCESS BY GLOBAL INDEX R
19、OWID | CM_USER | 1 | 30 | 3 (34)| ROWID | ROW L | 17. |* 13 | INDEX UNIQUE SCAN | PK_ZK_CM_USER | 1 | | 2 (50)| | | 18. | 14 | PARTITION RANGE ALL | | | | | 1 | 10 | 19. |* 15 | INDEX RANGE SCAN | IDX_SIM_SIM | 1 | | 31 (4)| 1 | 10 | 20. - 21. 22. Predicate Information (identified by operation id):
20、23. - 24. 25. 4 -filter(“B“.“BOOK_ITEM_ID“=23000001 OR “B“.“BOOK_ITEM_ID“=23000002 OR “B“.“BOOK_ITEM_ID“=27000003)26. 7 - filter(“A“.“OP_ID“=TO_DATE( 2011-08-31 00:00:00, syyyy-mm-dd hh24:mi:ss) AND “A“.“SO_COUNTY_CODE“=7111 AND 28. “A“.“SO_DATE“ 0 ; SELECT * FROM (SELECT A.INVOICE_ID,A.VENDOR_ID, A
21、.INVOICE_NUM,A.INVOICE_AMOUNTB 是一个视图，定义如下：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, 4. AID1.INVOICE_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID, 5. AID1.PREPAY_DISTRIBUTION_ID PREPAY_DISTRIBUTION_ID
22、, 6. AID1.DISTRIBUTION_LINE_NUMBER PREPAY_DIST_NUMBER, 7. (-1) * AID1.AMOUNT PREPAY_AMOUNT_APPLIED, 8. nvl(AID2.PREPAY_AMOUNT_REMAINING, AID2.AMOUNT) PREPAY_AMOUNT_REMAINING, 9. AID1.DIST_CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID, 10. AID1.ACCOUNTING_DATE ACCOUNTING_DATE, 11. AID1.PERIOD_NAME PER
23、IOD_NAME, 12. AID1.SET_OF_BOOKS_ID SET_OF_BOOKS_ID, 13. AID1.DESCRIPTION DESCRIPTION, 14. AID1.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID, 15. AID1.RCV_TRANSACTION_ID RCV_TRANSACTION_ID, 16. AID1.ORG_ID ORG_ID, 17. AI.INVOICE_NUM PREPAY_NUMBER, 18. AI.VENDOR_ID VENDOR_ID, 19. AI.VENDOR_SITE_ID VENDOR_SIT
24、E_ID, 20. ATC.TAX_ID TAX_ID, 21. ATC.NAME TAX_CODE, 22. PH.SEGMENT1 PO_NUMBER, 23. PV.VENDOR_NAME VENDOR_NAME, 24. PV.SEGMENT1 VENDOR_NUMBER, 25. PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE, 26. RSH.RECEIPT_NUM RECEIPT_NUMBER 27. FROM AP_INVOICES AI, 28. AP_INVOICE_DISTRIBUTIONS AID1, 29. AP_INVOICE_DISTR
25、IBUTIONS AID2, 30. AP_TAX_CODES ATC, 31. PO_VENDORS PV, 32. PO_VENDOR_SITES PVS, 33. PO_DISTRIBUTIONS PD, 34. PO_HEADERS PH, 35. PO_LINES PL, 36. PO_LINE_LOCATIONS PLL, 37. RCV_TRANSACTIONS RTXNS, 38. RCV_SHIPMENT_HEADERS RSH, 39. RCV_SHIPMENT_LINES RSL 40. WHERE AID1.PREPAY_DISTRIBUTION_ID = AID2.I
26、NVOICE_DISTRIBUTION_ID 41. AND AI.INVOICE_ID = AID2.INVOICE_ID 42. AND AID1.AMOUNT $ORACLE_HOME/rdbms/admin/utlxpls 2. 3. PLAN_TABLE_OUTPUT 4. - 5. 6. - 7. | Id | Operation | Name | Rows | Bytes | Cost | 8. - 9. | 0 | SELECT STATEMENT | | 1 | 69 | 722 | 10. |* 1 | FILTER | | | | | 11. | 2 | SORT GRO
27、UP BY | | 1 | 69 | 722 | 12. | 3 | NESTED LOOPS OUTER | | 3 | 207 | 697 | 13. |* 4 | TABLE ACCESS FULL | AP_INVOICES_ALL | 3 | 153 | 694 | 14. | 5 | VIEW PUSHED PREDICATE | AP_UNAPPLY_PREPAYS_V | 1 | 18 | 1 | 15. | 6 | NESTED LOOPS | | 1 | 372 | 3 | 16. | 7 | NESTED LOOPS | | 1 | 368 | 3 | 17. | 8 | NESTED LOOPS | | 1 | 361 | 2 | 18. | 9 | NESTED LOOPS | | 1 | 347 | 1 | 19. | 10 | NESTED LOOPS OUTER | | 1 | 334 | 1 | 20. | 11 | NESTED LOOPS OUTER | | 1 | 321 | 1 | 21. | 12 | NESTED LOOPS O
