ORALCE的执行计划稳定性

 

 

  什么是执行计划

所谓执行计划，顾名思义，就是对一个查询任务，做出一份怎样去完成任务的详细方案。举个生活中的例子，我从珠海要去英国，我可以选择先去香港然后转机，也可以先去北京转机，或者去广州也可以。但是到底怎样去英国划算，也就是我的费用最少，这是一件值得考究的事情。同样对于查询而言，我们提交的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

SQL>

这是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 DOCUMENT），更重要的一个问题就是及时统计数据相对已经比较准确，但是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文档中并没有指明可以这样做，文档中只是说明，如果存在OUTLINES的同时又在SQL中加了HINTS，则会使用OUTLINES而忽略HINTS。这个功能在LECCO将发布的产品中会使用这一功能，这样可以将SQL EXPERT的改写SQL的能力和稳定执行计划的能力结合起来，那么我们就对不能更改源代码的应用具有了相当强大的SQL优化能力。

也许我们会有疑问，假如稳定了执行计划，那还搜集统计信息干吗？这是因为几个原因造成的，首先，现在的执行计划对于未来发生了变化的数据未必就是合适的，存在着当前的执行计划不满足未来数据的变化后的效率，而新的统计信息的情况下所产生的执行计划也并不是全部都合理的。那这个时候，我们可以采用新搜集的统计信息，但是却对新统计信息下不良的执行计划采用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产生一个良好的执行计划的OUTLINES，在利用执行计划稳定性来把SQL和产生的良好执行计划的OUTLINES做绑定。这样就可以在不修改源代码的基础上提高程序的运行效率。这也是惟一的办法。

  怎么使用执行计划稳定性

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

首先我们得创建一个category,把我们所想稳定下来的执行计划放在这个category下，这是一种执行计划的分类，我们可以创建很多category，但是我们的每个session只能选择其中一个category以使用其中的定制好的执行计划。

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

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

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

 

表已创建。

这个脚本utlxplan.sql 在 $ORACLE_HOME/RDBMS/ADMIN目录下

 

然后创建一个实验表。

SQL> create table t as select * from all_objects;

 

表已创建。

 

SQL> create index t_index on t(object_id);

 

索引已创建。（注意我们创建索引的字段是非空字段）

 

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

SQL> set autotrace on

SQL> select count(*) from t;

 

  COUNT(*)

----------

     30658

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=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 compute 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=8Card=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=8Card=30658)

 

SQL> alter session set  create_stored_outlines = false;

 

会话已更改。

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

SQL> analyze table t delete statistics;

表已分析。

 

SQL> select count(*) from t;

  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 use_stored_outlines = my_demo;

 

会话已更改。

 

SQL> select count(*) from t;

 

  COUNT(*)

----------

     30658

 

Execution Plan

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

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

   1    0   SORT (AGGREGATE)

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

这个时候我们发现我们已经成功的使用 my_demo 这个category中保存的执行计划

然后在这里我们要再验证一件事情，那就是在新的ORACLE9.2.0的版本中，关于SQL，即使空格、大小写和换行等不一样，我们依然能使用原来生成的执行计划（也就是数据库能判定为相同的SQL），这在9i以前版本中是不能做到的。

SQL> select

  2          COUNT(*) FROM         T;

 

  COUNT(*)

----------

     30658

 

Execution Plan

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

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

   1    0   SORT (AGGREGATE)

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

在SQLPLUS中我们有换行有空格，并且把 from t→ FROM T，但是数据库依然能认为我们的SQL是一样的，这是在9i中新加入的特性。

然后我们让session终止使用执行计划稳定性再看来看查询。

SQL> alter session set use_stored_outlines = false;

 

会话已更改。

 

SQL> select count(*) from t;

 

  COUNT(*)

----------

     30658

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   SORT (AGGREGATE)

   2    1     TABLE ACCESS (FULL) OF 'T'

可以看出这个时候查询又恢复了全表扫描方式而不是根据索引进行扫描。

我们再来看看相关的一些为执行计划而保存的信息。

SQL> select sql_text,name,category from user_outlines where category = 'MY_DEMO';

 

SQL_TEXT                               NAME                          CATEGORY

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

select count(*) from t     SYS_OUTLINE_030401154756109         MY_DEMO

系统自动命名名称为SYS_OUTLINE_030401154756109。

所属category为MY_DEMO。

SQL文本为select count(*) from t。

这里我们所查询的是view,本质上所有相关信息都保存在下面这些表中。

SQL>  select object_name from dba_objects where owner = 'OUTLN' and  OBJECT_TYPE    = 'TABLE';

 

OBJECT_NAME

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

OL$

OL$HINTS

OL$NODES

也就是说，我们实际上，可以仅仅把这三个表exp出来然后imp进新的数据库并使session使用某个category 就可以实现我们的目的了。建议把这三个表迁移出SYSTEM表空间放在其他特定表空间。但是我们怎么能使得已经存在的应用程序而使用某个category呢？

我们可以通过ORACLE8i开始就提供的 LOGON系统级触发器来实现。

通过SYS用户创建系统级触发器。

create or  replace trigger biti_rainy_logon

after logon database

begin

if (user = ‘RAINY’) then

execute immediate ‘alter session set use_stored_outlines = my_demo’;

end if;

end;

通过这段触发器可以使得用户 RAINY 登陆数据库的时候使用my_demo这个category;当然由这个例子我们也可以很容易的想到一个问题，那就是对于SAP的ERP这种无法获得源码的程序，我们可以通过在logon触发器中设置。

execute immediate ‘alter session set create_stored_outlines = my_demo’;

这样当应用程序使用的时候我们可以搜集SQL和执行计划信息。

  交换两条SQL的OUTLINES

现在我们以817为例子先演示怎么交换2条不同的SQL的OUTLINES。

SQL>  create table t as select * from all_objects;

 

表已创建。

 

SQL> create table t_small as select * from all_objects where rownum < 11;

 

表已创建。

 

SQL> create index t_index on t(object_id);

 

索引已创建。

 

SQL>  create index t_small_index on t_small(object_id);

 

索引已创建。

 

SQL> select count(*)  from t,t_small where t.object_id = t_small.object_id;

 

  COUNT(*)

----------

        10

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   SORT (AGGREGATE)

   2    1     NESTED LOOPS

   3    2       TABLE ACCESS (FULL) OF 'T_SMALL'

   4    2       INDEX (RANGE SCAN) OF 'T_INDEX' (NON-UNIQUE)

 

SQL> select count(*) from t_small,t where t.object_id = t_small.object_id;

 

  COUNT(*)

----------

        10

 

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   SORT (AGGREGATE)

   2    1     NESTED LOOPS

   3    2       TABLE ACCESS (FULL) OF 'T'

   4    2       INDEX (RANGE SCAN) OF 'T_SMALL_INDEX' (NON-UNIQUE)

我们注意这两个查询语句和执行计划中斜体部分，然后我们创建OUTLINES。

SQL> create or replace outline UL1 for category my_demo on select count(*)  from t,t_small where t.object_id = t_small.object_id;

 

概要信息已创建。

 

SQL> create or replace outline UL2 for category my_demo on select count(*)  from t_small,t where t.object_id = t_small.object_id;

 

概要信息已创建。

从这里开始我们尝试交换OUTLINES,我们通过手工update 数据字典表的办法来进行（由于这是817版本，只更新2个表就可以了，9i多了ol$nodes 表），在这里我们要注意刚才我们创建OUTLINES的时候使用了自己特定的名称，如果是自动创建的OUTLINES，则系统会给出一个自动创建的惟一名称，这样需要仔细在表中核对出哪个是你所需要的名称。

SQL> update outln.ol$hints

  2  set ol_name =

  3   decode(

  4    ol_name,

  5     'UL1','UL2',

  6     'UL2','UL1'

  7   )

  8  where ol_name in ('UL1','UL2')

  9  ;

 

已更新20行。

 

SQL> update outln.ol$ ol1

  2  set hintcount = (

  3   select hintcount

  4   from outln.ol$ ol2

  5   where ol2.ol_name in ('UL1','UL2')

  6   and ol2.ol_name != ol1.ol_name

  7   )

  8  where

  9   ol1.ol_name in ('UL1','UL2')

 10  ;

 

已更新2行。

SQL> commit;

 

提交完成。

到这里更新了ol$ 和ol$hints 表之后，我们来观察我们查询的效果。

SQL>  alter session set use_stored_outlines = my_demo;

 

会话已更改。

 

SQL> select count(*)  from t,t_small where t.object_id = t_small.object_id;

 

  COUNT(*)

----------

        10

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=28478 Card=1 Bytes=2

          6)

   1    0   SORT (AGGREGATE)

   2    1     NESTED LOOPS (Cost=28478 Card=23309 Bytes=606034)

   3    2       TABLE ACCESS (FULL) OF 'T' (Cost=53 Card=28425 Bytes=3

          69525)

   4    2       INDEX (RANGE SCAN) OF 'T_SMALL_INDEX' (NON-UNIQUE) (Co

          st=1 Card=82 Bytes=1066)

我们观察上面红色斜体部分，发现我们的查询的执行计划已经被改变成另一句SQL的执行计划了。

 利用工具维护执行计划稳定性

从上面的例子可以看出，假如我们人手来做是件相当麻烦的事情。也许几条需要使用stored outlines的SQL这么做还好，如果成批的，恐怕就麻烦了。更重要的是，一条SQL实际上具有很多的执行计划，怎么为这个SQL产生一个优良的执行计划并测试这也是一件相当麻烦的事情。

值得庆幸的是，在这样的工具诞生之前已经具有了很多的对SQL进行优化、改写的工具。LECCO公司的SQL EXPERT是在SQL优化领域最领先的专家系统，当然其他公司也效仿SQL EXPERT做出了类似产品。在这些产品的帮助下我们可以很容易的为一条SQL生成大量的不同的执行计划，并且包含SQL源码本身被改写了后生成的执行计划。这些所有的执行计划都具有自己的OUTLINES。假如我们能把这些OUTLINES跟SQL语句绑定在一起，那么这就是一件了不起的工作。

这里需要指出的一点是在ORACLE官方文档中早先并没有支持可修改OUTLINES，后来在ORACLE服务站点METALINK得到确认可修改，在9i版本之后得到ORACLE内部开发优化器的人员的确认，可以将不同的SQL的OUTLINES相互绑定，ORACLE可识别绑定是否有效然后决定是否使用绑定的OUTLINES。这给我们开了一个方便之门，使得我们可以随意的绑定OUTLINES，这样为我们使用开发工具进行这步工作奠定了基础。于是我们可以使用SQL优化工具生成大量的OUTLINES然后尝试把这些OUTLINES与SQL绑定，再测试执行计划的改变和效率的改变，然后选择出我们期望的OUTLINES，最后将这些OUTLINES移植到生产环境。

LECCO公司目前已经有产品具备这样的能力，但没有正式发布，还在做进一步的测试。

 

在测试中我们惊讶的发现，将不同SQL的OUTLINES相互绑定的时候，可能会产生新的执行计划，这是ORACLE优化器所产生的，自然我们也不必害怕。甚至对于我们来说这是一件好事情，我们只需要关心OUTLINES绑定后最终生成什么样的执行计划而不是这个OUTLINES本身意味着什么样的执行计划。OUTLINES对于我们人而言依然是晦涩难懂的。在这样的状况下，工具相对于人来说又显示了其强大的一面。所以，我们期望这个工具的正式推出能带给我们更大的惊喜。

  STORED OUTLINES使用总结

STORED OUTLINES是为了维持SQL执行计划稳定性而推出的功能，主要适用于测试环境到产品数据库环境的迁移、当搜集统计信息以采样方式运行、搜集统计信息可能给某些特定SQL带来危害、无法对源代码进行修改等情况下，为了保证产品数据库的良好运行，我们需要稳定执行计划。人为的调整某些特定的SQL，我们可以使用SQLPLUS谨慎的确定某个SQL所需要的OUTLINES。为了更方便有效的进行这项工作，我们可以使用工具从SQL的改写到执行计划的稳定整个一系列的让机器来为我们做这些复杂琐碎的工作。这样的功能是一个让人惊喜的功能。

 

作者简介：冯春培，毕业于北京信息工程学院。曾做电信计费后台程序开发，从事过开发DBA工作和做数据库优化产品设计工作，目前独立对外提供oracle培训和服务。本人热爱ORACLE，在www.itpub.net任数据库管理版块版主(biti_rainy)，个人兴趣主要在oracle internal、performance tuning。对数据库管理、备份与恢复、数据库应用开发、SQL优化均有广泛深入理解。希望大家一起探讨oracle及相关技术。