ORACLE-SQL优化案例.doc

一个哥们QQ问我，这个SQL怎么优化，它要跑160秒 view plaincopy to clipboardprint?1. SQLexplainplanforselecta.so_region_codeso_region_code,2. 2a.so_county_codeso_county_code,3. 3a.so_org_idso_org_id,4. 4_type_idorg_type_id,5. 5a.op_idop_id,6. 6nvl(c.brand,0)brand,7. 7e.res_code,8. 8a.busi_code,9. 9a.so_nbr,10. 10decode(a.isnormal,11. 112,12. 12-count(distincta.so_nbr),13. 130,14. 14count(distincta.so_nbr),15. 150)so_amount,16. 16sum(decode(b.book_item_id,23000002,item_total,0)/100sim_fee,17. 17sum(decode(b.book_item_id,23000001,item_total,0)/100sim_fee_add,18. 18sum(decode(b.book_item_id,27000003,item_total,0)/100sim_fee_discount,19. 19sum(decode(b.book_item_id,21000013,0,b.item_total)/100total_fee20. 20fromzk.cm_busi_201108a,21. 21zk.cm_busi_charge_201108b,22. 22zk.cm_userc,23. 23xg.sys_organizationsd,24. 24zy.res_sime25. 25wherea.so_nbr=b.so_nbr(+)26. 26anda.serv_id=c.serv_id27. 27andc.sim_id=e.sim_id28. 28anda.so_org_id=_id29. 29and(b.book_item_idin(23000001,23000002,27000003)or30. 30a.busi_codein(1,31. 312,32. 324,33. 335,34. 348,35. 3511,36. 3614,37. 3715,38. 3817,39. 3918,40. 4019,41. 4121,42. 4224,43. 4325,44. 4428,45. 4599,46. 46101,47. 47104,48. 48105,49. 49201,50. 50204,51. 51205,52. 52206,53. 532201,54. 541023,55. 551006,56. 563312,57. 572251)58. 58anda.op_id!=7101026459. 59anda.so_date=to_date(20110831000000,yyyymmddhh24miss)60. 60anda.so_date78. SQLselect*fromtable(dbms_xplan.display);79. 80. PLAN_TABLE_OUTPUT81. -82. 83. -84. |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Pstart|Pstop|85. -86. |0|SELECTSTATEMENT|59|11741|1703(1)|87. |1|SORTGROUPBY|59|11741|1703(1)|88. |*2|FILTER|89. |*3|HASHJOINOUTER|90. |4|TABLEACCESSBYLOCALINDEXROWID|RES_SIM|1|26|32(4)|91. |5|NESTEDLOOPS|46|7820|1670(1)|92. |6|NESTEDLOOPS|49|7056|146(2)|93. |7|NESTEDLOOPS|46|5244|53(2)|94. |*8|TABLEACCESSBYINDEXROWID|CM_BUSI_201108|46|4784|7(15)|95. |*9|INDEXRANGESCAN|DX_BUSI_SO_DATE_201108|166K|3(34)|96. |10|TABLEACCESSBYINDEXROWID|SYS_ORGANIZATIONS|1|10|2(50)|97. |*11|INDEXUNIQUESCAN|PK_SYS_ORGANIZATIONS|1|98. |12|TABLEACCESSBYGLOBALINDEXROWID|CM_USER|1|30|3(34)|ROWID|ROWL|99. |*13|INDEXUNIQUESCAN|PK_ZK_CM_USER|1|2(50)|100. |14|PARTITIONRANGEALL|1|10|101. |*15|INDEXRANGESCAN|IDX_SIM_SIM|1|31(4)|1|10|102. |16|TABLEACCESSFULL|CM_BUSI_CHARGE_201108|474|13746|32(4)|103. -104. 105. PredicateInformation(identifiedbyoperationid):106. -107. 108. 2-filter(B.BOOK_ITEM_ID=23000001ORB.BOOK_ITEM_ID=23000002ORB.BOOK_ITEM_ID=27000109. A.BUSI_CODE=1ORA.BUSI_CODE=2ORA.BUSI_CODE=4ORA.BUSI_CODE=5ORA.BUSI110. A.BUSI_CODE=11ORA.BUSI_CODE=14ORA.BUSI_CODE=15ORA.BUSI_CODE=17ORA.111. A.BUSI_CODE=19ORA.BUSI_CODE=21ORA.BUSI_CODE=24ORA.BUSI_CODE=25ORA.112. A.BUSI_CODE=99ORA.BUSI_CODE=101ORA.BUSI_CODE=104ORA.BUSI_CODE=105ORA113. A.BUSI_CODE=204ORA.BUSI_CODE=205ORA.BUSI_CODE=206ORA.BUSI_CODE=1006OR114. A.BUSI_CODE=2201ORA.BUSI_CODE=2251ORA.BUSI_CODE=3312)115. 3-access(A.SO_NBR=B.SO_NBR(+)116. 8-filter(A.SO_COUNTY_CODE=7111ANDA.OP_ID71010264ANDA.SO_NBRISNOTNULL)117. 9-access(A.SO_DATE=TO_DATE(2011-08-3100:00:00,syyyy-mm-ddhh24:mi:ss)ANDA.SO_D118. 2011-08-3123:59:59,syyyy-mm-ddhh24: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.SIM_ID=E.SIM_ID)122. 123. 已选择40行。124. 125. 已用时间:00:00:00.20CM_BUSI_201108 是大表，有3千多万的数据，CM_USER也是一个大表，有3千多万的数据 其他表都是小表注意观察第9行，CBO认为它返回166k的数据，回表的时候又过滤有filter过滤，这个时候CBO认为它返回46行，先不管这46行 CBO计算是对是错，单单就是索引扫描返回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执行计划如下：view plaincopy to clipboardprint?1. -2. |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Pstart|Pstop|3. -4. |0|SELECTSTATEMENT|17|3264|635(1)|5. |1|TABLEACCESSBYLOCALINDEXROWID|RES_SIM|1|26|32(4)|6. |2|NESTEDLOOPS|17|3264|635(1)|7. |3|NESTEDLOOPS|18|2988|75(2)|8. |*4|FILTER|9. |5|NESTEDLOOPSOUTER|10. |6|NESTEDLOOPS|17|1870|24(5)|11. |*7|TABLEACCESSBYLOCALINDEXROWID|CM_BUSI_201108|17|1700|7(15)|6|6|12. |*8|INDEXRANGESCAN|YI_XXX|61917|3(34)|6|6|13. |9|TABLEACCESSBYINDEXROWID|SYS_ORGANIZATIONS|1|10|2(50)|14. |*10|INDEXUNIQUESCAN|PK_SYS_ORGANIZATIONS|1|15. |*11|INDEXRANGESCAN|PK_CM_BUSI_CHARGE_201108|1|26|2(50)|16. |12|TABLEACCESSBYGLOBALINDEXROWID|CM_USER|1|30|3(34)|ROWID|ROWL|17. |*13|INDEXUNIQUESCAN|PK_ZK_CM_USER|1|2(50)|18. |14|PARTITIONRANGEALL|1|10|19. |*15|INDEXRANGESCAN|IDX_SIM_SIM|1|31(4)|1|10|20. -21. 22. PredicateInformation(identifiedbyoperationid):23. -24. 25. 4-filter(B.BOOK_ITEM_ID=23000001ORB.BOOK_ITEM_ID=23000002ORB.BOOK_ITEM_ID=27000003)26. 7-filter(A.OP_ID71010264)27. 8-access(A.SO_DATE=TO_DATE(2011-08-3100:00:00,syyyy-mm-ddhh24:mi:ss)ANDA.SO_COUNTY_CODE=7111AND28. A.SO_DATE0;B是一个视图，定义如下：view plaincopy to clipboardprint?1. CREATEORREPLACEVIEWAPPS.AP_UNAPPLY_PREPAYS_VAS2. SELECTAID1.ROWIDROW_ID,3. AID1.INVOICE_IDINVOICE_ID,4. AID1.INVOICE_DISTRIBUTION_IDINVOICE_DISTRIBUTION_ID,5. AID1.PREPAY_DISTRIBUTION_IDPREPAY_DISTRIBUTION_ID,6. AID1.DISTRIBUTION_LINE_NUMBERPREPAY_DIST_NUMBER,7. (-1)*AID1.AMOUNTPREPAY_AMOUNT_APPLIED,8. nvl(AID2.PREPAY_AMOUNT_REMAINING,AID2.AMOUNT)PREPAY_AMOUNT_REMAINING,9. AID1.DIST_CODE_COMBINATION_IDDIST_CODE_COMBINATION_ID,10. AID1.ACCOUNTING_DATEACCOUNTING_DATE,11. AID1.PERIOD_NAMEPERIOD_NAME,12. AID1.SET_OF_BOOKS_IDSET_OF_BOOKS_ID,13. AID1.DESCRIPTIONDESCRIPTION,14. AID1.PO_DISTRIBUTION_IDPO_DISTRIBUTION_ID,15. AID1.RCV_TRANSACTION_IDRCV_TRANSACTION_ID,16. AID1.ORG_IDORG_ID,17. AI.INVOICE_NUMPREPAY_NUMBER,18. AI.VENDOR_IDVENDOR_ID,19. AI.VENDOR_SITE_IDVENDOR_SITE_ID,20. ATC.TAX_IDTAX_ID,21. ATC.NAMETAX_CODE,22. PH.SEGMENT1PO_NUMBER,23. PV.VENDOR_NAMEVENDOR_NAME,24. PV.SEGMENT1VENDOR_NUMBER,25. PVS.VENDOR_SITE_CODEVENDOR_SITE_CODE,26. RSH.RECEIPT_NUMRECEIPT_NUMBER27. FROMAP_INVOICESAI,28. AP_INVOICE_DISTRIBUTIONSAID1,29. AP_INVOICE_DISTRIBUTIONSAID2,30. AP_TAX_CODESATC,31. PO_VENDORSPV,32. PO_VENDOR_SITESPVS,33. PO_DISTRIBUTIONSPD,34. PO_HEADERSPH,35. PO_LINESPL,36. PO_LINE_LOCATIONSPLL,37. RCV_TRANSACTIONSRTXNS,38. RCV_SHIPMENT_HEADERSRSH,39. RCV_SHIPMENT_LINESRSL40. WHEREAID1.PREPAY_DISTRIBUTION_ID=AID2.INVOICE_DISTRIBUTION_ID41. ANDAI.INVOICE_ID=AID2.INVOICE_ID42. ANDAID1.AMOUNT$ORACLE_HOME/rdbms/admin/utlxpls2. 3. PLAN_TABLE_OUTPUT4. -5. 6. -7. |Id|Operation|Name|Rows|Bytes|Cost|8. -9. |0|SELECTSTATEMENT|1|69|722|10. |*1|FILTER|11. |2|SORTGROUPBY|1|69|722|12. |3|NESTEDLOOPSOUTER|3|207|697|13. |*4|TABLEACCESSFULL|AP_INVOICES_ALL|3|153|694|14. |5|VIEWPUSHEDPREDICATE|AP_UNAPPLY_PREPAYS_V|1|18|1|15. |6|NESTEDLOOPS|1|372|3|16. |7|NESTEDLOOPS|1|368|3|17. |8|NESTEDLOOPS|1|361|2|18. |9|NESTEDLOOPS|1|347|1|19. |10|NESTEDLOOPSOUTER|1|334|1