oracle执行计划学习文档

oracle执行计划学习文档

一、O racl e 执行SQL的步骤

1.1、SQL 语句的两种类型

DDL语句，不共享，每次执行硬解析；

DML语句，会共享，硬解析或者软解析。

1.2、SQL执行步骤

1、语法检测。判断一条SQL语句的语法是否符合SQL的规范；

2、语义检查。语法正确的SQL语句在解析的第二个步骤就是判断该SQL语句所访问的表及列是否准确？用户是否有权限访问或更改相应的表或列？

3、检查共享池中是否有相同的语句存在。假如执行的SQL语句已经在共享池中存在同样的副本，那么该SQL语句将会被软解析，也就是可以重用已解析过的语句的执行计划和优化方案，可以忽略语句解析过程中最耗费资源的步骤，这也是我们为什么一直强调避免硬解析的原因。这个步骤又可以分为两个步骤：

（1）验证SQL语句是否完全一致。

（2）验证SQL语句执行环境是否相同。比如同样一条SQL语句，一个查询会话加了/*+ first_rows */的HINT，另外一个用户加/*+ all_rows */的HINT，他们就会产生不同的执行计划，尽管他们是查询同样的数据。

通过如上三个步骤检查以后，如果SQL语句是一致的，那么就会重用原有SQL语句的执行计划和优化方案，也就是我们通常所说的软解析。如果SQL语句没有找到同样的副本，那么就需要进行硬解析了。

4、Oracle根据提交的SQL语句再查询相应的数据对象是否有统计信息。如果有统计信息的话，那么CBO将会使用这些统计信息产生所有可能的执行计划（可能多达成千上万个）和相应的Cost，最终选择Cost最低的那个执行计划。如果查询的数据对象无统计信息，则按RBO的默认规则选择相应的执行计划。这个步骤也是解析中最耗费资源的，因此我们应该极力避免硬解析的产生。至此，解析的步骤已经全部完成，Oracle将会根据解析产生的执行计划执行SQL语句和提取相应的数据。

二、优化器介绍

Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条SQL 可能有多种执行计划,但在某一时点,一定只有一种执行计划是最优的,花费时间是最少的。

Oracle目前提供RBO和CBO两种优化器。

2.1 RBO（RULE-BASE Optimization）基于规则的优化器

RBO的执行路径和等级：

1、Single Row by Rowid(等级最高)

2、Single Row by Cluster Join

3、Single Row by Hash Cluster Key with Unique or Primary Key

4、Single Row by Unique or Primary Key

5、Clustered Join

6、Hash Cluster Key

7、Indexed Cluster Key

8、Composite Index

9、Single-Column Indexes

10、Bounded Range Search on Indexed Columns

11、Unbounded Range Search on Indexed Columns

12、Sort Merge Join

13、MAX or MIN of Indexed Column

14、ORDER BY on Indexed Column

15、Full Table Scan(等级最低)

优化器根据上述等级优先选择高效的执行路径，以上涉及到的概念在后面详细分析。2.2 CBO（COST-BASE Optimization）基于代价的优化器

Oracle把一个代价引擎集成在数据库内核，用来估计每个执行计划的代价，并量化执行计划所耗费资源，从而选择选择最优的执行计划，查询耗费资源分为以下三种。

I/0代价，即从磁盘读数据到内存的代价,从数据文件中数据块的内容读取到SGA数据高速缓存中，这是数据访问最主要的代价，故优化原则一般以降低查询产生的I/0次数为主；

CPU代价，即处理在内存中数据所需代价，如对数据进行排序(sort)或者连接(join)操作等；

NetWork代价，对访问跨服务器数据库的数据，需要花费的传输操作耗费的资源。

CBO 方式通过表和索引的统计数据计算出相对准确的代价，然后采用最佳的执行计划，所以定期对表和索引进行分析是非常必要的，否则得不偿失，关于数据分析技术详见第三章。

2.3 优化器模式

Optimization-mode 即优化器模式，可选值包括：

1、Rule ，采用的是RBO；

2、CHOOSE，根据实际情况，如果数据字典中包含了引用表的统计数据，则采用CBO优

化器，否则采用RBO；

3、ALL-Rows是CBO使用的第一种优化方法，以数据吞吐量为目标，以便可以使用最少

的资源完成查询；

4、FIRST-ROWS是CBO使用的第二种优化方法，以数据的响应时间为目标，以便快速查

询出开始的几行；

5、FIRST-ROWS_[1|10|100|1000] 是CBO使用的第三种优化方法，选择一个响应时间最

小的计划，迅速查询出结果。

2.4 查看执行计划

2.4.1、查看能执行计划方式

1、通过下面的sql查询：

explain plan for

SELECT * FROM bss_org WHERE bss_org_id=1;

SELECT * FROM table(dbms_xplan.display);

2、直接看pl/sql的explain Plan。

2.4.2 Estimator

共 3 种度量标准：

1、Selectivity

表示有多少 rows 可以通过谓词被选择出来，大小介于 0.0~1.0，0 表示没有 row 被