崔华_SQL优化的方法论_IT168文库

Q&A
http://www.acoug.org 主讲人：崔华（dbsnake）
用合适的函数索引来避免看似无法避免的全表扫描
• drop index idx_t1; • create index idx_t1 on t1(object_name); • select object_name,object_id from t1 where object_name like '%EMP';
SQL Tuning Overall Example
• Oracle里SQL优化的方法论在实战中的验证之例一 • Oracle里SQL优化的方法论在实战中的验证之例二
Summary
• Oracle里的SQL优化实际上是基于对CBO和执行计划的深刻理解 • Oracle里的SQL优化不能脱离实际的业务 • 运用之妙，存乎一心
SQL Tuning Method
• 找到执行时间最长、消耗系统资源最多的Top SQL语句； • 查看上述Top SQL语句的执行计划，并结合其资源消耗情况和 相关统计信息、Trace文件来分析其执行计划是否合理； • 通过修正措施（如调整上述Top SQL的执行计划等）来对上述 Top SQL做调整以缩短它们的执行时间，这里调整的指导原则 就是之前刚介绍的Oracle数据库里做SQL优化通常会采用的三 种方法。
Reduce the Workload
• “方法1：降低目标SQL语句的资源消耗”以缩短执行时间，这 是最常用的SQL优化方法。这种方法的核心是要么通过在不更 改业务逻辑的情况下改写SQL来降低目标SQL语句的资源消耗， 要么不改SQL但通过调整执行计划或相关表的数据来降低目标 SQL语句的资源消耗
用合适的函数索引来避免看似无法避免的全表扫描
• select object_name,object_id from t1 where reverse(object_name) like reverse('%EMP'); • create index idx_fun_t1 on t1(reverse(object_name));
How to Implement corrective actions
• 在Oracle数据库里做SQHale Waihona Puke Baidu优化是一个不断迭代、循序渐进的过程。 当你解决了执行时间最长、消耗系统资源最多的Top SQL后，系 统里原先一些执行时间不那么长、消耗资源不那么多的SQL此时 可能就会变成执行时间最长、消耗系统资源最多的Top SQL了。 所以上述三个SQL优化步骤需要被不断的重复执行下去，直到系 统性能已经达到预期目标或者再没有SQL可以被调整
Reduce the Workload
我们在“第四章 Oracle里的查询转换”中曾经提到过一个案例， 在 那个例子里，我们在不更改原有业务逻辑的情况下通过将目标 SQL由原语句： • select pubamnt from v_bc_lcgrppol where grppolno in (select grppolno from v_bc_lcpol where polno = '9022000000000388'); 改写为： • select pubamnt from v_bc_lcgrppol a,(select distinct grppolno grppolno from v_bc_lcpol where polno = '9022000000000388') b where a.grppolno=b.grppolno; 后就实现了将原SQL的逻辑读从200万降低到了6，其执行时间也 从6秒降低到了毫秒级这样一个极好的优化效果
SQL Tuning may depends on your APP Logic
• 这里我想再提出Oracle数据库里SQL优化方法论的第二点： Oracle里SQL优化需要联系实际的业务
SQL Tuning may depends on your APP Logic
• Oracle里SQL优化需要联系实际的业务的实例
重新设计索引来避免不必要的全表扫描
重新设计索引来避免不必要的全表扫描
重新设计索引来避免不必要的全表扫描
• drop index ind_cus_basdata_t_5; • create index ind_cus_basdata_t_5 on cus_basdata_t (cust_uid) online;
Reduce the Workload
• 在很多人眼里，SQL优化就是走索引，就是用走索引来取代全 表扫描。实际上这种认识是非常肤浅的。 • 是，大部分SQL优化的问题都可以通过增加或者减少索引的方 式来解决，但这绝不是全部！
Reduce the Workload
• • • • 例1：用合适的索引来避免不必要的全表扫描 例2：用合适的索引来避免不必要的排序 例3：用合适的函数索引来避免看似无法避免的全表扫描 例4：重新设计索引来避免不必要的全表扫描
SQL Tuning may depends on your APP Logic
SQL Tuning may depends on your APP Logic
SQL Tuning may depends on your APP Logic
• 这套系统的主要业务功能之一是“及时地插入数据”，表中的数据活 动可以说是99%是插入，不到1%的查询，没有delete和update操作 • 由于要插入的大表比较多，索引也很大，而buffer cache有限，因此 插入时的索引维护对插入性能有非常关键的影响 • 如果贸然地增加一个acct_id1列的索引，这个操作可能需要很久才会 体现出其负面后果
用合适的索引来避免不必要的排序
• drop index idx_t1; • select * from (select object_name,object_id from t1 order by object_id desc) where rownum<4;
用合适的索引来避免不必要的排序
• create index idx_t1 on t1(object_id);
How to Identify High-Load SQL
• 也许根本就不用定位，因为我们可能已经知道哪些SQL是我们 的调整目标 • 在Oracle数据库里，定位Top SQL通常所采用的方法就是查看 AWR报告或者Statspack报告，从AWR报告里的“SQL ordered by Elapsed Time”、“SQL ordered by CPU Time”、“SQL ordered by Gets”等部分就能清晰的定位出在采样的时间段 内执行时间最长、消耗系统资源最多的Top SQL
SQL Tuning Methodology
• Oracle里如何做SQL优化 • Oracle里SQL优化的方法论 • Oracle里SQL优化的方法论在实战中的验证
SQL Tuning Goals
Oracle数据库里SQL优化的终极目标就是要缩短目标SQL语句执 行时间。要达到上述目的，我们通常只有如下三种方法可以选择： • 降低目标SQL语句的资源消耗 • 并行执行目标SQL语句 • 平衡系统的资源消耗
用合适的索引来避免不必要的全表扫描
• create index idx_t1 on t1(object_id); • select * from t1 where object_id is null;
用合适的索引来避免不必要的全表扫描
• drop index idx_t1; • create index idx_t1 on t1(object_id,1);
SQL优化的方法论 崔华（dbsnake） Email：allantreycn@yahoo.com.cn
About Me
• 中航信工程师 • Oracle ACE • ACOUG成员
《基于Oracle的SQL优化》
• • • • • • • • 第一章 第二章 第三章 第四章 第五章 第六章 第七章 第八章 Oracle里的优化器 Oracle里的执行计划 Oracle里的Cursor和绑定变量 Oracle里的查询转换 Oracle里的统计信息 Oracle里的Hint Oracle里的并行 Oracle里SQL优化的方法论
How to verify the execution plan
• Get the real execution plan － display_cursor_9i.sql － printsql － $ORACLE_HOME/rdbms/admin/awrsqrpt.sql － $ORACLE_HOME/rdbms/admin/sprepsql • Verify statistics － sosi.txt • 10046/10053 event • http://www.dbsnake.net/books
Parallelize the Workload
• “方法2：并行执行目标SQL语句”，这实际上是以额外的资源 消耗来换取执行时间的缩短，很多情况下使用并行是针对某些 SQL的唯一优化手段。
Balance the Workload
• “方法3：平衡系统的资源消耗” 可以避免不必要的资源争用 所导致的目标SQL语句执行时间的增长。
Reduce the Workload
• 方法1所涉及到的这两种优化手段在Oracle数据库中能否奏效 以及效果的好坏与否很大程度上取决于对CBO和执行计划的理 解程度，对CBO和执行计划理解的越深，这两种优化手段的应 用就会越纯熟，效果就会越好。这也是这本书所要提出的 Oracle数据库里SQL优化方法论的第一点：Oracle里SQL优化的 本质是基于对CBO和执行计划的深刻理解
How to Implement corrective actions
• 如果是不必要的全表扫描/排序而导致了目标SQL的性能问题，我 们可以通过建立合适的索引（包括函数索引、位图索引等）来加 以解决； • 如果是因为各种原因导致的目标SQL的执行计划不稳定，我们可 以通过使用Hint或SQL Profile/SPM来加以解决； • 如果是表或者索引的不良设计导致的目标SQL的性能问题，我们 可以通过重新设计表/索引，重新组织表里的数据来加以解决； • 如果上述调整措施都失效，我们可以考虑用并行来缩短目标SQL 的执行时间； • 如果上述调整措施、包括并行都失效，我们还可以在联系实际业 务的基础上更改目标SQL的执行逻辑，甚至不执行目标SQL，这是 最彻底的优化 :)
How to Implement corrective actions
当定位到Top SQL及通过上述手段分析清楚其问题所在后，接下 来只需秉承“对症下药”的原则采用针对性的调整措施就可以了。 这里的关键就在于分析清楚原因后的“对症下药”，即使是同样 的 症状、由于导致上述症状的原因的不同，依然可能会采用截然相 反的调整手段。在Oracle数据库里，针对Top SQL的调整手段是 五花八门、不一而足的，包括但不限于如下调整措施： • 如果是统计信息不准或是因为CBO计算某些SQL的执行路径 （Access Path）的成本所用公式的先天不足而导致的SQL性能 问题，我们可以通过重新收集统计信息或者手工修改统计信息 或者使用Hint来加以解决； • 如果是SQL语句的写法问题，我们可以通过在不更改业务逻辑 的情况下改写SQL来加以解决；