什么是执行计划？

执行计划是一条查询语句在Oracle中的执行过程或访问路径的描述。

执行计划描述了SQL引擎为执行SQL语句进行的操作；分析SQL语句相关的性能问题或仅仅质疑查询优化器的决定时，必须知道执行计划；所以执行计划常用于sql调优。

怎么查看执行计划？

如果是使用PLSQL的话，那就可以使用PLSQL提供的查询执行计划了,也就是按F5

这些参数的意思:

• 基数(Rows)：Oracle估计的当前步骤的返回结果集行数
• 字节(Bytes)：执行SQL对应步骤返回的字节数
• 耗费(COST)、CPU耗费：Oracle估计的该步骤的执行耗费和CPU耗费
• 时间(Time)：Oracle估计的执行sql对于步骤需要的时间

执行SQL语句explain plan，然后查询结果输出表（普遍方法）：

设置autotrace

autotrace命令如下

序号	命令	解释
1	SET AUTOTRACE OFF	此为默认值，即关闭Autotrace
2	SET AUTOTRACE ON EXPLAIN	只显示执行计划
3	SET AUTOTRACE ON STATISTICS	只显示执行的统计信息
4	SET AUTOTRACE ON	包含2,3两项内容
5	SET AUTOTRACE TRACEONLY	与ON相似，但不显示语句的执行结果

SQL> EXPLAIN PLAN FOR SELECT * FROM EMP;

已解释。

SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

或者：

SQL> select * from table(dbms_xplan.display);

如何读懂执行计划

执行顺序的原则

执行顺序的原则是：由上至下，从右向左 由上至下：在执行计划中一般含有多个节点，相同级别(或并列)的节点，靠上的优先执行，靠下的后执行 从右向左：在某个节点下还存在多个子节点，先从最靠右的子节点开始执行。

一般按缩进长度来判断，缩进最大的最先执行，如果有2行缩进一样，那么就先执行上面的。

以下面的sql为例:

select address.address, city.city, country.country
from address
inner join city on address.city_id = city.city_id
inner join country on city.country_id = country.country_id;

执行计划中字段解释

ID: 一个序号，但不是执行的先后顺序。执行的先后根据缩进来判断。

Operation： 当前操作的内容。

Rows： 当前操作的Cardinality，Oracle估计当前操作的返回结果集。

**Cost（CPU）：**Oracle 计算出来的一个数值（代价），用于说明SQL执行的代价。

Time：Oracle 估计当前操作的时间。

在看执行计划的时候，除了看执行计划本身，还需要看谓词和统计信息。 通过整体信息来判断SQL效率。

谓词说明

Access :

• 通过某种方式定位了需要的数据，然后读取出这些结果集，叫做Access。
• 表示这个谓词条件的值将会影响数据的访问路劲（表还是索引）。

Filter：

• 把所有的数据都访问了，然后过滤掉不需要的数据，这种方式叫做filter 。
• 表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。

在谓词中主要注意access，要考虑谓词的条件，使用的访问路径是否正确。

Statistics(统计信息)说明

recursive calls	产生的递归sql调用的条数。 当执行一条SQL语句时，产生的对其他SQL语句的调用，这些额外的语句称之为''recursive calls''或''recursive SQL statements''. 我们做一条insert 时，没有足够的空间来保存row记录，Oracle 通过Recursive Call 来动态的分配空间。
Db block gets	从buffer cache中读取的block的数量。当前模式块意思就是在操作中正好提取的块数目，而不是在一致性读的情况下而产生的块数。正常的情况下，一个查询提取的块是在查询开始的那个时间点上存在的数据块，当前块是在这个时刻存在的数据块，而不是在这个时间点之前或者之后的数据块数目。
consistent gets	从buffer cache中读取的undo数据的block的数量 。这里的概念是在处理你这个操作的时候需要在一致性读状态上处理多少个块，这些块产生的主要原因是因为由于在你查询的过程中，由于其他会话对数据块进行操作，而对所要查询的块有了修改，但是由于我们的查询是在这些修改之前调用的，所以需要对回滚段中的数据块的前映像进行查询，以保证数据的一致性。这样就产 生了一致性读。
physical reads	从磁盘读取的block的数量
redo size	DML生成的redo的大小
bytes sent via SQL*Net to client	数据库服务器通过SQL*Net向查询客户端发送的查询结果字节数
bytes received via SQL*Net from client	通过SQL*Net接受的来自客户端的数据字节数
SQL*Net roundtrips to/from client	服务器和客户端来回往返通信的Oracle Net messages条数
sorts (memory)	在内存执行的排序量
sorts (disk)	在磁盘上执行的排序量
rows processed	处理的数据的行数

Physical Reads：

Physical Reads:实例启动后，从磁盘读到Buffer Cache数据块数量

就是从磁盘上读取数据块的数量，其产生的主要原因是：

（1） 在数据库高速缓存中不存在这些块

（2） 全表扫描

（3） 磁盘排序

它们三者之间的关系大致可概括为：

逻辑读指的是Oracle从内存读到的数据块数量。一般来说是'consistent gets' + 'db block gets'。当在内存中找不到所需的数据块的话就需要从磁盘中获取，于是就产生了'physical reads'。

Physical Reads通常是我们最关心的，如果这个值很高，说明要从磁盘请求大量的数据到Buffer Cache里，通常意味着系统里存在大量全表扫描的SQL语句，这会影响到数据库的性能，因此尽量避免语句做全表扫描，对于全表扫描的SQL语句，建议增 加相关的索引，优化SQL语句来解决。

关于physical reads ，db block gets 和consistent gets这三个参数之间有一个换算公式：

数据缓冲区的使用命中率=1 - ( physical reads / (db block gets + consistent gets) )。

用以下语句可以查看数据缓冲区的命中率：

SQL>SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets','physical reads');

查询出来的结果Buffer Cache的命中率应该在90％以上，否则需要增加数据缓冲区的大小。

访问数据的方法

Oracle访问表中数据的方法有两种，一种是直接表中访问数据，另外一种是先访问索引，如果索引数据不符合目标SQL，就回表，符合就不回表，直接访问索引就可以。

Oracle直接访问表中数据的方法又分为两种：一种是全表扫描；另一种是ROWID扫描

全表扫描（TABLE ACCESS FULL）

全表扫描是Oracle直接访问数据的一种方法，全表扫描时从第一个区(EXTENT)的第一个块(BLOCK)开始扫描，一直扫描的到表的高水位线(High Water Mark)，这个范围内的数据块都会扫描到，Oracle会对这期间读到的所有数据施加目标SQL的where条件中指定的过滤条件，最后只返回那些满足过滤条件的数据。

全表扫描是采用多数据块一起扫的，并不是一个个数据库扫的，然后我们经常说全表扫描慢是针对数据量很多的情况，数据量少的话，全表扫描并不慢的，不过随着数据量越多，高水位线也就越高，也就是说需要扫描的数据库越多，自然扫描所需要的IO越多，时间也越多。

在Oracle中，如果对目标表不停地插入数据，当分配给该表的现有空间不足时高水位线就会向上移动，但如果你用DELETE语句从该表删除数据， 则高水位线并不会随之往下移动（这在某种程度上契合了"高水位线"的定义，就好比水库的水位，当水库涨水时，水位会往上移，当水库放水后，曾经的最高水位 的痕迹还是会清晰可见）。高水位线的这种特性所带来的副作用是，即使使用DELETE语句删光了目标表中的所有数据，高水位线还是会在原来的位置，这意味着全表扫描该表时Oracle还是需要扫描该表高水位线下的所有数据块，所以此时对该表的全表扫描操作所耗费的时间与之前相比并不会有明显的改观。

ROWID扫描（TABLE ACCESS BY ROWID）

ROWID也就是表数据行所在的物理存储地址，所谓的ROWID扫描是通过ROWID所在的数据行记录去定位。ROWID是一个伪列，数据库里并没有这个列，它是数据库查询过程中获取的一个物理地址，用于表示数据对应的行数，一个ROWID值用于唯一确定数据库表中的的一条记录。

因此通过ROWID 方式来访问数据也是 Oracle 数据库访问数据的实现方式之一。一般情况下，ROWID方式的访问一定以索引访问或用户指定ROWID作为先决条件，因为所有的索引访问方式最终都会转换为通过ROWID来访问数据记录。(注：index full scan 与index fast full scan除外)由于Oracle ROWID能够直接定位一条记录，因此使用ROWID方式来访问数据，极大提高数据的访问效率。

select t.* , rowid from TABLE

随意获取一个ROWID序列：AAAWSJAAFAAAWwUAAA，前6位表示对象编号(Data Object number)，其后3位文件编号(Relative file number)，接着其后6位表示块编号(Block number)， 再其后3位表示行编号(Row number)

ROWID编码方法是：A ~ Z表示0到25；a ~ z表示26到51；0~9表示52到61；+表示62；/表示63；刚好64个字符。

随意找张表查一下文件编号、区编号、行编号，查询后会返回rowid的一系列物理地址和文件编号(rowid_relative_fno(rowid))、块编号(rowid_block_number(rowid))、行编号(rowid_row_number(rowid))

select rowid,
     dbms_rowid.rowid_relative_fno(rowid),
     dbms_rowid.rowid_block_number(rowid),
     dbms_rowid.rowid_row_number(rowid)
from XXXX t;

#表格名称为TABLE的对象编码 
select owner,object_id,data_object_id,status from dba_objects where object_name='TABLE';

相对文件id和绝对文件编码 相对文件id是指相对于表空间，在表空间唯一;绝对文件编码是指相当于全局数据库而言的，全局唯一；下面SQL查询一下相对文件id和绝对文件编码

select file_name,file_id,relative_fno from dba_data_files;

ROWID扫描是指Oracle在访问目标表里的数据时，直接通过数据所在的ROWID去定位并访问这些数据。

从严格意义上来说，Oracle中的ROWID扫描有两层含义：一种是根据用户在SQL语句中输入的ROWID的值直接去访问对应的数据行记录；另外一种是先去访问相关的索引，然后根据访问索引后得到的ROWID再回表去访问对应的数据行记录。

对Oracle中的堆表而言，我们可以通过Oracle内置的ROWID伪列得到对应行记录所在的ROWID的值（注意，这个ROWID只是一个伪 列，在实际的表块中并不存在该列），然后我们还可以通过DBMS_ROWID包中的相关方法（dbms_rowid.rowid_object,dbms_rowid.rowid_relative_fno、dbms_rowid.rowid_block_number和 dbms_rowid.rowid_row_number）将上述ROWID伪列的值翻译成对应数据行的实际物理存储地址。

索引扫描

访问索引（TABLE ACCESS BY INDEX SCAN）的情况就比较多了，可以分为：

• 索引唯一扫描（INDEX UNIQUE SCAN）
• 索引全扫描（INDEX FULL SCAN）
• 索引范围扫描（INDEX RANGE SCAN）
• 索引快速全扫描（INDEX FAST FULL SCAN）
• 索引跳跃式扫描（INDEX SKIP SCAN）

索引范围扫描（INDEX RANGE SCAN）

索引范围扫描（INDEX RANGE SCAN）索引范围扫描(INDEX RANGE SCAN)适用于所有类型的B树索引，一般不包括唯一性索引，因为唯一性索引走索引唯一性扫描。 当扫描的对象是非唯一性索引的情况，where谓词条件为Between、=、<、>等等的情况就是索引范围扫描，注意，可以是等值查询，也可以是范围查询。如果where条件里有一个索引键值列没限定为非空的，那就可以走索引范围扫描，如果改索引列是非空的，*那就走索引全扫描*

同样的SQL建的索引不同，就可能是走索引唯一性扫描，也有可能走索引范围扫描。在同等的条件下，索引范围扫描所需要的逻辑读和索引唯一性扫描对比，逻辑读如何？索引范围扫描可能返回多条记录，所以优化器为了确认，肯定会多扫描，所以在同等条件，索引范围扫描所需要的逻辑读至少会比相应的唯一性扫描的逻辑读多1

测试:唯一索引的范围查询

这个执行计划和上面的执行计划有什么区别呢？其中第一个执行计划是索引范围扫描（INDEX RANGE SCAN），但是第二个执行计划是索引范围降序扫描（INDEX RANGE SCAN DESCENDING）。由此可以看出对于唯一索引，ORACLE是已经进行了正序排列了。也就是唯一索引在进行排序的时候消耗不是很大的，因为它在保存 的时候就按照升序进行保存的。

索引唯一性扫描(INDEX UNIQUE SCAN)

索引唯一性扫描（INDEX UNIQUE SCAN）是针对唯一性索引（UNIQUE INDEX）的扫描，它仅仅适用于where条件里是等值查询的目标SQL。因为扫描的对象是唯一性索引，所以索引唯一性扫描的结果至多只会返回一条记录。

Emp表中empno为primary key，对应一个unique index

索引全扫描（INDEX FULL SCAN）

索引全扫描(INDEX FULL SCAN)适用于所有类型的B树索引(包括唯一性索引和非唯一性索引)。

索引全扫描过程简述：索引全扫描是指扫描目标索引所有叶子块的索引行，但不意味着需要扫描所有的分支块，索引全扫描时只需要访问必要的分支块，然后定位到位于改索引最左边的叶子块的第一行索引行，就可以利用改索引叶子块之间的双向指针链表，从左往右依次顺序扫描所有的叶子块的索引行。

例子一：查询的列有唯一索引，使用索引全扫描（INDEX FULL SCAN）

对于上述SQL（即select empno from emp）而言，表EMP的列EMPNO上存在一个单键值B树主键索引PK_EMP，所以列EMPNO的属性一定是NOT NULL，而该SQL的查询列又只有列EMPNO，所以Oracle此时就可以走对主键索引PK_EMP的索引全扫描。

例子二：查询的列为非唯一索引时，不使用索引全扫描（INDEX FULL SCAN）

emp_temp 表和emp表结构相同，只不过empno为非唯一索引

例子三：order by包含唯一索引，使用索引全扫描（INDEX FULL SCAN）

order by 中为非唯一索引时，不使用索引全扫描（INDEX FULL SCAN）

索引快速全扫描（INDEX FAST FULL SCAN）

索引快速全扫描和索引全扫描很类似，也适用于所有类型的B树索引(包括唯一性索引和非唯一性索引)。和索引全扫描一样，索引快速全扫描也需要扫描目标索引所有叶子块的所有索引行。

索引快速全扫描和索引全扫描区别：

• 索引快速全扫描只适应于CBO(基于成本的优化器)
• 索引快速全扫描可以使用多块读，也可以并行执行
• 索引全扫描会按照叶子块排序返回，而索引快速全扫描则是按照索引段内存储块顺序返回
• 索引快速全扫描的执行结果不一定是有序的，而索引全扫描的执行结果是有序的，因为索引快速全扫描是根据索引行在磁盘的物理存储顺序来扫描的，不是根据索引行的逻辑顺序来扫描的。这是因为索引快速全扫描时Oracle是根据索引行在磁盘上的物理存储顺序来扫描，而不是根据索引行的逻辑顺序来扫描的，所以扫描结果才不一定有序（对于单个索引叶子块中的索引行而言，其物理存储顺序和逻辑存储顺序一致；但对于物理存储位置相邻的索引叶子块而言，块与块之间索引行的物理存储顺序则不一定在逻辑上有序）。

向emp表中插入数据：

BEGIN

FOR I IN 0..1000 LOOP

INSERT INTO EMP(EMPNO,ENAME) VALUES(

I,CONCAT('TBL',I));

END LOOP;

END;

对表EMP及主键索引重新收集一下统计信息：

analyze table emp compute statistics for table for all columns for all indexes;

重新执行

select empno from emp;

对比Index Fast Full Scans与Index Fast Full Scans

INDEX FULL SCAN 与 INDEX FAST FULL SCAN两个长相差不多，因此既有其共性，也有其个性。两者来说其共性是不用扫描 表而是通过索引就可以直接返回所需要的所有数据。这对提高查询性能而言，无疑是一个难得的数据访问方式之一，因为索引中存储的数据通常 是远小于原始表的数据。下面具体来看看两者之间的异同。

select /*+ index_ffs(emp pk_emp) */empno from emp;

select /*+ index(emp pk_emp) */empno from emp;

和index full scan不同，index fast full scan的执行结果并没有按照主键索引PK_EMP的索引键值前导列EMPNO来排序，即索引快速全扫描的执行结果确实不一定是有序的。

• 当select和where中出现的列都存在索引是发生index full scan与index fast full scan的前提
• index fast full scan使用多块读的方式读取索引块，产生db file scattered reads 事件，读取时高效，但为无序读取
• index full scan使用单块读方式有序读取索引块，产生db file sequential reads事件，当采用该方式读取大量索引全扫描，效率低下

索引跳跃式扫描（INDEX SKIP SCAN）

索引跳跃式扫描(INDEX SKIP SCAN)适用于所有类型的***复合B树索引***(包括唯一性索引和非唯一性索引)，它使那些在where条件中没有对目标索引的前导列指定查询条件但同时又对该 索引的非前导列指定了查询条件的目标SQL依然可以用上该索引，这就像是在扫描该索引时跳过了它的前导列，直接从该索引的非前导列开始扫描一样（实际的执行过程并非如此），这也是索引跳跃式扫描中"跳跃"（SKIP）一词的含义。

如图执行计划就有INDEX RANGE SCAN、 INDEX UNIQUE SCAN 。

创建一个测试表EMPLOYEE：

create table employee(gender varchar2(1),employee_id number);
alter table employee modify(employee_id not null);
#创建一个名为IDX_EMPOLYEE的复合B树索引，其中列GENDER是该索引的前导列，列EMPLOYEE_ID是该索引的第二列
create index idx_employee on employee(gender,employee_id);

#插入10,000条记录，其中5,000条记录的列GENDER的值为"F"，另外5,000条记录的列GENDER的值为"M"
begin

for i in 1..5000 loop

insert into employee values ('F',i);

end loop;

commit;

end;

begin

for i in 5001..10000 loop

insert into employee values ('M',i);

end loop;

commit;

end;

对表EMPLOYEE 及索引收集一下统计信息：

analyze table EMPLOYEE compute statistics for table for all columns for all indexes;

执行以下sql

select * from employee where employee_id = 100;

where条件是"employee_id = 100"，即它只对复合B树索引IDX_EMPOLYEE的第二列EMPLOYEE_ID指定了查询条件，但并没有对该索引的前导列GENDER指定任何查询条件。

set autotrace traceonly  

select * from employee where employee_id = 100;

执行计划如下:

从上述显示内容可以看出，Oracle在执行时用上了索引IDX_EMPOLYEE，并且其执行计划走的就是对该索引的索引跳跃式扫描。

这里在没有指定前导列的情况下还能用上述索引，就是因为Oracle帮我们对该索引的前导列的所有distinct值做了遍历。

所谓的对目标索引的所有distinct值做遍历，其实际含义相当于对原目标SQL做等价改写（即把要用的目标索引的所有前导列的distinct 值都加进来）。索引IDX_EMPOLYEE的前导列GENDER的distinct值只有"F"和"M"两个值，所以这里能使用索引 IDX_EMPOLYEE的原因可以简单地理解成是Oracle将范例SQL 9等价改写成了如下形式：

select * from employee where gender = 'F' and employee_id = 100 
union all  
select * from employee where gender = 'M' and employee_id = 100;

Oracle中的索引跳跃式扫描仅仅适用于那些目标索引前导列的distinct值数量较少、后续非前导列的可选择性又非常好的情形，因为索引跳跃式扫描的执行效率一定会随着目标索引前导列的distinct值数量的递增而递减。

表连接方法

执行计划中有如下NESTED LOOPS等等这些，是什么？这种其实就是Oracle中表连接的方法

两个表之间的表连接方法有排序合并连接、嵌套循环连接、哈希连接、笛卡尔连接

• 排序合并连接（merge sort join） merge sort join是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配
• 嵌套循环连接（Nested loop join） Nested loops 工作方式是循环从一张表中读取数据(驱动表outer table)，然后访问另一张表（被查找表 inner table,通常有索引）。驱动表中的每一行与inner表中的相应记录JOIN。类似一个嵌套的循环。对于被连接的数据子集较小的情况，nested loop连接是个较好的选择
• 哈希连接（Hash join） 散列连接是CBO 做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。
• 笛卡尔连接（Cross join） 如果两个表做表连接而没有连接条件，而会产生笛卡尔积，在实际工作中应该尽可能避免笛卡尔积。