内容发布更新时间 : 2024/6/26 17:11:05星期一 下面是文章的全部内容请认真阅读。

Oracle 执行计划

1，什么是执行计划

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

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

的事情。同样对于查询而言，我们提交的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可以使得我们拥有以后产生相同执行计划的能力，也