子查询优化的经典案例.docx

子查询优化的经典案例 分类： oracle 性能优化2012-09-06 02:19324人阅读评论(0)收藏举报上周五要下班的时候，盖尔发来一个SQLhtml view plaincopyprint?1. select tpc.policy_id, 2. tcm.policy_code, 3. an_id, 4. to_char(tpf.insert_time, YYYY-MM-DD) As insert_time, 5. tpc.change_id, 6. d.policy_code, 7. pany_name, 8. f.real_name, 9. tpf.fee_type, 10. sum(tpf.pay_balance) as pay_balance, 11. c.actual_type, 12. tpc.notice_code, 13. d.policy_type, 14. g.mode_name as pay_mode 15. from t_policy_change tpc, 16. t_contract_master tcm, 17. t_policy_fee tpf, 18. t_fee_type c, 19. t_contract_master d, 20. t_company_customer e, 21. t_customer f, 22. t_pay_mode g 23. where tpc.change_id = tpf.change_id 24. and tpf.policy_id = d.policy_id 25. and tcm.policy_id = tpc.policy_id 26. and tpf.receiv_status = 1 27. and tpf.fee_status = 1 28. and tpf.payment_id is null 29. and tpf.fee_type = c.type_id 30. and tpf.pay_mode = g.mode_id 31. and pany_id = pany_id(+) 32. and d.applicant_id = f.customer_id(+) 33. and an_id in 34. (select 35. organ_id 36. from t_company_organ 37. start with organ_id = 101 38. connect by prior organ_id = parent_id) 39. group by tpc.policy_id, 40. tpc.change_id, 41. tpf.fee_type, 42. to_char(tpf.insert_time, YYYY-MM-DD), 43. c.actual_type, 44. d.policy_code, 45. g.mode_name, 46. pany_name, 47. f.real_name, 48. tpc.notice_code, 49. d.policy_type, 50. an_id, 51. tcm.policy_code 52. order by change_id, fee_type 53.54. SQL select * from table(dbms_xplan.display); 55.56. PLAN_TABLE_OUTPUT 57. - 58. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| 59. - 60. | 0 | SELECT STATEMENT | | 45962 | 11M| | 45650 (0)| 61. | 1 | SORT GROUP BY | | 45962 | 11M| 23M| 45650 (0)| 62. |* 2 | HASH JOIN | | 45962 | 11M| | 43908 (0)| 63. | 3 | INDEX FULL SCAN | T_FEE_TYPE_IDX_003 | 106 | 636 | | 1 (0)| 64. | 4 | NESTED LOOPS OUTER | | 45962 | 11M| | 43906 (0)| 65. |* 5 | HASH JOIN | | 45962 | 7271K| 6824K| 43905 (0)| 66. | 6 | NESTED LOOPS | | 45961 | 6283K| | 42312 (0)| 67. |* 7 | HASH JOIN SEMI | | 45961 | 5655K| 50M| 33120 (1)| 68. |* 8 | HASH JOIN OUTER | | 400K| 45M| 44M| 32315 (1)| 69. |* 9 | HASH JOIN | | 400K| 39M| 27M| 26943 (0)| 70. |* 10 | HASH JOIN | | 400K| 23M| | 16111 (0)| 71. | 11 | TABLE ACCESS FULL | T_PAY_MODE | 25 | 525 | | 2 (0)| 72. |* 12 | TABLE ACCESS FULL | T_POLICY_FEE | 400K| 15M| | 16107 (0)| 73. | 13 | TABLE ACCESS FULL | T_CONTRACT_MASTER | 1136K| 46M| | 9437 (0)| 74. | 14 | VIEW | index_join_007 | 2028K| 30M| | | 75. |* 15 | HASH JOIN | | 400K| 45M| 44M| 32315 (1)| 76. | 16 | INDEX FAST FULL SCAN | PK_T_CUSTOMER | 2028K| 30M| | 548 (0)| 77. | 17 | INDEX FAST FULL SCAN | IDX_CUSTOMER_BIR_REAL_GEN | 2028K| 30M| | 548 (0)| 78. | 18 | VIEW | VW_NSO_1 | 7 | 42 | | | 79. |* 19 | CONNECT BY WITH FILTERING | | | | | | 80. | 20 | NESTED LOOPS | | | | | | 81. |* 21 | INDEX UNIQUE SCAN | PK_T_COMPANY_ORGAN | 1 | 6 | | | 82. | 22 | TABLE ACCESS BY USER ROWID| T_COMPANY_ORGAN | | | | | 83. | 23 | NESTED LOOPS | | | | | | 84. | 24 | BUFFER SORT | | 7 | 70 | | | 85. | 25 | CONNECT BY PUMP | | | | | | 86. |* 26 | INDEX RANGE SCAN | T_COMPANY_ORGAN_IDX_002 | 7 | 70 | | 1 (0)| 87. | 27 | TABLE ACCESS BY INDEX ROWID | T_POLICY_CHANGE | 1 | 14 | | 2 (50)| 88. |* 28 | INDEX UNIQUE SCAN | PK_T_POLICY_CHANGE | 1 | | | 1 (0)| 89. | 29 | INDEX FAST FULL SCAN | IDX1_ACCEPT_DATE | 1136K| 23M| | 899 (0)| 90. | 30 | TABLE ACCESS BY INDEX ROWID | T_COMPANY_CUSTOMER | 1 | 90 | | 2 (50)| 91. |* 31 | INDEX UNIQUE SCAN | PK_T_COMPANY_CUSTOMER | 1 | | | | 92. - 93.94. Predicate Information (identified by operation id): 95. - 96.97. 2 - access(TPF.FEE_TYPE=C.TYPE_ID) 98. 5 - access(TCM.POLICY_ID=TPC.POLICY_ID) 99. 7 - access(TPF.ORGAN_ID=VW_NSO_1.$nso_col_1) 100. 8 - access(D.APPLICANT_ID=F.CUSTOMER_ID(+) 101. 9 - access(TPF.POLICY_ID=D.POLICY_ID) 102. 10 - access(TPF.PAY_MODE=G.MODE_ID) 103. 12 - filter(TPF.CHANGE_ID IS NOT NULL AND TO_NUMBER(TPF.RECEIV_STATUS)=1 AND TPF.FEE_STATUS=1 AND 104. TPF.PAYMENT_ID IS NULL) 105. 15 - access(indexjoin_alias_012.ROWID=indexjoin_alias_011.ROWID) 106. 19 - filter(T_COMPANY_ORGAN.ORGAN_ID=101) 107. 21 - access(T_COMPANY_ORGAN.ORGAN_ID=101) 108. 26 - access(T_COMPANY_ORGAN.PARENT_ID=NULL) 109. 28 - access(TPC.CHANGE_ID=TPF.CHANGE_ID) 110. 31 - access(D.COMPANY_ID=E.COMPANY_ID(+) 111.112. 55 rows selected 113.114. Statistics 115. - 116. 21 recursive calls 117. 0 db block gets 118. 125082 consistent gets 119. 21149 physical reads 120. 0 redo size 121. 2448 bytes sent via SQL*Net to client 122. 656 bytes received via SQL*Net from client 123. 2 SQL*Net roundtrips to/from client 124. 4 sorts (memory) 125. 0 sorts (disk) 126. 11 rows processed 这个SQL要21秒才能跑完，逻辑读12W左右，问我能不能优化。优化这个SQL我只花了1分钟左右的时间，因为太简单了你们看这个SQL是典型的JOIN，对付这种SQL肯定要让表走索引，但是从执行计划上看有个1千万行的表T_CONTRACT_MASTER走的是全表扫描，T_POLICY_FEE 这个400W行的表也是走全表扫描，那么它不慢才怪呢，然后SQL的过滤条件有个 in 子查询(select organ_idfrom t_company_organstart with organ_id = 101connect by prior organ_id = parent_id)从执行计划上看，CBO对这儿子查询进行了unnest，因为通常情况下CBO认为子查询被unnest之后性能好于filter 于是我让盖尔查询 子查询返回多少行 select organ_idfrom t_company_organstart with organ_id = 101connect by prior organ_id = parent_id -盖尔说它返回1行 对于子查询，如果它返回数据很少(这里返回1行)，那么可以让它走filter, 而且filter基本上是在SQL最后去阶段执行，这样t_policy_fee就可以走索引了所以我给这个子查询加了个HINT，禁止子查询扩展html view plaincopyprint?1. select tpc.policy_id, 2. tcm.policy_code, 3. an_id, 4. to_char(tpf.insert_time, YYYY-MM-DD) As insert_time, 5. tpc.change_id, 6. d.policy_code, 7. pany_name, 8. f.real_name, 9. tpf.fee_type, 10. sum(tpf.pay_balance) as pay_balance, 11. c.actual_type, 12. tpc.notice_code, 13. d.policy_type, 14. g.mode_name as pay_mode 15. from t_policy_change tpc, 16. t_contract_master tcm, 17. t_policy_fee tpf, 18. t_fee_type c, 19. t_contract_master d, 20. t_company_customer e, 21. t_customer f, 22. t_pay_mode g 23. where tpc.change_id = tpf.change_id 24. and tpf.policy_id = d.policy_id 25. and tcm.policy_id = tpc.policy_id 26. and tpf.receiv_status = 1 -这里原来没引号，是开发那SB搞忘了写,我让盖尔添加上了，不添加上就没法用索引 27. and tpf.fee_status = 1 28. and tpf.payment_id is null 29. and tpf.fee_type = c.type_id 30. and tpf.pay_mode = g.mode_id 31. and pany_id = pany_id(+) 32. and d.applicant_id = f.customer_id(+) 33. and an_id in 34. (select /*+ no_unnest */ -此处的HINT后加的 35. organ_id 36. from t_company_organ 37. start with organ_id = 101 38. connect by prior organ_id = parent_id) 39. group by tpc.policy_id, 40. tpc.change_id, 41. tpf.fee_type, 42. to_char(tpf.insert_time, YYYY-MM-DD), 43. c.actual_type, 44. d.policy_code, 45. g.mode_name, 46. pany_name, 47. f.real_name, 48. tpc.notice_code, 49. d.policy_type, 50. an_id, 51. tcm.policy_code 52. order by change_id, fee_type 53.54. SQL select * from table(dbms_xplan.display); 55.56. PLAN_TABLE_OUTPUT 57. - 58. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| 59. - 60. | 0 | SELECT STATEMENT | | 20026 | 4928K| | 68615 (30)| 61. | 1 | SORT GROUP BY | | 20026 | 4928K| 10M| 28563 (0)| 62. |* 2 | FILTER | | | | | | 63. | 3 | NESTED LOOPS | | 20026 | 4928K| | 27812 (0)| 64. | 4 | NESTED LOOPS | | 20026 | 4498K| | 23807 (0)| 65. | 5 | NESTED LOOPS OUTER | | 20026 | 4224K| | 19802 (0)| 66. | 6 | NESTED LOOPS OUTER | | 20026 | 3911K| | 15797 (0)| 67. | 7 | NESTED LOOPS | | 20026 | 2151K| | 15796 (0)| 68. |* 8 | HASH JOIN | | 20026 | 1310K| | 11791 (0)| 69. | 9 | INDEX FULL SCAN | T_FEE_TYPE_IDX_003 | 106 | 636 | | 1 (0)| 70. |* 10 | HASH JOIN | | 20026 | 1192K| | 11789 (0)| 71. | 11 | TABLE ACCESS FULL | T_PAY_MODE | 25 | 525 | | 2 (0)| 72. |* 12 | TABLE ACCESS BY INDEX ROWID| T_POLICY_FEE | 20026 | 782K| | 11786 (0)| 73. |* 13 | INDEX RANGE SCAN | IDX_POLICY_FEE_RECEIV_STATUS | 1243K| | | 10188 (0)| 74. | 14 | TABLE ACCESS BY INDEX ROWID | T_CONTRACT_MASTER | 1 | 43 | | 2 (50)| 75. |* 15 | INDEX UNIQUE SCAN | PK_T_CONTRACT_MASTER | 1 | | | 1 (0)| 76. | 16 | TABLE ACCESS BY INDEX ROWID | T_COMPANY_CUSTOMER | 1 | 90 | | 2 (50)| 77. |* 17 | INDEX UNIQUE SCAN | PK_T_COMPANY_CUSTOMER | 1 | | | | 78. | 18 | TABLE ACCESS BY INDEX ROWID | T_CUSTOMER | 1 | 16 | | 2 (50)| 79. |* 19 | INDEX UNIQUE SCAN | PK_T_CUSTOMER | 1 | | | 1 (0)| 80. | 20 | TABLE ACCESS BY INDEX ROWID | T_POLICY_CHANGE | 1 | 14 | | 2 (50)| 81. |* 21 | INDEX UNIQUE SCAN | PK_T_POLICY_CHANGE | 1 | | | 1 (0)| 82. | 22 | TABLE ACCESS BY INDEX ROWID | T_CONTRACT_MASTER | 1 | 22 | | 2 (50)| 83. |* 23 | INDEX UNIQUE SCAN | PK_T_CONTRACT_MASTER | 1 | | | 1 (0)| 84. |* 24 | FILTER | | | | | | 85. |* 25 | CONNECT BY WITH FILTERING | | | | | | 86. | 26 | NESTED LOOPS | | | | | | 87. |* 27 | INDEX UNIQUE SCAN | PK_T_COMPANY_ORGAN | 1 | 6 | | | 88. | 28 | TABLE ACCESS BY USER ROWID | T_COMPANY_ORGAN | | | | | 89. | 29 | NESTED LOOPS | | | | | | 90. | 30 | BUFFER SORT | | 7 | 70 | | | 91. | 31 | CONNECT BY PUMP | | | | | | 92. |* 32 | INDEX RANGE SCAN | T_COMPANY_ORGAN_IDX_002 | 7 | 70 | | 1 (0)| 93. - 94.95. Predicate Information (identified by operation id): 96. - 97.98. 2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM T_COMPANY_ORGAN T_COMPANY_ORGAN WHERE 99. T_COMPANY_ORGAN.PARENT_ID=NULL AND (T_COMPANY_ORGAN.ORGAN_ID=:B1) 100. 8 - access(SYS_ALIAS_1.FEE_TYPE=C.TYPE_ID) 101. 10 - access(SYS_ALIAS_1.PAY_MODE=G.MODE_ID) 102. 12 - filter(SYS_ALIAS_1.CHANGE_ID IS NOT NULL AND SYS_A