oracle 11g cbo,8.1.1 Oracle里SQL优化的本质是基于对CBO和执行计划的深刻理解（7）...

8.1.1  Oracle里SQL优化的本质是基于对CBO和执行计划的深刻理解(7)

以下为重新创建全局索引后上述SQL在生产环境的实际执行结果，我们可以看到它的执行时间从88 s降至0.1 s，逻辑读从452,610降至2,623：

指标

原SQL语句

优化后的SQL语句

执行时间

88

0.1

逻辑读

452,610

2623

在这个例子中，我们通过重新设计索引就把上述目标SQL的执行计划从走全表扫描的哈希连接变为走索引的嵌套循环连接，大幅降低了目标SQL语句的资源消耗，进而大幅缩短了目标SQL的执行时间。

方法2"并行执行目标SQL语句"，这实际上是以额外的资源消耗来换取执行时间的缩短，很多情况下使用并行是针对某些SQL的***优化手段。对于并行执行，我在本书的"第7章 Oracle里的并行"中用一整章的篇幅深入介绍了Oracle数据库里并行执行的各个方面的内容，包括并行的基本概念、如何开启并行、如何控制并行等，这里不再赘述。

方法3"平衡系统的资源消耗"可以避免不必要的资源争用所导致的目标SQL语句执行时间的增长。对某些SQL而言，其执行时间的增长或者不稳定(时快时慢)不是由于其执行计划的问题，而是由于在目标SQL执行时系统处于高负荷状态，执行目标SQL所需要的硬件资源(CPU、内存、I/O等)得不到保障，存在争用的情况。在这样的情况下，如果我们能平衡系统的资源消耗，把一些与目标SQL同时执行的不重要但很消耗系统资源的操作(如报表、批处理等)挪到系统不忙的时候执行，比如把它们挪到晚上执行，那么就可以释放部分系统硬件资源，以确保目标SQL执行时所需要的硬件资源不再存在争用的情况，进而就避免了其执行时间的增长或者不稳定。

我们刚才已经介绍了在Oracle数据库里做SQL优化通常会采用的三种方法，这三种方法实际上是宏观意义的SQL优化的指导原则。下面我们来介绍一下Oracle数据库里做SQL优化的具体步骤。

对存在SQL性能问题的Oracle数据库做SQL优化的三个具体步骤为：

(1)找到执行时间最长、消耗系统资源最多的Top SQL语句。

(2)查看上述Top SQL语句的执行计划，并结合其资源消耗情况和相关统计信息、trace文件来分析其执行计划是否合理。

(3)通过修正措施(如调整上述Top SQL的执行计划等)来对上述Top SQL做调整以缩短它们的执行时间，这里调整的指导原则就是前面介绍的Oracle数据库里做SQL优化通常会采用的三种方法。

在Oracle数据库里，定位Top SQL通常所采用的方法就是查看AWR报告或Statspack报告，从AWR报告里的"SQL ordered by Elapsed Time"、"SQL ordered by CPU Time"、"SQL ordered by Gets"等部分就能清晰地定位出在采样的时间段内执行时间最长、消耗系统资源最多的Top SQL。当然，这里有可能不需要定位，因为你也许早就知道要调整的目标SQL是什么。

在本书的"第2章 Oracle里的执行计划"中我们已经提到，如果目标SQL的执行计划还在Shared Pool中，那就可以使用脚本display_cursor_9i.sql和存储过程printsql来得到其真实的执行计划和资源消耗，如果目标SQL的执行计划已经被age out出Shared Pool了，那么我们可以执行DBMS_XPLAN.DISPLAY_AWR或者使用AWR SQL报告(获得AWR SQL报告的途径通常是手工执行脚本$ORACLE_HOME/rdbms/admin/awrsqrpt.sql，适用于Oracle 10g及其以上的版本)和Statspack SQL报告(获得Statspack SQL报告的途径通常是手工执行脚本$ORACLE_HOME/rdbms/admin/sprepsql，适用于Oracle 9i及其以上的版本)来得到其历史执行计划和资源消耗(DBMS_XPLAN.DISPLAY_AWR仅能查看目标SQL的历史执行计划)。当我们得到了Top SQL的执行计划和资源消耗后，通常还会执行脚本sosi.txt来获得上述Top SQL中相关对象的统计信息来辅助诊断，甚至还可能会对上述Top SQL使用10046/10053等事件，综合上述内容后我们就可以分析出此Top SQL的执行计划是否合理，是否存在性能问题了。display_cursor_9i.sql、printsql和sosi.txt都可以通过我网站上的Books专栏下载，网址为http://www.dbsnake.net/books。

当定位到Top SQL及通过上述手段分析清楚其问题所在后，接下来只需秉承"对症下药"的原则采用针对性的调整措施就可以了。这里的关键就在于分析清楚原因后的"对症下药"，即使是同样的症状，由于导致上述症状的原因不同，依然可能会采用截然相反的调整手段。在Oracle数据库里，针对Top SQL的调整手段是五花八门、不一而足的，包括但不限于如下调整措施。

如果是统计信息不准或是因为CBO计算某些SQL的成本所用公式的先天不足而导致的SQL性能问题，我们可以通过重新收集统计信息，或者手工修改统计信息，或者使用Hint来加以解决。

如果是SQL语句的写法问题，我们可以通过在不更改业务逻辑的情况下改写SQL来加以解决。

如果是不必要的全表扫描/排序而导致的目标SQL的性能问题，我们可以通过建立合适的索引(包括函数索引、位图索引等)来加以解决。

如果是因为各种原因导致的目标SQL的执行计划不稳定，我们可以通过使用Hint、SQL Profile或者SPM来加以解决。

如果是表或者索引的不良设计导致的目标SQL的性能问题，我们可以通过重新设计表/索引，重新组织表里的数据来加以解决。

如果上述调整措施都失效，我们可以考虑用并行来缩短目标SQL的执行时间。

如果上述调整措施(包括并行)都失效，我们还可以在联系实际业务的基础上更改目标SQL的执行逻辑，甚至不执行目标SQL，这是最彻底的优化。:

在Oracle数据库里做SQL优化是一个不断迭代、循序渐进的过程。当你解决了执行时间最长、消耗系统资源最多的Top SQL后，系统里原先一些执行时间不那么长、消耗资源不那么多的SQL此时可能就会变成执行时间最长、消耗系统资源最多的Top SQL了。所以上述三个SQL优化步骤需要被不断地重复执行下去，直到系统性能已经达到预期目标或者再没有SQL可以被调整。

【责任编辑：book TEL：(010)68476606】

点赞 0