• 1. Oracle数据库性能优化实务 第15讲:多表连接的优化主讲人:白鳝华章培训网、[www.hztraining.com]华章培训网版权所有
  • 2. 多表连接的优化要点表连接的顺序 表连接的方式 多表连接的问题最终被分解为多次双表连接
  • 3. 本节关注的和不关注的细节本节关注的内容: 复杂表连接优化的思路和方法 子查询优化的要点 外联接优化 CONNECT BY的优化 本节不关注的内容: 执行计划分析 表连接方式及相关优化要点
  • 4. 表连接的顺序的优化建议表连接顺序最好从业务的角度考虑 多表连接可以一层一层分析,每次只考虑两张表 表连接顺序的选择原则是尽快减少连接结果集的大小
  • 5. 表连接优化可能面临的问题无法从业务人员得到所需的信息 连接条件十分复杂 连接的表很多 无法获得绑定变量的值
  • 6. 多表连接的分析(1)分析WHERE条件分析WHERE条件 了解每张表的过滤条件 了解表之间的连接关系 分析可能的连接顺序 确定最佳连接顺序
  • 7. 多表连接的分析(2)分析子查询为了便于分析部分子查询可以改写为表连接 注意等价原则 常见子查询模式 EXISTS/NOT EXISTS IN/NOT IN select d.dname from dept d where exists (select 1 from emp e where e. deptno is not null and d.deptno=e.deptno and e.ename is not null and e.empno > 1000) and d.deptno is not null; select d.dname from dept d where d.deptno in (select e.deptno from emp e); select ename from emp where deptno not in (select deptno from dept) and deptno is not null;
  • 8. 多表连接的分析(3)分析VIEW和INLINE VIEWMERGE VIEW不合理往往导致性能问题 大部分INLINE VIEW可以改写为等价表连接 部分VIEW和INLINE VIEW可以用物化视图来优化
  • 9. 多表连接的分析(4)分析表连接方式表连接方式对性能影响极大 大多数多表连接出现突发性能问题和表连接方式有关 主要的表连接方式 NESTED LOOP HASH JOIN SORT MERGE JOIN
  • 10. 案例(1)优化一个复杂SQLselect acct_id,billing_cycle,bill_item_name,item_source_name,to_ char(sum(amount),'FM999999990.90') amount,stateName,stateDate f rom( select a.acct_id ,c.billing_cycle,d.bill_item_name,e.name i tem_source_name,a.amount,f.name stateName,to_char(a.state_date,' yyyy-mm-dd hh24:mi:ss') stateDate from (select acct_item_type_ id ,billing_cycle_id,acct_id,item_source_id,state,state_date,sum (amount/100) amount from( select * from acct_item where acct_ id =165440341 and serv_id =169002286374 and billing_cycle_id in (10906) ) group by acct_item_type_id,billing_cycle_id,acct_id,it em_source_id,state ,state_date) a, (select billing_cycle_id ,to_ char(cycle_end_date -1,'yyyy-mm') billing_cycle from billing_cyc le where state in('10A','10R','10E','10D') and billing_cycle_id in(10906))c ,(select x.acct_item_type_id ,y.bill_item_name,x.i tem_source_id from bill_item_acct_item x ,bill_item y , (select distinct invoice_require_id from serv_acct where acct_id=165440 341 and serv_id =169002286374 ) ee , bill_requement ff,bill_form at_bill_item gg where x.bill_item_type_id = y.bill_item_type_id and ee.invoice_require_id = ff.require_id and y.classify='55A' and ff.bill_format_id = gg.bill_format_id and gg.bill_item_type _id = x.bill_item_type_id)d ,acct_item_source e, (select domain, name from v_domain where table_name ='ACCT_ITEM' and field_name= 'STATE' ) f where a.billing_cycle_id = c.billing_cycle_id and a .acct_item_type_id= d.acct_item_type_id and a.item_source_id = d .item_source_id and a.item_source_id =e.item_source_id and e.ite m_source_type='52A' and a.state=f.domain ) group by acct_id ,bi lling_cycle,bill_item_name,item_source_name,stateName,stateDate order by billing_cycle
  • 11. 一个SQL的分析过程(1)格式化文本select acct_id,billing_cycle,bill_item_name,item_source_name, to_char(sum(amount),'FM999999990.90') amount,stateName,stateDate From ( select a.acct_id ,c.billing_cycle,d.bill_item_name,e.name item_source_name,a.amount,f.name stateName,to_char(a.state_date,'yyyy-mm-dd hh24:mi:ss') stateDate from (select acct_item_type_id ,billing_cycle_id,acct_id,item_source_id,state,state_date,sum(amount/100) amount from ( select * from acct_item where acct_id =165440341 and serv_id =169002286374 and billing_cycle_id in (10906) ) group by acct_item_type_id,billing_cycle_id,acct_id, item_source_id,state ,state_date ) a, (select billing_cycle_id ,to_char(cycle_end_date -1,'yyyy-mm') billing_cycle from billing_cycle where state in('10A','10R','10E','10D') and billing_cycle_id in(10906) )c , (select x.acct_item_type_id ,y.bill_item_name,x.item_source_id from bill_item_acct_item x ,bill_item y , (select distinct invoice_require_id from serv_acct where acct_id=165440 341 and serv_id =169002286374 ) ee , bill_requement ff,bill_form at_bill_item gg where x.bill_item_type_id = y.bill_item_type_id and ee.invoice_require_id = ff.require_id and y.classify='55A' and ff.bill_format_id = gg.bill_format_id and gg.bill_item_type_id = x.bill_item_type_id )d ,acct_item_source e, (select domain,name from v_domain where table_name ='ACCT_ITEM' and field_name='STATE' ) f where a.billing_cycle_id = c.billing_cycle_id and a.acct_item_type_id= d.acct_item_type_id and a.item_source_id = d.item_source_id and a.item_source_id =e.item_source_id and e.item_source_type='52A' and a.state=f.domain ) group by acct_id ,billing_cycle,bill_item_name,item_source_name,stateName,stateDate order by billing_cycle
  • 12. 一个SQL的分析过程(2)截取子查询select a.acct_id ,c.billing_cycle,d.bill_item_name,e.name item_source_name,a.amount,f.name stateName,to_char(a.state_date,'yyyy-mm-dd hh24:mi:ss') stateDate from (select acct_item_type_id ,billing_cycle_id,acct_id,item_source_id,state,state_date,sum(amount/100) amount from ( select * from acct_item where acct_id =165440341 and serv_id =169002286374 and billing_cycle_id in (10906) ) group by acct_item_type_id,billing_cycle_id,acct_id, item_source_id,state ,state_date ) a, (select billing_cycle_id ,to_char(cycle_end_date -1,'yyyy-mm') billing_cycle from billing_cycle where state in('10A','10R','10E','10D') and billing_cycle_id in(10906) )c , (select x.acct_item_type_id ,y.bill_item_name,x.item_source_id from bill_item_acct_item x ,bill_item y , (select distinct invoice_require_id from serv_acct where acct_id=165440 341 and serv_id =169002286374 ) ee , bill_requement ff,bill_form at_bill_item gg where x.bill_item_type_id = y.bill_item_type_id and ee.invoice_require_id = ff.require_id and y.classify='55A' and ff.bill_format_id = gg.bill_format_id and gg.bill_item_type_id = x.bill_item_type_id )d ,acct_item_source e, (select domain,name from v_domain where table_name ='ACCT_ITEM' and field_name='STATE' ) f where a.billing_cycle_id = c.billing_cycle_id and a.acct_item_type_id= d.acct_item_type_id and a.item_source_id = d.item_source_id and a.item_source_id =e.item_source_id and e.item_source_type='52A' and a.state=f.domain
  • 13. 一个SQL的分析过程(3)识别行源视图1: (select acct_item_type_id ,billing_cycle_id,acct_id,item_source_id,state,state_date,sum(amount/100) amount from ( select * from acct_item where acct_id =165440341 and serv_id =169002286374 and billing_cycle_id in (10906) ) group by acct_item_type_id,billing_cycle_id,acct_id, item_source_id,state ,state_date ) a, 视图2: (select billing_cycle_id ,to_char(cycle_end_date -1,'yyyy-mm') billing_cycle from billing_cycle where state in('10A','10R','10E','10D') and billing_cycle_id in(10906) )c 视图3: (select x.acct_item_type_id ,y.bill_item_name,x.item_source_id from bill_item_acct_item x ,bill_item y , (select distinct invoice_require_id from serv_acct where acct_id=165440 341 and serv_id =169002286374 ) ee ,bill_requement ff,bill_form at_bill_item gg where x.bill_item_type_id = y.bill_item_type_id and ee.invoice_require_id = ff.require_id and y.classify='55A' and ff.bill_format_id = gg.bill_format_id and gg.bill_item_type_id = x.bill_item_type_id )d 表1: acct_item_source e, 视图4: (select domain,name from v_domain where table_name ='ACCT_ITEM' and field_name='STATE' ) f
  • 14. 一个SQL的分析过程(4)识别连接条件 a.billing_cycle_id = c.billing_cycle_id and a.acct_item_type_id= d.acct_item_type_id and a.item_source_id = d.item_source_id and a.item_source_id =e.item_source_id and e.item_source_type='52A' and a.state=f.domain 以A核心,其他表均通过A进行连接 A上无过滤条件,唯一的过滤条件是E上的e.item_source_type='52A‘ 连接顺序应该是首选A+E连接,再考虑和其他表连接
  • 15. 一个SQL的分析过程(5)分析A+E连接 (select acct_item_type_id ,billing_cycle_id,acct_id,item_source_id,state,state_date,sum(amount/100) amount from ( select * from acct_item where acct_id =165440341 and serv_id =169002286374 and billing_cycle_id in (10906) ) group by acct_item_type_id,billing_cycle_id,acct_id, item_source_id,state ,state_date ) a acct_item_source e A+E连接的等价SQL select acct_item_type_id ,billing_cycle_id,acct_id,item_source_id,state,state_date,sum(amount/100) amount from ( select * from acct_item X1, acct_item_source X2 where x1.acct_id =165440341 and x1.serv_id =169002286374 and x1.billing_cycle_id in (10906) and x2. item_source_type='52A‘ and x1.item_source_id =x1.item_source_id ) group by acct_item_type_id,billing_cycle_id,acct_id, item_source_id,state ,state_date
  • 16. 一个SQL的分析过程(6)分析第一层连接 select acct_item_type_id ,billing_cycle_id,acct_id,item_source_id,state,state_date,sum(amount/100) amount from ( select * from acct_item X1, acct_item_source X2 where x1.acct_id =165440341 and x1.serv_id =169002286374 and x1.billing_cycle_id in (10906) and x2. item_source_type='52A‘ and x1.item_source_id =x1.item_source_id ) group by acct_item_type_id,billing_cycle_id,acct_id, item_source_id,state ,state_date 分析表连接的方式 分析结果集的大小
  • 17. 一个SQL的分析过程(7)创建用于分析的临时表CREATE TABLE TMP_TBL1 AS select acct_item_type_id ,billing_cycle_id,acct_id,item_source_id,state,state_date,sum(amount/100) amount from ( select * from acct_item X1, acct_item_source X2 where x1.acct_id =165440341 and x1.serv_id =169002286374 and x1.billing_cycle_id in (10906) and x2. item_source_type='52A‘ and x1.item_source_id =x1.item_source_id ) group by acct_item_type_id,billing_cycle_id,acct_id, item_source_id,state ,state_date; EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>’…’,TABNAME=>’TMP_TBL1);
  • 18. 一个SQL的分析过程(8)测试T+C的连接select c.* (select billing_cycle_id ,to_char(cycle_end_date -1,'yyyy-mm') billing_cycle from billing_cycle where state in('10A','10R','10E','10D') and billing_cycle_id in(10906) )c ,tmp_tbl t where t.billing_cycle_id = c.billing_cycle_id
  • 19. 一个SQL的分析过程(9)测试T+D的连接select d.* (select x.acct_item_type_id ,y.bill_item_name,x.item_source_id from bill_item_acct_item x ,bill_item y , (select distinct invoice_require_id from serv_acct where acct_id=165440 341 and serv_id =169002286374 ) ee , bill_requement ff,bill_form at_bill_item gg where x.bill_item_type_id = y.bill_item_type_id and ee.invoice_require_id = ff.require_id and y.classify='55A' and ff.bill_format_id = gg.bill_format_id and gg.bill_item_type_id = x.bill_item_type_id) d ,tmp_tbl1 t where t.acct_item_type_id= d.acct_item_type_id and t.item_source_id = d.item_source_id
  • 20. 一个SQL的分析过程(10)测试T+F的连接select d.* (select domain,name from v_domain where table_name ='ACCT_ITEM' and field_name='STATE' ) f ,tmp_tbl1 t where t.state=f.domain
  • 21. 分析先和哪个表连接过滤条件比较有效,可以尽快减少结果集的大小 对于INLINE视图,有些时候还要分析合并视图是否更为有效,这样就涉及更复杂的优化分析路径 比如对于T+D的连接,本身就比较复杂,针对该SQL可以采取类似的方式进行分析一个SQL的分析过程(11)下一步工作
  • 22. 一个SQL的分析过程(12)优化T+D的连接select d.* (select x.acct_item_type_id ,y.bill_item_name,x.item_source_id from bill_item_acct_item x ,bill_item y , (select distinct invoice_require_id from serv_acct where acct_id=165440 341 and serv_id =169002286374 ) ee , bill_requement ff,bill_form at_bill_item gg where x.bill_item_type_id = y.bill_item_type_id and ee.invoice_require_id = ff.require_id and y.classify='55A' and ff.bill_format_id = gg.bill_format_id and gg.bill_item_type_id = x.bill_item_type_id) d ,tmp_tbl1 t where t.acct_item_type_id= d.acct_item_type_id and t.item_source_id = d.item_source_id
  • 23. 一个SQL的分析过程(13)确定表连接顺序根据上一步的分析结果,选择一个连接顺序 通过LEADING,ORDERED等提示修改SQL,分析执行计划和效率 选择一个可以接受的执行计划 制定优化方案 如果执行计划不佳是表分析数据不准确引起,进行表分析 可以通过10053 TRACE辅助分析执行计划选择错误的原因 如果可以修改SQL,通过HINT固定执行计划 可以使用10G的SPA采用SQL PROFILE固定执行计划 对于无法修改执行计划的情况,可以使用STORED OUTLINES
  • 24. 一个SQL的分析过程(14)记录下优化方案将分析过程和优化方案记录在案 向开发人员讲解优化方案 将优化方案记录在代码中(注释)
  • 25. 感谢您对华章培训网的支持!http://www. hztraining.com休息一下,下节更精彩
  • 26. 感谢您对华章培训网的支持!http://www. hztraining.com欢迎到Oraclefans网交流技术http://www. oraclefans.cn