Oracle Execute Plan原理分析与实例分享之二
一、说明
在上一章中讲了一些实例的分享，这一章我们将更加深入的了解执行计划。
二、执行计划的概念
所谓执行计划，顾名思义，就是对一个查询任务，做出一份怎样去完成任务的详细方案。举个生活中的例子，我从珠海要去英国，我可以选择先去香港然后转机，也可以先去北京转机，或者去广州也可以。但是到底怎样去英国划算，也就是我的费用最少，这是一件值得考究的事情。同样对于查询而言，我们提交的SQL仅仅是描述出了我们的目的地是英国，但至于怎么去，通常我们的SQL中是没有给出提示信息的，是由数据库来决定的。
-----先看一个执行计划的对比，创建一张表jack-------------
1 SQL> create table jack as select * from dba_objects; 2 3 Table created. 4 -----给表增加一个唯一约束-----------------------
5 SQL> alter table jack add constraint jack_pk primary key(object_id); 6 7 Table altered. 8 -----分析一下表及索引--------------
9 SQL> exec dbms_stats.gather_table_stats(user,'JACK',cascade=>true); 10 11 PL/SQL procedure successfully completed. 12 13 SQL> set autotrace traceonly; 14 SQL> select /*+ full(jack) */ count(*) from jack; 15 16 17 Execution Plan 18 ---------------------------------------------------------- 19 Plan hash value: 1205023501 20 21 ------------------------------------------------------------------- 22 | Id | Operation | Name | Rows | Cost (%CPU)| Time | 23 ------------------------------------------------------------------- 24 | 0 | SELECT STATEMENT | | 1 | 288 (1)| 00:00:04 | 25 | 1 | SORT AGGREGATE | | 1 | | | 26 | 2 | TABLE ACCESS FULL| JACK | 72229 | 288 (1)| 00:00:04 | 27 ------------------------------------------------------------------- 28 29 30 Statistics 31 ---------------------------------------------------------- 32 1 recursive calls 33 0 db block gets 34 1034 consistent gets 35 0 physical reads 36 37 SQL> select /*+ index(jack,jack_pk) */ count(*) from jack; 38 39 40 Execution Plan 41 ---------------------------------------------------------- 42 Plan hash value: 3245199814 43 44 -------------------------------------------------------------------- 45 | Id | Operation | Name | Rows | Cost (%CPU)| Time | 46 -------------------------------------------------------------------- 47 | 0 | SELECT STATEMENT | | 1 | 151 (0)| 00:00:02 | 48 | 1 | SORT AGGREGATE | | 1 | | | 49 | 2 | INDEX FULL SCAN| JACK_PK | 72229 | 151 (0)| 00:00:02 | 50 -------------------------------------------------------------------- 51 52 53 Statistics 54 ---------------------------------------------------------- 55 1 recursive calls 56 0 db block gets 57 151 consistent gets 58 0 physical reads
这两个执行计划中，第一个表示求和是通过进行全表扫描来做的，把整个表中数据读入内存来逐条累加；第二个表示根据表中索引，把整个索引读进内存来逐条累加，而不用去读表中的数据。但是这两种方式到底哪种快呢？通常来说可能二比一块，但也不是绝对的。 《全表扫描优于索引访问示例》
上面代码中的例子是一个很简单的演示执行计划差异的例子。对于复杂的SQL(表连接、嵌套子查询等)，执行计划可能几十种甚至上百种，但是到底哪种最好呢？
我们事前并不知道，数据库本身也不知道，但是数据库会根据一定的规则或者统计信息(statistics)去选择一个执行计划，通常来说选择的是比较优的，但也有选择失误的时候，这就是这次讨论的价值所在。
三、Oracle优化器模式
Oracle优化器有两大类，基于规则的和基于代价的，在SQLPLUS中我们可以查看pfile文件或者spfile文件中定义的缺省的优化器模式。
1 SQL> show parameter optimizer_mode 2 3 NAME TYPE VALUE 4 ------------------------------------ ----------- ------------------------------ 5 optimizer_mode string ALL_ROWS
这是Oracle11g R2企业版，我们可以看出，默认安装后数据库优化模式为ALL_ROWS，我们还可以为FIRST_ROWS，而RBO已经在Oracle10g的时候弃用，但是可以用hints来使用。设置优化器模式可以在pfile文件或spfile文件中对整个instance的所有会话设置，也可以单独对某个会话设置：
1 SQL> alter system set optimizer_mode=first_rows; 2 3 System altered. 4 5 SQL> alter session set optimizer_mode=first_rows; 6 7 Session altered.
基于规则的查询，数据库根据表和索引等定义信息，按照一定的规则来产生执行计划；基于代价的查询，数据库根据搜集的表和索引的数据的统计信息(通过analyze命令或者使用dbms_stats包来搜集，下面有一个分析的例子。)综合来决定选取一个数据库认为最优的执行计划(实际上不一定最优)。
----analyze语句是只分析jack表---------
1 SQL> analyze table jack compute statistics; 2 3 Table analyzed. 4 ----下面的语句中加了cascade表示分析了表jack以及连带其它对象(index)-----------
5 SQL> exec dbms_stats.gather_table_stats(user,'JACK',cascade=>true); 6 7 PL/SQL procedure successfully completed.
RULE是基于规则的，CHOOSE表示如果查询的表存在搜集的统计信息则基于代价来执行(在CHOOSE模式下Oracle采用的是FIRST_ROWS),否则基于规则来执行。在基于代价的两种方式中，FIRST_ROWS指执行计划采用最少资源尽快的返回部分结果给客户端，对于排序分页显示这种查询尤其适用；ALL_ROWS指以总体消耗资源最好的方式返回结果给客户端。
基于规则的模式下，数据库的执行计划通常比较稳定。但在基于代价的模式下，我们才有更大的机会选择最优的执行计划。也由于Oracle的很多查询方面的特性必须在基于代价的模式下才能体现出来，所以我们通常不选择RULE(并且Oracle宣称从Oracle10i版本数据库开始将不再支持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写的好不好，就相当于我们同样的要想去英国，但是我的起点在珠海，你的起点却在西藏的最边缘偏僻的一个地方，那不管你做怎样的最优线路选择，你都不如我在珠海去英国所花费的代价小。
四、执行计划的生成
4.1、Explain plan
1 SQL> explain plan for select * from jack; 2 3 Explained. 4 5 SQL> select * from table(dbms_xplan.display()); 6 7 PLAN_TABLE_OUTPUT 8 -------------------------------------------------------------------------------- 9 Plan hash value: 949574992 10 11 -------------------------------------------------------------------------- 12 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 13 -------------------------------------------------------------------------- 14 | 0 | SELECT STATEMENT | | 72229 | 6842K| 288 (1)| 00:00:04 | 15 | 1 | TABLE ACCESS FULL| JACK | 72229 | 6842K| 288 (1)| 00:00:04 | 16 -------------------------------------------------------------------------- 17 18 8 rows selected.
4.2、Autotrace
1 SQL> set timing on -------------记录所用时间 2 SQL> set autotrace on/traceonly ------自动记录执行计划
4.3、SQL_TRACE
"SQL TRACE"是Oracle提供的用于进行SQL跟踪的手段，是强有力的辅助诊断工具。在日常的数据库问题诊断和解决中，"SQL TRACE"是非常常用的方法。
一般，一次跟踪可以分为以下几大步：
1、界定需要跟踪的目标范围，并使用适当的命令启用所需跟踪。
2、经过一段时间后，停止跟踪。此时应该产生了一个跟踪结果文件。
3、找到跟踪文件，并对其进行格式化，然后阅读或分析。
五、查看执行计划
在9i及以前版本，Oracle只能看到DML的执行计划，从Oracle10g开始，可以通过EXPLAIN PLAN FOR查看DDL语句的执行计划了。对于研究CREATE TABLE AS SELECT、CREATE MATERIALIZED VIEW AS SELECT以及CREATE INDEX ALTER INDEX REBUILD等语句有很大的帮助。
举个简单的例子，Oracle的文档上对于索引的建立有如下描述：
The optimizer can use an existing index to build another index.This results in a much faster index build.如果看不到DDL的执行计划，只能根据执行时间的长短去猜测Oracle的具体执行计划，但是这种方法没有足够的说服力。但是通过DDL的执行计划，就使得结果一目了然了。
1 SQL> create table jack as select * from dba_objects; 2 3 Table created. 4 5 SQL> explain plan for 6 2 create index jack_ind on jack(object_id); 7 8 Explained. 9 10 SQL> select * from table(dbms_xplan.display); 11 12 PLAN_TABLE_OUTPUT 13 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 14 Plan hash value: 776580901 15 16 ----------------------------------------------------------------------------------- 17 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 18 ----------------------------------------------------------------------------------- 19 | 0 | CREATE INDEX STATEMENT | | 86173 | 1093K| 342 (1)| 00:00:05 | 20 | 1 | INDEX BUILD NON UNIQUE| JACK_IND | | | | | 21 | 2 | SORT CREATE INDEX | | 86173 | 1093K| | | 22 | 3 | TABLE ACCESS FULL | JACK | 86173 | 1093K| 288 (1)| 00:00:04 | 23 ----------------------------------------------------------------------------------- 24 25 26 PLAN_TABLE_OUTPUT 27 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 28 Note 29 ----- 30 - estimated index size: 3145K bytes 31 32 14 rows selected.
参考资料：
