oracle sql 执行计划分析

标签： oracle sql 计划 | 发表时间：2013-10-09 06:28 | 作者：xiaohai20102010
出处：http://blog.csdn.net

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>


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

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")


 

 

作者：xiaohai20102010 发表于2013-10-8 22:28:56 原文链接
阅读：89 评论：0 查看评论

相关 [oracle sql 计划] 推荐：

oracle sql 执行计划分析

- - CSDN博客数据库推荐文章
oracle sql 执行计划分析.     今天是2013-10-08，时间过的非常快，十月一就这样过去了，回想一下我感觉还是蛮不错的，1号与Amy相约青岛，两个人痛快的玩了两天，我们拍了很多照片，也感受到了彼此的快乐. 四号到家开始在家干农活，在昨天的晚上我和我爸妈一直忙到晚上11点才把所有的棒子都剥完了.

oracle如何获得客户端sql执行计划以便优化sql（三）

- - CSDN博客数据库推荐文章
oracle如何获得客户端sql执行计划以便优化sql.    今天是2013-09-25日，继续学习sql优化这一部分，在之前写过怎么获得sql的执行计划两篇笔记. 虽然笔记有点粗糙，但是如果耐心看，还是发现点东西的.   也就是刚刚学习的一个系统包dbms_system中几个procedure的使用：.

oracle sql 优化大全

- - Oracle - 数据库 - ITeye博客
最近遇到了oracle sql优化的问题，找了一下，发现这文章实在不错，跟大家分享一下，如果以后有什么新的改进也会继续补充的. 1     前言… 2 . 2     总纲… 2 . 3     降龙十八掌… 3 . 第一掌 避免对列的操作… 3 . 第二掌 避免不必要的类型转换… 4 . 第三掌 增加查询的范围限制… 4 .

Oracle SQL性能优化

- - 数据库 - ITeye博客
（1）      选择最有效率的表名顺序(只在基于规则的优化器中有效)：. ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表. 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

ORACLE SQL Performance Analyzer的使用

- - CSDN博客数据库推荐文章
通过 SPA，您可以根据各种更改类型（如初始化参数更改、优化器统计刷新和数据库升级）播放特定的. SQL 或整个 SQL 负载，然后生成比较报告，帮助您评估它们的影响.. 在 Oracle Database 11g 之前的版本中，我必须捕获所有 SQL 语句，通过跟踪运行这些语句，. 然后得到执行计划 — 这是一项极其耗时又极易出错的任务.

查询Oracle 耗资源sql

- - 非技术 - ITeye博客
已有 0 人发表留言，猛击->> 这里<<-参与讨论. —软件人才免语言低担保 赴美带薪读研.

oracle执行计划

- - Oracle - 数据库 - ITeye博客
所谓执行计划，顾名思义，就是对一个查询任务，做出一份怎样去完成任务的详细方案. 举个生活中的例子，我从珠海要去英国，我可以选择先去香港然后转机，也 可以先去北京转机，或者去广州也可以. 但是到底怎样去英国划算，也就是我的费用最少，这是一件值得考究的事情. 同样对于查询而言，我们提交的SQL仅仅是 描述出了我们的目的地是英国，但至于怎么去，通常我们的SQL中是没有给出提示信息的，是由数据库来决定的.

oracle各类型SQL的操作流程

- - 数据库 - ITeye博客
•Select * from test  where  object_id=200在体系中是如何运转的. 在PGA中把此条SQL语句hash成一个值；. 接下来根据此hash值到SGA的共享池中去匹配，如果没有，首先查询自己的语句语法是否正确，语义是否正确，是否有权限. 如果都通过则通过CBO解析生成执行计划(如走索引还是全表).

Oracle PL/SQL 编程基础 实例 2

- - CSDN博客数据库推荐文章
if  循环  控制语句 . --编写一个过程，可以 输入一个雇员名，如果该雇员的工资低于2000就给他增加10%.           --判断. --======####案例s33 编写一个过程，可以 输入一个雇员名，如果该雇员的补助不是0就在原基础上增加100，如果是0就加200.           --判断.