执行计划

所谓执行计划，顾名思义，就是对一个查询任务，做出一份怎样去完成任务的详细方案。举个生活中的例子，我从珠海要去英国，我可以选择

先去香港然后转机，也可先去北京转机，或者去广州也可以。但是到底怎么去英国划算，也就是我的费用最少，这是一件值得考究的事件。同样

对于查询而言，我们提交的SQL仅仅描述了我们的目的地是英国，但至于怎么去，通常我们的SQL中是没有给出提示信息，是由数据来决定的。

 

我们先简单的看一个执行计划的对比:

SQL>set autotrace traceonly

执行计划一:

SQL>select count(*) from t;

　　COUNT(*)
　　----------
　　24815
　　

Execution Plan

---------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1      0      SORT(AGGREGATE)

2      1      TABLE ACCESS(FULL) OF 'T'

执行计划二：

SQL>select count(*) from t;

　　COUNT(*)
　　----------
　　24815

Execution Plan

-----------------------------------------------

0         SELECT STATEMENT Optimizer=CHOOSE(Cost=26 Card=1)

1         0    SORT(AGGREGATE)

2         1           INDEX(FULL SCAN) OF 'T_INDEX'(NON-UNIQUE)(COST=26 Card=28180)

这两年执行计划中，第一个表示求和是通过进行全表扫描来做的，把整个表中数据读入内存来逐条累加；第二个表示根据表中索引，

把整个索引读进内存来逐条累加，而不用去读表中的数据。但是这种方式到底中那种快呢？通常来说可能二比一快，但也不是绝对的。

这是一个很简单的例子演示执行计划的差异。对于复杂的SQL(表连接、嵌套子查询等),执行计划可能几十种甚至上百种，但是到底那种最好呢？

我们事前并不知道，数据库本身也不知道，但是数据库会根据一定的规则或统计信息(statistics)去选择一个执行计划，通常来说选择的是比较优的。

但也有选择失误的时候，这就是这次讨论的价值所在。

ORACLE优化器模式

ORACLE优化器有两大类，基于规则的和基于代码的，在SQLPLUS中我们可以查看init文件中定义的缺省的优化器模式。

SQL>show parameters optimizer_mode

NAME                   TYPE                 VALUE
-----------------------------------------------------------------------

optimizer_mode   string            CHOOSE

这是ORACLE8.1.7企业版，我们可以看出，默认安装后数据库优化器模式为CHOOSE，我们还可以设置为RULE、FIRST_ROWS、ALL_ROWS.

可以在init文件中对整个instance的所有会话设置，也可以单独对某个会话设置

 

SQL>ALTER SESSION SET optimizer_mode=RULE;

SQL>ALTER SESSION SET optimizer_mode=FIRST_ROWS;

SQL>ALTER SESSION SET optimizer_mode=ALL_ROWS;

基于规则的查询，数据库根据表和索引等定义信息，按照一定的规则来产生执行计划；基于代价的查询，数据库根据搜集的表和索引的数据的统计信息

（通过analyze命令或者使用dbms_stats包来搜集）综合来决定选取一个数据库认为最优的执行计划(实际上不一定最优)。RULE是基于规则的，

CHOOSE表示如果查询的表存在搜集的统计信息则基于代价来执行(在CHOOSE模式下ORACLE采用的是FIRST_ROWS),否则基于规则来执行。

在基于代价的两种试中，FIRST_ROWS指执行计划采用最少资源尽快的返回部分结果给客户端，对于排序分页显示这种查询尤其适用，ALL_ROWS指

总体消耗资源最少的方式返回结果给客户端。

基于规则的模式下，数据库的执行计划通常比较稳定。但在基于代价的模式下，我们才有更大的机会选择最优的执行计划。也由于ORACLE的很多

查询方面的特性必须在基于代价的下才能体现出来，所以我们通常不选择RULE（并且ORACLE宣称从ORACLE 10i版本数据开始不再支持RULE）。

既然是基于代价的模式，也就是说执行计划的选择是根据表、索引等定义和数据的统计信息来决定的，这个统计信息是根据analyze使命或者dbms_stats

包来定期搜集的。首先存在着一种可能，就是由于搜集信息是一个很消耗资源和时间表的动作，尤其当表数据量很大的时候，因为搜集信息是对

整个表数据进行重新完全统计，所以这是我们必须慎重考虑的问题。我们只能在服务器空闲的时候定期的进行信息搜索。这说明我们在一段时期内

统计信息可能和数据库本身的数据并不吻合；另外就是ORACLE的统计数据本身也存在着不精确部分，更重要的的一个问题就是及时统计数据相对已经

比较准确，但是ORACLE优化器的选择也并不是始终是最优的方案。这也倚赖于ORACLE对不同执行计划的代价的计算规则(我们通常无法知道具体

的计算规则的).这好比我们决定从香港还是从北京去英国，车票、机票等实际到底是怎么核算出来的我们并不知道，或者说我们现在了解的价格信息，

在我们乘车前往的时候，真实价格跟我们的预算已经发生了变化。所有的因素，都将影响我们的整个开销。

 

执行计划稳定性能带给我们什么

ORACLE存在着执行计划选择失误的可能。这也是我们经常遇见的一些现象，比如总有人说我的程序在测试数据库中跑得很好，但在产品数据库上

就是跑的很差，甚至后者硬件条件比前还好，这到底是为什么？硬件资源，统计信息，参数设置都可能对执行计划产生影响。由于因素太多，我们总是

对未来怀着一种莫名的恐惧，我的产品数据库上线后到底跑的好不好？于是ORACLE提供了一种稳定执行计划的能力，也就是把在测试环境中运行良

好的执行计划所产生的OUTLINES移植到产品数据库，使得执行计划不会随着其他因素的变化而变化。

 

 

 

 那么OUTLINES是什么呢？先要介绍一个内容，ORACLE提供了在SQL中使用HINTS来引导优化器产生我们想要的执行计划的能力。这在多表连接

复杂查询中特别有效。HINTS的类型很多，可以设置优化器目标(RULE、CHOOSE、FIRST_ROWS、ALL_ROWS），可以指定表连接的顺序，

可以指定使用哪个表的哪个索引等等，可以对SQL进行很多精细的控制，通过这种方式产生我们想要的执行计划的这个HINTS,ORACLE可以存储

这些HINTS，我们称之为OUTLINES。通过STORE OUTLINES可以使得我们拥有以后产生相同执行计划的能力，也就是我们拥有了稳定执行计划

的能力。

这里想给出一个附加的说明就是，实际上，我们通过工具改写SQL，比如使用SQL EXPERT改写后的SQL，这些不仅仅是加了HINTS而且广西都

已经发生了变化的SQL，也可以存储OUTLINES，并可被应用到应用中。但这不是一定生效，我们必须测试检查是否生效。但由于就算给了错误的

OUTLINES，数据库在执行的时候，也只是忽略过去重新生成执行计划而不会返回错误，所以我们才敢放心的这么使用。当然在ORACLE文档

中并没有指明可以这样做，文档中只是说明，如果存在OUTLINE的同时又在SQL中加了HINTS，则会使用HINTS而忽略HINTS。这个功能在LECCO

将发布的产品中会使用这一功能，这样可以将SQL EXPERT的改写SQL的能力和稳定执行计划的能力结合起来，那么我们就对不能列改源代码的应用

具有了相当强大的SQL优化能力。

 

 

 http://www.wzsky.net/html/Program/DataBase/35120.html

 

 

也许我们会有疑问，假如稳定了执行计划，那还搜集统计信息干吗？这是因为几个原因生成的，首先，现在执行计划对于未来发生了变化的数据未必就是合适的。

存在着当前的执行计划不满足未来数据的变化后的效率，而新的统计信息的情况下所产生的执行计划也并不是全部都合理的。那这个时候，我们可

以采用新的搜集的统计信息，但是却对新统计信息下不良的执行计划采用ORACLE提供的执行计划稳定性这个能力固定计划，这样结合起来

我们可以建立满足的高效的数据库运行环境。

我们还需要关注的一个东西，ORACLE提供的dbms_stats包除了具有搜集统计信息的能力，还具有把数据库中统计信息(statistics) export,import

的能力，还具有只搜集统计信息而使得统计信息不应用于数据的能力(把统计信息搜集到一个特定的表中而不是立即生效),这个基础上我们就可

把统计信息export出来再import到一个测试环境中，再运行我们的应用，在测试环境中我们观察最新的统计信息会导致哪能些执行计划发生变化

（DB EXPERT的PLan Version Tracer是模拟不同环境并自动检查不同环境中执行计划变化的工具),是变好一学变差了。我们可以把变差的这一部分

在测试环境中使用hints或者利用工具(SQL EXPERT是在重写SQL这一领导目前最强有力的工具)产生良好的执行计划的SQL，利用这些SQL

可以产生OUTLINES，然后在产品数据库应用最新的统计信息的同时移植进这OUTLINES.

最后说一下我们不得不使用执行计划稳定性能力的场合。我们假定ORACLE的优化器的选择都是准确的，但是优化器选择的基础就是我们的SQL

这些SQL才从根本上了运行效率，这是更重要的一个优化环节。SQL是基础(当然数据库的设计是基础的基础),一个SQL写的好不好，就相当于我们

同样是想要去英国，但是我的起点在海，你的起点却在西藏的最边缘偏僻的一个地方，那不管你做怎么样的最优路线选择，你都不如我在珠海

去英国所花费的代码介。由于这个原因，通常如果是我们自己设计程序，我们可以尝试修改SQL代码，但是，如果应用程序是第三方开发的，或者

我们是在别人的基础上进行的二次开发，比如我们的ERP系统是SAP的，那就算我们在数据库中发现SQL有严重的效率问题，我们也无力对应用程序

进行修改。但是，我们可以在数据库获这些SQL，然后为这些SQL产生一个良好的执行计划的OUTLINE，在利用执行计划稳定性来把SQL

和良好执行计划的OUTLINES做绑定。这样就可以在不修改源代码的基础上提高程序的运行效率。这也唯一的办法。

怎么使用执行计划稳定性

我们先以一个最简单的例子演示怎么使用计划稳定性

首先我们得创建一个category，把我们所想稳定下来的执行讲放在这个category下，这是一种执行计划的分类，我们可以创建很多category

但是我们的每个session只选择其中一个category以使用的定制好的执行计划。

通常我们采用一种最简单的方式来进行这个过程:

首先，为了生成执行和观察计划，我们创建一个保存执行计划的表。

SQL>@E:\oracle\ora81\RDBMS\ADMIN\utlxplan;

这个脚本utlxplan.sql在$ORACLE_HOME\RDBS\ADMIN目录下

创建一个实验表

SQL>create table t as select * from all_objects;

SQL>create index t_index on t(object_id);//object_id为非空字段

这里开始打开执行计划跟踪。

SQL>set autotrace on

SQL>select count(*) from t;

COUNT(*)

------------------

30658

Execution Plan

--------------------------------------------------------

0        SELECT STATEMENT Optimzer=CHOOSE

1        0          SORT(AGGREGATE)

2        1                 TABLE ACCESS(FULL) OF 'T'

Statistics

---------------------------

0 recursive calls

0 db block gets

422 consistent gets

418 physical reads

0 redo size

370 bytes sent via SQL*Net to client

425 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0  sorts(memory)

0  sorts(disk)

1  rows processed

这里我们可以看见生成的执行计划，查询进行了全面扫描，后面其实还跟了一系列的查询执行的时候的统计信息，但由于这不在我们的

讨论范围之内，所以我们将忽略这些信息。

然后我们搜集这个表的统计信息，之后在执行查询发现执行计划已经发生了变化，不再是全表扫描而根据索引进行了扫描。

SQL> analyze table t comput statistics;

SQL > select count(*) from t;

COUNT(*)

----------------

30658

Execution Plan

----------------------------------------------------

0       SELECT STATEMENT Optimizer=CHOOSE(Cost=8 Card=1)

1        0     SORT(AGGREGATE)

2        1      INDEX(FAST FULL SCAN ) OF 'T_INDEX')(NON-UNIQUE)(Cost=8 Card=30658)

从这里开始，我们将尝试创建一个category

一直到会话结束或者set create_stored_outlines=false之间的所有查询，我们都将为这些查询生成并保留一个执行计划，如下

这些计划保存在my_demo这个分类中.

SQL>alter session set create_stored_outlines=my_demo;

SQL>select count(*) from t;

COUNT(*)

----------------

30658

Execution Plan

-------------------------------

0       SELECT STATEMENT Optimizer=CHOOSE(Cost=8 Card=1)

1       0    SORT(AGGREGATE)

2       1     INDEX(FAST FULL SCAN) OF 'T_INDEX'(NON-UNIQUE)(Cost=8 Card=30658)

SQL>alter session set create_sotred_outlines=false;

在这里我们删除表的统计信息，然后再执行查询看看。

SQL>analyze table t delete statistics;

COUNT(*)

-------------------------

30658

Execution Plan

--------------------------------------------

0           SELECT STATEMENT Optimizer=CHOOSE

1             0         SORT(AGGREGATE)

2             1              TABLE ACCESS (FULL) OF 'T'

我们发现这个时候执行计划已经恢复成全表扫描

于是我们尝试使session使用我们生成的category在执行查询

SQL>alter session set user_stored_outlines=my_demo;

SQL>select count(*) from t;

...............