oracle sql 执行计划分析_oracle sql 执行计划分析

oracle sql 执行计划分析

今天是2013-10-08，时间过的非常快，十月一就这样过去了，回想一下我感觉还是蛮不错的，1号与Amy相约青岛，两个人痛快的玩了两天，我们拍了很多照片，也感受到了彼此的快乐。四号到家开始在家干农活，在昨天的晚上我和我爸妈一直忙到晚上11点才把所有的棒子都剥完了。而现在的我已经正式来到“地狱”，开始我新的奋斗历程。

按照SunnyXu的笔记学习一下oracle的sql执行计划分析。

一、首先创建表

SQL> show user

USER is "RHYS"

SQL> create table A(col1 number(4,0),col2 number(4,0), col4 char(30));

create table B(col1 number(4,0),col3 number(4,0), name_b char(30));

create table C(col2 number(4,0),col3 number(4,0), name_c char(30));

Table created.

SQL>

Table created.

SQL>

Table created.

第二、查看一下执行计划。

1、

SQL> select a.col4 from c,a,b

2 where c.col3=5 and a.col1=b.col1 and a.col2=c.col2 and b.col3=10;

Execution Plan

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

Plan hash value: 1485247927

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 110 | 6 (0)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 110 | 6 (0)| 00:00:01 |

| 2 | MERGE JOIN CARTESIAN| | 1 | 52 | 4 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL | C | 1 | 26 | 2 (0)| 00:00:01 |

| 4 | BUFFER SORT | | 1 | 26 | 2 (0)| 00:00:01 |

|* 5 | TABLE ACCESS FULL | B | 1 | 26 | 2 (0)| 00:00:01 |

| 6 | TABLE ACCESS FULL | A | 1 | 58 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("A"."COL1"="B"."COL1" AND "A"."COL2"="C"."COL2")

3 - filter("C"."COL3"=5)

5 - filter("B"."COL3"=10)

Note

-----

- dynamic sampling used for this statement (level=2)

执行计划主要查看：访问路径，连接顺序，连接方法

执行计划顺序为上内原则，同层次上边先执行，内层先执行。

plan hash value：当sql第一次在shared pool中进行执行的是硬解析并生产该hash值

id，只是一个标号，并不是实际执行顺序

operation：从字面意思也看出来就是操作的类型

name：对象的名字

rows：oracle估计该操作返回的行数

bytes：产生的数据量

cost：表示该sql执行 到此步骤的时候sql执行代价。

该sql的执行步骤如下：

首先执行id 3-》id5-》id4—》id2-》id6-》id1-》id0

首先对id3进行全表扫描过滤条件为filter("C"."COL3"=5)，然后对表b进行全表扫描，条件为filter("B"."COL3"=10),完了之后再进行buffer sort排序，最后把3和4的row source 进行merge join 笛卡尔积操作，并把所有的结果作为row source1 ，也就是驱动表，然后把表A作为被探测表，两者进行hash join。这就是这一个过程信息。

注意此处在id5和id3没有关联的条件，就采用了笛卡尔积，这是不好的现象。

2、

SQL> select /*+ordered*/ a.col4 from c,a,b

2 where c.col3=5 and a.col1=b.col1 and a.col2=c.col2 and b.col3=10;

Execution Plan

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

Plan hash value: 531790806

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 110 | 6 (0)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 110 | 6 (0)| 00:00:01 |

|* 2 | HASH JOIN | | 1 | 84 | 4 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL| C | 1 | 26 | 2 (0)| 00:00:01 |

| 4 | TABLE ACCESS FULL| A | 1 | 58 | 2 (0)| 00:00:01 |

|* 5 | TABLE ACCESS FULL | B | 1 | 26 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("A"."COL1"="B"."COL1")

2 - access("A"."COL2"="C"."COL2")

3 - filter("C"."COL3"=5)

5 - filter("B"."COL3"=10)

Note

-----

- dynamic sampling used for this statement (level=2)

使用hints可以调整optimizer的执行连接方法，在此例中我们指定了ordered使得采用hash join选取from 之后从左到有第一个表c作为驱动表。

执行顺序为：id3全表扫描过滤条件为filter("C"."COL3"=5)-》id4 全表扫描，然后表c为驱动表，a为探测表以此来进行hashjoin-》id5 全表扫描过滤条件为filter("B"."COL3"=10)，此后执行id2为外部表，id5为被探测表进行hash join，从access访问路径可以看出首先是id2为("A"."COL2"="C"."COL2")此后为id1access("A"."COL1"="B"."COL1")。

这是整个sql执行的整个过程。

为了便于理解分析一下数据，

首先我要取到在表c中col3=5的所有数据，然后再内存进行hash，作为hash table，然后我在去使用该hash table去探测A表进行匹配，取出的数据为access("A"."COL2"="C"."COL2")，把最后的匹配结果作为row source，再次建立hash table表，然后再去探测b表，方式为：access("A"."COL1"="B"."COL1")。最终获得了0执行的结果信息。

对于note动态采样信息请参考：

http://www.oracle.com/technetwork/issue-archive/2009/09-jan/o19asktom-086775.html

由于本次没有对表进行analyze所有存有动态取样。

SQL> select /*+ordered use_nl(a c)*/ a.col4 from c,a,b

2 where c.col3=5 and a.col1=b.col1 and a.col2=c.col2 and b.col3=10;

Execution Plan

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

Plan hash value: 1446226736

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 110 | 6 (0)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 110 | 6 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 1 | 84 | 4 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL| C | 1 | 26 | 2 (0)| 00:00:01 |

|* 4 | TABLE ACCESS FULL| A | 1 | 58 | 2 (0)| 00:00:01 |

|* 5 | TABLE ACCESS FULL | B | 1 | 26 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("A"."COL1"="B"."COL1")

3 - filter("C"."COL3"=5)

4 - filter("A"."COL2"="C"."COL2")

5 - filter("B"."COL3"=10)

Note

-----

- dynamic sampling used for this statement (level=2)

在这个语句中，表c和a进行了nested loops然后把结果惊醒hash table在与表b做jash join。

另外对于表有索引的情况进行如下分析。

首先创建表a的组合索引，索引列为(col1，col2)

eg：

SQL> create index inx_col12A on a(col1,col2);

Index created.

SQL> select A.col4

2 from C , A , B

3 where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2

4 and B.col3 = 10;

Execution Plan

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

Plan hash value: 2122808611

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 110 | 4 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 1 | 110 | 4 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 1 | 110 | 4 (0)| 00:00:01 |

| 3 | MERGE JOIN CARTESIAN | | 1 | 52 | 4 (0)| 00:00:01 |

|* 4 | TABLE ACCESS FULL | C | 1 | 26 | 2 (0)| 00:00:01 |

| 5 | BUFFER SORT | | 1 | 26 | 2 (0)| 00:00:01 |

|* 6 | TABLE ACCESS FULL | B | 1 | 26 | 2 (0)| 00:00:01 |

|* 7 | INDEX RANGE SCAN | INX_COL12A | 1 | | 0 (0)| 00:00:01 |

| 8 | TABLE ACCESS BY INDEX ROWID| A | 1 | 58 | 0 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - filter("C"."COL3"=5)

6 - filter("B"."COL3"=10)

7 - access("A"."COL1"="B"."COL1" AND "A"."COL2"="C"."COL2")

Note

-----

- dynamic sampling used for this statement (level=2)

这个比较有意思了。首先看一下执行顺序，首先对表c进行全表扫描过滤条件为col3=5取出数据作为row source1，然后再对b进行全表扫描过滤条件为col3=10，因为走的是merge join 笛卡尔积的排序连接，然后再buffer 进行sort作为row sources2 ，完了之后row source1和row source2作合并连接，完了之后作为row source1 是驱动表，然后再进行index range scan(索引范围扫描)访问路径为： access("A"."COL1"="B"."COL1" AND "A"."COL2"="C"."COL2")，完了之后把结果作为row source1 然后再去与表A进行嵌套循环操作，不过A也就是id8 走的是index rowid。完了之后再进行0获得数据。太繁琐了。呵呵。

SQL> select /*+ ORDERED USE_NL (A C)*/ A.col4

2 from C , A , B

3 where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2

4 and B.col3 = 10;

Execution Plan

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

Plan hash value: 1446226736

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 110 | 6 (0)| 00:00:01 |

|* 1 | HASH JOIN | | 1 | 110 | 6 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 1 | 84 | 4 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL| C | 1 | 26 | 2 (0)| 00:00:01 |

|* 4 | TABLE ACCESS FULL| A | 1 | 58 | 2 (0)| 00:00:01 |

|* 5 | TABLE ACCESS FULL | B | 1 | 26 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("A"."COL1"="B"."COL1")

3 - filter("C"."COL3"=5)

4 - filter("A"."COL2"="C"."COL2")

5 - filter("B"."COL3"=10)

Note

-----

- dynamic sampling used for this statement (level=2)

当改变optimizer选择的执行计划时候，添加了hints，然后我们使用嵌套循环，驱动表为c，被驱动表为A，完了之后再作为row source1做为hash table， 然后与表B进行hash join。

SQL> select /*+ USE_NL (A C)*/ A.col4

2 from C , A , B

3 where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2

4 and B.col3 = 10;

Execution Plan

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

Plan hash value: 2122808611

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 110 | 4 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 1 | 110 | 4 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 1 | 110 | 4 (0)| 00:00:01 |

| 3 | MERGE JOIN CARTESIAN | | 1 | 52 | 4 (0)| 00:00:01 |

|* 4 | TABLE ACCESS FULL | C | 1 | 26 | 2 (0)| 00:00:01 |

| 5 | BUFFER SORT | | 1 | 26 | 2 (0)| 00:00:01 |

|* 6 | TABLE ACCESS FULL | B | 1 | 26 | 2 (0)| 00:00:01 |

|* 7 | INDEX RANGE SCAN | INX_COL12A | 1 | | 0 (0)| 00:00:01 |

| 8 | TABLE ACCESS BY INDEX ROWID| A | 1 | 58 | 0 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - filter("C"."COL3"=5)

6 - filter("B"."COL3"=10)

7 - access("A"."COL1"="B"."COL1" AND "A"."COL2"="C"."COL2")

Note

-----

- dynamic sampling used for this statement (level=2)

SQL>

SQL> set autotrace off

SQL> analyze table a compute statistics;

Table analyzed.

SQL> analyze table b compute statistics;

Table analyzed.

SQL> analyze table c compute statistics;

Table analyzed.

SQL> analyze index inx_col12A compute statistics;

Index analyzed.

SQL> select A.col4

2 from C , A , B

3 where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2

4 and B.col3 = 10;

no rows selected

SQL> set auotrace trace explain

SP2-0158: unknown SET option "auotrace"

SQL> set autotrace trace explain

SQL> r

1 select A.col4

2 from C , A , B

3 where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2

4* and B.col3 = 10

Execution Plan

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

Plan hash value: 2122808611

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 110 | 4 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 1 | 110 | 4 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 1 | 110 | 4 (0)| 00:00:01 |

| 3 | MERGE JOIN CARTESIAN | | 1 | 52 | 4 (0)| 00:00:01 |

|* 4 | TABLE ACCESS FULL | C | 1 | 26 | 2 (0)| 00:00:01 |

| 5 | BUFFER SORT | | 1 | 26 | 2 (0)| 00:00:01 |

|* 6 | TABLE ACCESS FULL | B | 1 | 26 | 2 (0)| 00:00:01 |

|* 7 | INDEX RANGE SCAN | INX_COL12A | 1 | | 0 (0)| 00:00:01 |

| 8 | TABLE ACCESS BY INDEX ROWID| A | 1 | 58 | 0 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - filter("C"."COL3"=5)

6 - filter("B"."COL3"=10)

7 - access("A"."COL1"="B"."COL1" AND "A"."COL2"="C"."COL2")