<让oracle跑得更快-5> 执行计划

如果要[b]分析[color=red]某条(不是整体性能，后面还会讲到awr报告，会再次说明)[/color]sql的性能问题[/b]，通常来讲，[color=red]首先要去看sql的执行计划[/color]，看看sql的每一步执行计划是否存在问题。如果一条sql平时执行得都很好，却有一天突然性能很差，如果排除了系统资源和阻塞的原因，那么基本上可以断定是执行计划出了问题。
看懂执行计划便成了sql优化（大多数情况下，sql优化指的是sql的性能问题定位）的先决条件。
在讨论sql执行计划之前，需要知道执行计划当中一个非常重要的概念：[color=red][b]Cardinality(基数)。[/b][/color]

[b]5.1 Cardinality (基数)[/b]
[b]在看执行计划的每一步操作时，当前操作的Cardinality值表示CBO预期从一个行源（row source）返回的记录数，这个行源可能是一个表，一个索引，也可能是一个子查询。[/b]比如：
[img]http://dl2.iteye.com/upload/attachment/0106/2442/7e90f106-9d40-3ff6-9b21-b3ffa9bd23db.png[/img]

在执行计划中，[color=blue]Card就是Cardinality的缩写（在10g之后，Card值被rows替换）[/color]，它表示CBO估算当前操作预期获取的记录数。
在上面的执行计划中，CBO估算从T_INX索引中预期获取193个索引键值（card=193）,通过这193个键值，估算从表中返回的结果集为482条记录。

[color=red]Cardinality的值对于CBO做出正确的执行计划来说至关重要。[/color]如果CBO获得的Cardinality值不够准确（通常是没有做分析或者分析数据过旧导致），在执行成本计算上就会出现偏差，从而导致CBO错误地制定出执行计划。比如：

Create table t as select 1 id, object_name from dba_objects;
Update t set id=99 where rownum=1;
Commit;
Create index t_ind on t(id);

我们创建了一张表T，表里id=99的记录有1条，id=1的记录有50585条，相对id值来说，这是一个数据[b]分布非常不均匀[/b]的表。
我们查询select /*+ dynamic_sampling(t 0) */ * from t where id=1;
执行计划：
[img]http://dl2.iteye.com/upload/attachment/0106/2444/46b5d4f2-f647-3b0e-b3ed-fee9fdc9cac5.png[/img]

表T没有被分析过，提示/*+ dynamic_samping(t 0) */的目的是让CBO无法通过动态采样获得表中实际数据的情况，此时CBO只能根据数据字典表中表T的非常有限的信息（比如表的extents数量，数据块的数量）来猜测表中的数据。
从执行计划看，CBO猜出表中id=1的数据有194条，这个数值相对于表的总数来说是一个比较小的值，所以CBO选择了索引而不是全表扫描。
但实际情况是：
[img]http://dl2.iteye.com/upload/attachment/0106/2446/e2471e7c-6e6f-3583-84f0-ce25c40fd8d2.png[/img]

通过动态采样（10g下，如果表没有做过分析，oracle自动通过动态采样的方式来收集分析数据），CBO估算出表中的实际数据量52987，从执行计划中看到，这个数值非常接近表的实际数据50585，CBO判断id=1的数据基本上等同于表中的数据，所以选择了全表扫描。
可以使用

exec dbms_stats.gather_table_stats(user, ‘t’, cascade=>true);

来启动表的数据收集。

在多表关联查询或sql中有子查询时，每个关联表或子查询的Cardinality的值对主查询的影响非常大，甚至可以说，CBO就是依赖于各个关联表或子查询的cardinality值来计算出最后的执行计划。
[color=blue]对于多表查询，CBO使用每个关联表返回的行数（Cardinality）决定使用什么样的访问方式来做表关联（比如nested loops或是hash join）；对于子查询，它的Cardinality将决定子查询是使用索引还是使用全表扫描的方式访问数据。[/color]
[b]Hash join通常适用于两张关联的表都比较大的时候，否则采用nested loops。[/b]

[b]5.2 sql的执行计划[/b]
如果[b]一条sql[/b]的性能出了问题，首先应该看一下它的执行计划，以便于确定或猜测问题的所在。
生成sql的执行计划是oracle在对sql做硬分析时的一个非常重要的步骤，它制定出一个方案告诉oracle在执行这条sql时以什么样的方式访问数据：索引还是全表扫描，是hash join还是nested loops join等。
[color=red]执行计划可以使用以下方式（当然还有其他方式）获得：
（1） explain plan for
（2） sqlplus命令 set autotrace on
（3） 第三方软件提供的GUI工具，最常见的是Toad, pl/sql[/color]
第二种方式除了生成执行计划之外，还可以输出sql消耗的各种资源的统计，比如LIO(逻辑读)， PIO(物理读)等信息。
方式一：
Explain plan for select * from t1, t2 where t1.id=t2.id;
Select * from table(dbms_xplan.display);
[img]http://dl2.iteye.com/upload/attachment/0106/2448/11ab3171-3436-3bc9-ad8c-e4573094f5ad.png[/img]

方式二：
[img]http://dl2.iteye.com/upload/attachment/0106/2450/4a2621b9-06ec-3f4d-b2cc-1abb73c7f7d0.png[/img]

我们看到，set autotrace的语法不但可以生成执行计划，还可以跟踪sql，并获取sql执行中各种资源的使用情况。
上面两种方法都可以用来[color=red]生成sql的执行计划[/color]，下面讨论如何[color=violet]看懂一个执行计划。[/color]
下面看这样的一个执行计划：
Select t1.* from t1, t2 where t1.id=t2.id and t1.id=5 and t2.name=’A’;
[img]http://dl2.iteye.com/upload/attachment/0106/2452/870d9c78-9051-3773-9a45-331c6e0bda83.png[/img]

看执行计划时：
（1）[b]首先从缩进度最大的行读取[/b]，在这个执行计划中，id=3和id=4是最先被执行的；当两行的缩进一样时，最上面的最先执行，也就是id=3的先执行；
（2）[b]然后是缩进度次之的行[/b]，这里就是id=2的行；然后是id=1，最后是id=0的行。

把上面的过程翻译成语言描述大概如下：
1）从T2表第一行开始读取，全表扫描查看每一行是否符合下面的条件：
T2.id=5 and t2.name=’A’
2）如果符合就拿出一行来，然后到索引IND_T1中找到id=5的值，然后重复，直到把整个T2表扫描完，这个过程叫做nested loops.
3）当整个T2表被扫描完之后，会产生一个结果集，这个结果集是IND_T1的一个索引集，然后oracle根据索引键值上的rowid去T1表中找到相应的记录，就是这一步：
Operation： TABLE ACCESS BY INDEX ROWID
4）然后将结果返回：
Operation： select statement

执行计划如果显示是access,就表示这个谓词条件的值将会影响数据的访问路径（表还是索引，在这里是索引）；而filter表示谓词条件的值并不会影响数据访问路径，只起到过滤的作用。
如果执行计划最下面的一步是：
Note
-----
-Dynamic sampling used for this statement
这一步提示用户CBO当前使用的技术，需要用户在分析执行计划时考虑到这些因素，比如现在提示注意的信息时，当前表示用了动态采集，通过这个提示我们就知道，这个表可能没有做过分析。
如果表分析过，但是分析信息过旧，这时候CBO就不会再使用动态采样，而是使用这些旧的分析数据，从而可能导致错误的执行计划。
另外，如果我们通过hint强制使用RBO时，会有下面提示信息：
Note
-----
-Rule based optimizer used (consider using cbo)
它提醒我们，当前使用的是基于规则的优化器（RBO）,如果你在做sql性能优化时，看到执行计划提示这个信息，那么必须引起注意，分析一下这个执行计划是否是合适的，是否是人为的失误或者其他的原因导致的RBO被使用。