首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

oracle sql 执行计区划析(一)

2013-10-10 
oracle sql 执行计划分析(一)oracle sql 执行计划分析今天是2013-10-08,时间过的非常快,十月一就这样过去

oracle sql 执行计划分析(一)

oracle sql 执行计划分析

 

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

一、首先创建表

SQL> show userUSER 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> 


注意当我们对表进行了分析之后,那么就不会有动态分析了,动态分析只是为了进行执行计划的选择。
对于分析表知识详解:
http://blog.csdn.net/xiaohai20102010/article/details/8777158

SQL> set autotrace offSQL> 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 selectedSQL> set auotrace trace explainSP2-0158: unknown SET option "auotrace"SQL> set autotrace trace explainSQL> 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 = 10Execution 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")


 

 

热点排行