1、如何分析执行计划
例1:
假设LARGE_TABLE是一个较大的表,且username列上没有索引,则运行下面的语句:
SQL> SELECT * FROM LARGE_TABLE where USERNAME = ‘TEST’;
Query Plan
-----------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)
TABLE ACCESS FULL LARGE_TABLE [:Q65001] [ANALYZED]
在这个例子中,TABLE ACCESS FULL LARGE_TABLE是第一个操作,意思是在LARGE_TABLE表上做全表扫描。当这个操作完成之后,产生的row source中的数据被送往下一步骤进行处理,在此例中,SELECT STATEMENT操作是这个查询语句的最后一步。
Optimizer=CHOOSE 指明这个查询的optimizer_mode,即optimizer_mode初始化参数指定的值,它并不是指语句执行时真的使用了该优化器。决定该语句使用何种优化器的唯一方法是看后面的cost部分。例如,如果给出的是下面的形式,则表明使用的是CBO优化器,此处的cost表示优化器认为该执行计划的代价:
SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)
然而假如执行计划中给出的是类似下面的信息,则表明是使用RBO优化器,因为cost部分的值为空,或者压根就没有cost部分。
SELECT STATEMENT Optimizer=CHOOSE Cost=
SELECT STATEMENT Optimizer=CHOOSE
这样我们从Optimizer后面的信息中可以得出执行该语句时到底用了什么样的优化器。特别的,如果Optimizer=ALL_ROWS| FIRST_ROWS| FIRST_ROWS_n,则使用的是CBO优化器;如果Optimizer=RULE,则使用的是RBO优化器。
cost属性的值是一个在oracle内部用来比较各个执行计划所耗费的代价的值,从而使优化器可以选择最好的执行计划。不同语句的cost值不具有可比性,只能对同一个语句的不同执行计划的cost值进行比较。
[:Q65001] 表明该部分查询是以并行方式运行的。里面的数据表示这个操作是由并行查询的一个slave进程处理的,以便该操作可以区别于串行执行的操作。
[ANALYZED] 表明操作中引用的对象被分析过了,在数据字典中有该对象的统计信息可以供CBO使用。
例2:
假定A、B、C都是不是小表,且在A表上一个组合索引:A(a.col1,a.col2) ,注意a.col1列为索引的引导列。
考虑下面的查询:
select A.col4
from A , B , C
where B.col3 = 10 and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'
Statistics
----------------------------------------------------------
0 recursive calls
8 db block gets
6 consistent gets
0 physical reads
0 redo size
551 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
6 rows processed
在表做连接时,只能2个表先做连接,然后将连接后的结果作为一个row source,与剩下的表做连接,在上面的例子中,连接顺序为B与A先连接,然后再与C连接:
B <---> A <---> C
col3=10 col3=5
如果没有执行计划,分析一下,上面的3个表应该拿哪一个作为第一个驱动表?从SQL语句看来,只有B表与C表上有限制条件,所以第一个驱动表应该为这2个表中的一个,到底是哪一个呢?
B表有谓词B.col3 = 10,这样在对B表做全表扫描的时候就将where子句中的限制条件(B.col3 = 10)用上,从而得到一个较小的row source, 所以B表应该作为第一个驱动表。而且这样的话,如果再与A表做关联,可以有效利用A表的索引(因为A表的col1列为leading column)。
当然上面的查询中C表上也有谓词(C.col3 = 5),有人可能认为C表作为第一个驱动表也能获得较好的性能。让我们再来分析一下:如果C表作为第一个驱动表,则能保证驱动表生成很小的row source,但是看看连接条件A.col2 = C.col2,此时就没有机会利用A表的索引,因为A表的col2列不为leading column,这样nested loop的效率很差,从而导致查询的效率很差。所以对于NL连接选择正确的驱动表很重要。
因此上面查询比较好的连接顺序为(B - - > A) - - > C。如果数据库是基于代价的优化器,它会利用计算出的代价来决定合适的驱动表与合适的连接顺序。一般来说,CBO都会选择正确的连接顺序,如果CBO选择了比较差的连接顺序,我们还可以使用ORACLE提供的hints来让CBO采用正确的连接顺序。如下所示:
select /*+ ordered */ A.col4
from B,A,C
where B.col3 = 10
and A.col1 = B.col1
and A.col2 = C.col2
and C.col3 = 5
既然选择正确的驱动表这么重要,那么让我们来看一下执行计划,到底各个表之间是如何关联的,从而得到执行计划中哪个表应该为驱动表:
在执行计划中,需要知道哪个操作是先执行的,哪个操作是后执行的,这对于判断哪个表为驱动表有用处。判断之前,如果对表的访问是通过rowid,且该rowid的值是从索引扫描中得来得,则将该索引扫描先从执行计划中暂时去掉。然后在执行计划剩下的部分中,判断执行顺序的指导原则就是:最右、最上的操作先执行。具体解释如下:
得到去除妨碍判断的索引扫描后的执行计划:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'
看执行计划的第3列,即字母部分,每列值的左面有空格作为缩进字符。在该列值左边的空格越多,说明该列值的缩进越多,该列值也越靠右。如上面的执行计划所示:第一列值为6的行的缩进最多,即该行最靠右;第一列值为4、5的行的缩进一样,其靠右的程度也一样,但是第一列值为4的行比第一列值为5的行靠上;谈论上下关系时,只对连续的、缩进一致的行有效。
从这个图中我们可以看到,对于NESTED LOOPS部分,最右、最上的操作是TABLE ACCESS (FULL) OF 'B',所以这一操作先执行,所以该操作对应的B表为第一个驱动表(外部表),自然,A表就为内部表了。从图中还可以看出,B与A表做嵌套循环后生成了新的row source ,对该row source进行来排序后,与C表对应的排序了的row source(应用了C.col3 = 5限制条件)进行MSJ连接操作。所以从上面可以得出如下事实:B表先与A表做嵌套循环,然后将生成的row source与C表做排序—合并连接。
通过分析上面的执行计划,我们不能说C表一定在B、A表之后才被读取,事实上,B表有可能与C表同时被读入内存,因为将表中的数据读入内存的操作可能为并行的。事实上许多操作可能为交叉进行的,因为ORACLE读取数据时,如果就是需要一行数据也是将该行所在的整个数据块读入内存,而且还有可能为多块读。
看执行计划时,我们的关键不是看哪个操作先执行,哪个操作后执行,而是关键看表之间连接的顺序(如得知哪个为驱动表,这需要从操作的顺序进行判断)、使用了何种类型的关联及具体的存取路径(如判断是否利用了索引)。
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
转载于:https://blog.51cto.com/19880614/1306071