My Oracle SQL Tuning Roadmap PART2

接PART1：My Oracle SQL Tuning Roadmap PART1

二SQL优化Road Map 

一条SQL的优化路线图如下所示：

具体操作步骤：

2.1 制定SQL优化目标

获取待优化SQL、制定优化目标：从AWR、ASH、ORA工具等主动发现有问题的SQL、用户报告有性能问题DBA介入等，通过对SQL的执行情况进行了解，先初步制定SQL的优化目标。

2.2 检查执行计划

explain工具、sql*plus autotrace、dbms_xplan、10046、10053、awrsqrpt.sql等。 执行计划是我们进行SQL优化的核心内容，无计划，不优化。看执行计划有一些技巧，也有很多方式，各种方式之间是有区别的。

2.3 检查统计信息

ORACLE使用DBMS_STATS包对统计信息进行管理，涉及系统统计信息、表、列、索引、分区等对象的统计信息，统计信息是SQL能够使用正确执行计划的保证。我们知道，ORACLE CBO优化器主要是利用统计信息来判断正确的执行路径，JOIN方式的，因此，准确的统计信息是产生正确执行计划的首要条件。

可以从这个图看出，一条SQL产生执行计划需要经过哪些步骤，在我看来：1、正确的查询转换；2、准确的统计信息，是产生正确执行计划的重要保证。当然，还有BUG，或优化器限制等也会导致SQL效率低下，无法产生正确的执行计划。

如图所示：

2.4 检查高效访问结构

重要的访问结构，诸如索引、分区等能够快速提高SQL执行效率。表存储的数据本身，如碎片过多、数据倾斜严重、数据存储离散度大，也会影响效率。

2.5 检查影响优化器的参数

optimizer_mode、optimizer_index_cost_adj、optimizer_dynamic sampling、_optimizer_mjc_enabled、_optimizer_cost_based_transformation、hash_join_enable等对SQL执行计划影响较大。比如有时候我们通过禁用_optimizer_mjc_enabled 参数，让执行计划不要使用笛卡尔积来提升效率，因为这个参数开启有很多问题，所以一般生产库都要求禁用。

还有什么能够影响执行计划呢？对，new features,每个版本的new features，引入的目的都是好的，但是实际使用中，可能触发BUG。比如11g的ACS(自适应游标共享）、automatic serial direct path（自动串行直接路径读）、extended statistics、SQL query result cache等。有的新特性会导致问题，所以需要谨慎使用。

比如11g adaptive cursor sharing,自适应游标共享，它的引入是为了解决使用绑定变量与数据倾斜值，要产生多样性执行计划。因为绑定变量是为了共享执行计划，但是数据倾斜了，有的值要求走索引，有的值要求走全表，这样与使用绑定变量就产生了矛盾。以前是通过cursor_sharing=similar这样的设置可以解决，但是有很多BUG，会产生version count过高的问题，或者我们对不同的值（如果值很少），可以写多条SQL来解决，这都不是好的方案，11g acs引入就是为了解决这些问题，让这些东西交给oracle来做。

但是事与愿违，以后你们遇到执行计划经常变化，有快有慢，首先可以检查acs有没有关闭。
alter system set "_optimizer_extended_cursor_sharing"=NONE;

alter system set "_optimizer_extended_cursor_sharing_rel"=NONE;

alter system set "_optimizer_adaptive_cursor_sharing"=FALSE;

其次cardinality feedback特性也可能导致执行计划变动频繁，也可以检查后关闭：

alter system set "_optimizer_use_feedback"=FALSE;

2.6 SQL语句编写问题

SQL语句结构复杂、使用了不合理的语法，比如UNION代替UNION ALL都可能导致性能低下。 并不是说ORACLE优化器很强大了，我们就可以随便写SQL了，那是不正确的。SQL是一门编程语言，它能够执行的快，是有一些普遍规则的，遵循这种编程语言特性，简化语句，才能写出好的程序。SQL语句编写出了问题，我们就需要改写SQL、调整业务设计等。

2.7 SQL优化器限制导致的执行计划差

这个很重要，统计信息准确，SQL也不复杂，索引也有…,各种好的条件都满足，为什么我的SQL还是差，那么得考虑优化器限制因素了。这里说一点常见的执行计划限制，当semi join与or连用的时候（也就是exists(subquery) or ...或者in (subquery) or...，如果执行计划中因为OR导致有FILTER操作符，就得注意了，可能慢的因素就和OR有关。这时候我们得改写SQL，当然一般改写为UNION或UNION ALL了，或者对SQL从语法和业务上进行大的等价改写。

OK，以上全部检查完毕，我的系统还是很差，功能还是很慢，或者已经无法从SQL本身进行调整提升性能了，那咋办？优化设计，这是终极方法。有些东西不优化设计是无法解决的，比如业务高峰期跑了一堆SQL，CPU已经很吃紧，又不给增加，突然上线一个耗资源的业务，其他SQL已无法调整。那只能优化设计，比如有些耗资源的业务可以换时间段执行等。

以上几点，是我们进行优化需要考虑的地方，可以逐步检查。当然，80%到90%的纯SQL性能调整，我们通过建立索引，收集正确统计信息，改写避免优化器限制，已经能够解决了。