• 1. Oracle数据库性能优化实务 第12讲:SQL优化技术概述主讲人:白鳝华章培训网、[www.hztraining.com]华章培训网版权所有
  • 2. SQL优化的主要思路查找TOP SQL 分析SQL对系统的影响 分析SQL的优化方法 制定优化计划 实施优化操作 评估优化效果
  • 3. TOP SQLTOP SQL是在某个时间段内对系统影响较大的SQL 不同时间窗口的TOP SQL可能不同 TOP SQL的出现有其周期性 查找TOP SQL的主要工具 AWR/STATSPACK/ADDM/ASH报告 EM ADDM分析 SQLA(来自METALINK的工具) V$SQL/V$SQLAREA Oracle 9i SQLANALYZER Oracle 9i EM TOP SQL
  • 4. TOP SQL对系统的影响并不是所有的TOP SQL都是需要优化的 非周期性的临时SQL 对OLTP系统影响较小的批处理应用 可以安排在非业务高峰期间执行的批处理作业 TOP SQL对系统的影响 CPU开销 IO开销 热块冲突
  • 5. 分析优化方法优化效果和代价 最大性价比原则 规避风险原则 可能的选择 调整索引 调整执行计划 优化相关表的存储结构 数据归档 表和索引分析策略调整 调整SQL执行时间窗口 限制数据查询范围 修改SQL
  • 6. 制定优化计划优化计划包含的内容 优化时间窗口定义 优化操作详细步骤 各个步骤的回退方案 优化后的验证方案 注意事项 优化结果验证十分关键 优化实施前建立相关性能基线 优化实施后验证可能相关的核心业务模块十分功能正常
  • 7. SQL优化的要点合理的使用索引 尽可能使用CBO优化器 尽可能分拆过大的SQL 一次访问数据,多次使用 多表连接时的表连接顺序和方法 大批量数据操作尽可能使用BULK操作 注意绑定变量的使用 减少资源争用 优化应用架构对应用性能影响深远
  • 8. 合理使用索引索引对SELECT 操作有利,不过可能影响DML操作 绝大多数OLTP系统中SELECT操作的开销超过80% 在WHERE条件经常出现的字段上设置索引 注意索引的选择性 注意索引的类别 B树索引 降序索引 BITMAP索引 反转键索引 位图连接索引 9i后复合索引的作用更大
  • 9. 尽可能使用CBO优化器CBO智能化更强 不需要注意WHERE条件的顺序和FROM后面表的顺序 支持更多的表连接方式 支持INDEX SKIP SCAN
  • 10. 了解RBO优化器(1)为了向后兼容 执行计划和SQL语法相关 不使用统计数据 不计算COST RBO的特点 尽可能使用索引 使用内部规则选择执行计划
  • 11. 了解RBO优化器(2)Rank 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15Access Path Single row by ROWID Single row by cluster join Single row by hash cluster key with unique or primary key Single row by unique or primary key Cluster join Hash cluster key Indexed cluster key Composite index Single-column index Bounded range search on indexed columns Unbounded range search on indexed columns Sort-merge join MAX or MIN of indexed column ORDER BY on indexed column Full table scan
  • 12. 了解RBO优化器(3)RBO 可能的执行路径: Full table scan Rank = 15 ORDER BY on indexed column Rank = 14 COUNTRY_ID index equality (range) scan Rank = 9 CUST_CREDIT_LIMIT index bounded-range search Rank = 10SQL> select cust_last_name 2 from customers 3 where country_id = 'FR' 4 and cust_credit_limit between 11000 and 15000 5 order by cust_credit_limit;
  • 13. 课间休息 下面更精彩...
  • 14. 尽可能分拆大型SQL多表连接的大型SQL可能出现执行计划不稳定 一个SQL完成一项功能是一种误区 尽可能不要把什么操作都交给ORACLE去做 应用服务器应该承担其应有的作用
  • 15. 一次访问数据,多次使用拆分SQL,使用临时表 使用PL/SQL存储过程替代一条SQL 尽可能在一次扫描中完成最多的工作
  • 16. 多表连接的连接顺序多表连接中表连接顺序错误会导致致命后果 编程时应该从业务角度考虑连接顺序 在代码中对于连接顺序应该做出详细的业务描述 用ORDERED提示来固化连接顺序
  • 17. 选择适当的表连接方式NESTED LOOP:适合外表结果集较小,内表有强选择性的索引 HASH JOIN:无适当的索引可用,驱动表的结果集较大,内表结果集记录数远大于驱动表 SORT MERGE JOIN:适合无合适的索引可用,内外表结果集大小接近的情况 注意:并不一定通过索引连接就是合适的
  • 18. 大数据量操作时使用BULK操作BULK操作可以大幅度提高批量操作的性能 BULK操作支持SELECT和DML BULK操作的接口 PRO*C的数组绑定变量 PL/SQL的FORALL 循环 JAVA 10G开始支持ERROR LOG
  • 19. FORALL操作的案例批量(行)单条执行时间单条平均时间FORALL执行时间FORALL平均时间RUNID800910.11375230.028752450004900.0981340.0268262000019060.09534630.0231527结论: FORALL操作的响应时间是单条操作的1/4左右。随着每个批次的记录数的增加,单条操作和FORALL操作的性能都有所提高。
  • 20. FORALL操作举例CREATE OR REPLACE PROCEDURE TESTFORALL (N INTEGER) IS TYPE T_SM_ID IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER; TYPE T_SM_SUBID IS TABLE OF NUMBER(3) INDEX BY BINARY_INTEGER; TYPE T_ORGADDR IS TABLE OF VARCHAR2(21) INDEX BY BINARY_INTEGER; TYPE T_DESTADDR IS TABLE OF VARCHAR2(21) INDEX BY BINARY_INTEGER; TYPE T_ID_HINT IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER; V_SM_ID T_SM_ID; V_SM_SUBID T_SM_SUBID; V_ORGADDR T_ORGADDR; V_DESTADDR T_DESTADDR; V_ID_HINT T_ID_HINT; I INTEGER; BEGIN FOR I IN 1.. N LOOP V_SM_ID(I):=I; V_SM_SUBID(I):=12; V_ORGADDR(I):='444555565'; V_DESTADDR(I):='555555'; SELECT SM_IDSEQ.NEXTVAL INTO V_ID_HINT(I) FROM DUAL; END LOOP; FORALL I IN 1..N INSERT INTO SM_HISTABLE0101 (SM_ID,SM_SUBID,ORGADDR,DESTADDR,ID_HINT) VALUES (V_SM_ID(I),V_SM_SUBID(I),V_ORGADDR(I),V_DESTADDR(I),V_ID_HINT(I)); COMMIT; END;
  • 21. 合理绑定变量的使用绑定变量可以减少SQL分析,减少共享池争用 开发时尽可能使用绑定变量 也存在不适合使用绑定变量的场合 需要使用柱状图的情况不适合使用绑定变量
  • 22. 不适合使用绑定变量的案例案例描述 某表中的STATUS字段的取值为0,1 记录插入时STATUS=0 应用查找STATUS=0的记录,处理完毕后STATUS=1 STATUS=1的记录有2000万,STATUS=0的记录几十条到几百条 少量应用需要查找STATUS=1的数据 故障现象 有时候性能好,有时候性能差 RAC两个节点中一个走全表扫描,一个走索引
  • 23. 好的SQL编程风格写简单的SQL 只选出需要的字段 如果SQL的逻辑太复杂,拆分为多个SQL,通过一个PL/SQL对象来实现 尽可能用UNION ALL替代UNION 通过临时表来减少SQL的开销 不要使用占位操作 适当使用绑定变量 完整的注释 不要把所有操作都交给数据库服务器去做
  • 24. SQL优化小技巧(1)尽可能将表达式计算好 where a>1.2*3+1 ==> where a>7.6 不要做隐式类型转换 where a=123 ==> where a='123' 子查询的时候注意IN和EXISTS的使用 如果子查询的过滤条件强就用In 如果父查询的过滤条件强就用exists 在语义相同的时候尽可能用union all 替代union 不选择多余的列 创建索引的时候使用额外的字段 使用函数索引 使用HINT来固化执行计划
  • 25. HINT的例子SQL> UPDATE /*+ INDEX(p PROD_CATEGORY_IDX) */ 2 products p 3 SET p.prod_min_price = 4 (select 5 (pr.prod_list_price*.95) 6 from products pr 7 where p.prod_id = pr.prod_id) 8 WHERE p.prod_category = 'Men' 9 AND p.prod_status = 'available, on stock' 10 /
  • 26. SQL优化小技巧(2)不要使用占位符 where (1=1 and acct_attr=:b1) and (1=0 and acct_type=:b2) 占位符可能影响优化器的执行计划 对于小表,可以放入KEEP缓冲池中,进行全表扫描,而不要通过索引访问 使用表分区和索引分区技术,降低访问大型对象的开销 定期对索引进行重建,恢复索引的性能 注意位图索引的使用
  • 27. SQL优化小技巧(3)TOP NSELECT * FROM (SELECT /*+ INDEX_DESC( TTT IDX_TTT) */ OBJECT_NAME,OBJECT_ID FROM TTT ORDER BY OBJECT_NAME DESC) WHERE ROWNUM <= 5;
  • 28. SQL优化小技巧(4)分页查询SELECT * FROM ( SELECT rownum rnum, object_name,object_id FROM ( SELECT object_name,object_id FROM ttt ORDER BY object_name ) WHERE rownum <= 1000 ) outer_wrapper WHERE rnum > 990;
  • 29. 下节预告-SQL优化工具介绍SQL优化过程中各个阶段使用的工具 详细介绍工具的使用方法及使用场合
  • 30. 感谢您对华章培训网的支持!http://www. hztraining.com