oracle执行计划的分析

oracle执行计划的分析

目录

查看执行计划

方法一、explain plan for

如果某SQL执行非常长时间才会出结果，甚至慢到返回不了结果，这时候看执行计划就只能用方法1,
或者方法4；

SQL>EXPLAIN PLAN FOR SELECT * FROM SCOTT.EMP; --要解析的SQL脚本 

查看执行计划：
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 
或者
select PLAN_TABLE_OUTPUT from table(dbms_xplan.display());

缺点：没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况），无法判断是处理了多少行，
无法判断表被访问了多少次

方法二、set autotrace on;

不一定是最真实得执行计划，但是能很容易的看出是否排序；
缺点：必须要等到语句真正执行完毕后，才可以出结果，无法看到表被访问了多少次

实例：
一般sys和system用户set autotrace不会出现问题，但是其他普通用户set autotrace的时候可能会遇到SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled的错误，
解决办法参照：http://blog.csdn.net/dbaheng/article/details/16826719
平常都用sys，system用户使用autotrace,今天使用其他用户执行set autotrace 的时候报错：

SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

解决方案：

是该用户没有授予PLUSTRACE这个角色的权限。
SYS AS SYSDBA@oemrep>grant PLUSTRACE to test2;
grant PLUSTRACE to test2
      *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

授权发现PLUSTRACE角色不存在，查看官方文档后执行以下sql
SYS AS SYSDBA@oemrep>@$ORACLE_HOME/sqlplus/admin/plustrce.sql
SYS AS SYSDBA@oemrep>
SYS AS SYSDBA@oemrep>drop role plustrace;

语法：

Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
说明：
前三个参数{OFF | ON | TRACE[ONLY]} 里必需选择一个，而且只能选择一个
后两个参数[EXP[LAIN]] [STAT[ISTICS]]是可选的，也可以都不选择
TRACE[ONLY]的含意是只显示explain和statistic，不显示SQL的结果集，带TRACE[ONLY]的参数的以下的4,5,6最常用的是第4种
只要带上off，后面的[EXP[LAIN]] [STAT[ISTICS]]就无效了
1:set autotrace on;
显示：记录集+explain+statistics
2:set autotrace on explain
显示：记录集+explain
3:set autotrace on statistic
显示 ：记录集+statistics
4:set autotrace traceonly
显示： expain+statistics(不显示数据)
5：set autotrace traceonly statistics
显示： statistics
6：set autotrace traceonly explain
显示： explain
7:set autotrace off
显示：只显示记录集,这也就是默认的SQL PLUS的窗口效果

1、先创建一个包含很多数据的表，

create table test_index as select * from dba_objects;

写一个存储过程，增加表的数据量

create or replace procedure pro_e
as
  i number :=800000;
begin
  while i<=1000000 loop
  insert into a values(i,'周瑜');
  i:=i+1;
 end loop;
end pro_e;
/

2、查看数据量

select count(*) from dba_objects;

3、设置执行计划

set autotrace on;
set autotrace trace explain
  1. 查看没有索引的时候的查询效率：
select * from test_index where object_name='employess';
  1. 关闭执行计划
set autotrace off

执行计划中返回的统计信息的概念解释


方法三、使用dbms_xplan包（DBMS_XPLAN.DISPLAY_CURSOR）查看执行计划：

dbms_xplan.display_cursor：可能通过SQL_ID和child id获得某条语句的一条、或多条执行计划；
缺点：
没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况），无法判断是处理了多少行，无法判断表被访问了多少次。

SQL>EXPLAIN PLAN FOR SELECT * FROM SCOTT.EMP; --要解析的SQL脚本 

查看执行计划
如果不传递任何参数给DISPLAY_CURSOR函数，那么默认显示当前会话最后一条SQL语句的执行计划，如下所示：
SQL>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL));

传递SQL_ID以及FORMAT参数给DISPLAY_CURSOR函数，并配合修饰符控制执行计划的输出，如下所示：
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('315XAN8ZGVTBM',NULL,'ALL'));

利用STATISTICS_LEVEL或/+ GATHER_PLAN_STATISTICS/可以知道表访问的次数，也可以查看真实执行计划并获得统计信息。如下所示：

SET SERVEROUTPUT OFF
ALTER SESSION SET STATISTICS_LEVEL=ALL;

默认值TYPICAL只能显示一个普通的执行计划，不能显示出实际返回的行。

这种方式也是SQL调优中常用的方法，但使用该方法的前提是如下两个条件必须同时满足：

①　一般在会话级别设置参数STATISTICS_LEVEL为ALL，也可以使用/+ GATHER_PLAN_STATISTICS/提示。

②　若DBMS_XPLAN.DISPLAY_CURSOR中的入参SQL_ID输入值为NULL的话，则SERVEROUTPUT必须设置为OFF（SET SERVEROUTPUT OFF），否则会报类似如下的错误：

方法四 通过查看历史sql

只要目标SQL的执行计划所在的Child Cursor还没有被age out出Shared Pool，就可以使用该方法查看SQL执行计划

select sql_text, sql_id, hash_value, child_number
        from v$sql
       where sql_text like 'select count(*) from sh.customers%';

select * from table(dbms_xplan.display_cursor('sql_id/hash_value',
child_cursor_number,'advanced'));

如：
select sql_text, sql_id, hash_value, child_number
        from v$sql
       where sql_text like 'select * from emp';
       
select * from table(dbms_xplan.display_cursor('a2dk8bdn0ujx7',0,'advanced'));

方法五 Events 10046

能获得真实的执行计划、统计信息，以及等待事件，如果SQL语句中有函数调用，SQL中有SQL，将会都被列出，无处遁形，可以跟踪整个程序包； 缺点：步骤繁琐，比较麻烦，无法判断表被访问了多少次，执行计划中的谓词部分不能清晰的展现出来。

方法六 修改statistics_level=all参数

能很明显地看出表被访问次数、预估条数、实际条数、实际事件的信息；
缺点： 无法控制记录输屏打出，不像autotrace有 traceonly 可以控制不将结果打屏输出，看不出递归调用的次数，
看不出物理读的多少（不过逻辑读才是重点）

方法七 awrsqrpt.sql

能显示某一事件段内指定SQL的执行计划

对比：

简单对比查看执行计划的两种方法EXPLAIN PLAN 和 AUTOTRACE

EXPLAIN PLAN 和 AUTOTRACE 都可以查看执行计划。 值得一提的是：
前者只是优化器通过读取数据字典的统计信息做出'最佳'访问路径判断，并没有真正去执行语句；
后者是实际去执行了SQL语句，同时把访问记录数、执行计划、统计信息等打印出来。

因此，EXPLAIN PLAN时常不能反应真实的执行计划，尤其是在绑定变量的应用中。PLSQLDEVELOPER的F5能快速查看执行计划，其实就是EXPLAIN PLAN的方法。

执行计划的顺序

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

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

执行计划中字段解释

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

Operation： 当前操作的内容。

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

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

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

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

谓词说明


Access :

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

Filter：

把所有的数据都访问了，然后过滤掉不需要的数据，这种方式叫做filter 。
表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。
在谓词中主要注意access，要考虑谓词的条件，使用的访问路径是否正确。

Statistics(统计信息)说明


Recursive Calls：Number of recursive calls generated at both the user and system level.
Oracle Database maintains tables used for internal processing. When it needs to change these tables, Oracle Database generates an internal SQL statement, which in turn generates a recursive call. In short, recursive calls are basically SQL performed on behalf of your SQL. So, if you had to parse the query, for example, you might have had to run some other queries to get data dictionary information. These would be recursive calls. Space management, security checks, calling PL/SQL from SQL—all incur recursive SQL calls。

当执行一条SQL语句时，产生的对其他SQL语句的调用，这些额外的语句称之为''recursive calls''或''recursive SQL statements''. 我们做一条insert 时，没有足够的空间来保存row记录，Oracle 通过Recursive Call 来动态的分配空间。

DB Block Gets：Number of times a CURRENT block was requested.

Current mode blocks are retrieved as they exist right now, not in a consistent read fashion. Normally, blocks retrieved for a query are retrieved as they existed when the query began. Current mode blocks are retrieved as they exist right now, not from a previous point in time. During a SELECT, you might see current mode retrievals due to reading the data dictionary to find the extent information for a table to do a full scan (because you need the "right now" information, not the consistent read). During a modification, you will access the blocks in current mode in order to write to them.

DB Block Gets:请求的数据块在buffer能满足的个数

当前模式块意思就是在操作中正好提取的块数目，而不是在一致性读的情况下而产生的块数。正常的情况下，一个查询提取的块是在查询开始的那个时间点上存在的数据块，当前块是在这个时刻存在的数据块，而不是在这个时间点之前或者之后的数据块数目。

Consistent Gets： Number of times a consistent read was requested for a block.

This is how many blocks you processed in "consistent read" mode. This will include counts of blocks read from the rollback segment in order to roll back a block. This is the mode you read blocks in with a SELECT, for example. Also, when you do a searched UPDATE/DELETE, you read the blocks in consistent read mode and then get the block in current mode to actually do the modification.
(Consistent Gets: 数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块)

这里的概念是在处理你这个操作的时候需要在一致性读状态上处理多少个块，这些块产生的主要原因是因为由于在你查询的过程中，由于其他会话对数据块进行操作，而对所要查询的块有了修改，但是由于我们的查询是在这些修改之前调用的，所以需要对回滚段中的数据块的前映像进行查询，以保证数据的一致性。这样就产 生了一致性读。

Physical Reads：
Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.
(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％以上，否则需要增加数据缓冲区的大小。

清空Buffer Cache和数据字典缓存

SQL> alter system flush shared_pool;  //请勿随意在生产环境执行此语句  
System altered  
SQL> alter system flush buffer_cache;  //请勿随意在生产环境执行此语句  
System altered

bytes sent via SQL*Net to client:
Total number of bytes sent to the client from the foreground processes.

bytes received via SQL*Net from client:
Total number of bytes received from the client over Oracle Net.

SQL*Net roundtrips to/from client:
Total number of Oracle Net messages sent to and received from the client.
Oracle Net是把Oracle网络粘合起来的粘合剂。它负责处理客户到服务器和服务器到客户通信，

sorts (memory): 在内存里排序。
Number of sort operations that were performed completely in memory and did not require any disk writes

You cannot do much better than memory sorts, except maybe no sorts at all. Sorting is usually caused by selection criteria specifications within table join SQL operations.

Sorts(disk): 在磁盘上排序。
Number of sort operations that required at least one disk write. Sorts that require I/O to disk are quite resource intensive. Try increasing the size of the initialization parameter SORT_AREA_SIZE.

所有的sort都是优先在memory中做的，当要排序的内容太多，在sort area中放不下的时候，会需要临时表空间，产生sorts(disk)

动态分析
动态统计量收集是Oracle CBO优化器的一种特性。优化器生成执行计划是依据成本cost公式计算出的，如果相关数据表没有收集过统计量，又要使用CBO的机制，就会引起动态采样。
动态采样（dynamic sampling）就是在生成执行计划是，以一个很小的采用率现进行统计量收集。由于采样率低，采样过程快但是不精确，而且采样结果不会进入到数据字典中。

相关的概念

  • Rowid的概念
  • Recursive Sql概念
  • Predicate(谓词)
  • DRiving Table(驱动表)
  • Probed Table(被探查表)
  • 组合索引(concatenated index)
  • 可选择性(selectivity)

Rowid的概念：
rowid是一个伪列，既然是伪列，那么这个列就不是用户定义，而是系统自己给加上的。 对每个表都有一个rowid的伪列，但是表中并不物理存储ROWID列的值。不过你可以像使用其它列那样使用它，但是不能删除该列，也不能对该列的值进行修改、插入。一旦一行数据插入数据库，则rowid在该行的生命周期内是唯一的，即使该行产生行迁移，行的rowid也不会改变。
Recursive SQL概念：
　　有时为了执行用户发出的一个sql语句，Oracle必须执行一些额外的语句，我们将这些额外的语句称之为''recursive calls''或''recursive SQL statements''.如当一个DDL语句发出后，ORACLE总是隐含的发出一些recursive SQL语句，来修改数据字典信息，以便用户可以成功的执行该DDL语句。当需要的数据字典信息没有在共享内存中时，经常会发生Recursive calls，这些Recursive calls会将数据字典信息从硬盘读入内存中。用户不比关心这些recursive SQL语句的执行情况，在需要的时候，ORACLE会自动的在内部执行这些语句。当然DML语句与SELECT都可能引起recursive SQL.简单的说，我们可以将触发器视为recursive SQL.

Row Source（行源）：用在查询中，由上一操作返回的符合条件的行的集合，即可以是表的全部行数据的集合；也可以是表的部分行数据的集合；也可以为对上2个row source进行连接操作（如join连接）后得到的行数据集合。
Predicate（谓词）：
一个查询中的WHERE限制条件
Driving Table（驱动表）：
该表又称为外层表（OUTER TABLE）。这个概念用于嵌套与HASH连接中。如果该row source返回较多的行数据，则对所有的后续操作有负面影响。注意此处虽然翻译为驱动表，但实际上翻译为驱动行源（driving row source）更为确切。一般说来，是应用查询的限制条件后，返回较少行源的表作为驱动表，所以如果一个大表在WHERE条件有有限制条件（如等值限 制），则该大表作为驱动表也是合适的，所以并不是只有较小的表可以作为驱动表，正确说法应该为应用查询的限制条件后，返回较少行源的表作为驱动表。在执行计划中，应该为靠上的那个row source，后面会给出具体说明。在我们后面的描述中，一般将该表称为连接操作的row source 1.
Probed Table（被探查表）：
该表又称为内层表（INNER TABLE）。在我们从驱动表中得到具体一行的数据后，在该表中寻找符合连接条件的行。所以该表应当为大表（实际上应该为返回较大row source的表）且相应的列上应该有索引。在我们后面的描述中，一般将该表称为连接操作的row source 2.
组合索引（concatenated index）：
由多个列构成的索引，如create index idx_emp on emp（col1， col2， col3， ……），则我们称idx_emp索引为组合索引。在组合索引中有一个重要的概念：引导列（leading column），在上面的例子中，col1列为引导列。当我们进行查询时可以使用“where col1 = ？ ”，也可以使用“where col1 = ？ and col2 = ？”，这样的限制条件都会使用索引，但是“where col2 = ？ ”查询就不会使用该索引。所以限制条件中包含先导列时，该限制条件才会使用该组合索引。
可选择性（selectivity）：
比较一下列中唯一键的数量和表中的行数，就可以判断该列的可选择性。 如果该列的“唯一键的数量/表中的行数”的比值越接近1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。在可选择性高的列上进 行查询时，返回的数据就较少，比较适合使用索引查询。

驱动表和被驱动表的问题：
关于驱动表的问题
驱动表普遍认为是由SQL语句的写法决定的，简单的说，就是FROM语句后面的表列表中的最后一个。由于SQL语句是从后向前进行分析，Oracle会根据FROM语句从后到前将各个表依次连接起来。

首先理解执行顺序

先从最开头一直往右看，直到看到最右边的并列的地方，对于不并列的，靠右的先执行：对于并列的，靠上的先执行。
即并列的缩进，从上往下执行，非并列的缩进块，从下往上执行。

1.    如果所连接的表A和B，A表长度远远大于B表，建议从较大的A表上驱动。(简言之 大值为驱动表)
2.  如果Where子句中含有选择性条件,Where No=20，将最具有选择性部分放在表达式最后。
3.  如果只有一个表有索引，另一表无索引，无索引的表通常作为驱动表。

驱动表（Driving Table）：
表连接时首先存取的表，又称外层表（Outer Table），这个概念用于 NESTED LOOPS（嵌套循环） 与 HASH JOIN（哈希连接）中；

如果驱动表返回较多的行数据，则对所有的后续操作有负面影响，故一般选择小表（应用Where限制条件后返回较少行数的表）作为驱动表。

匹配表（Probed Table）：
又称为内层表（Inner Table），从驱动表获取一行具体数据后，会到该表中寻找符合连接条件的行。故该表一般为大表（应用Where限制条件后返回较多行数的表）。

如A表的No列以被索引，而B表的No列没被索引，
则应当B表作为驱动表，A表作为被驱动表。

  • 优化器依次根据下面的规则来作出选择：
  • 优化器选择执行计划使得内部表为全表扫描的NESTED LOOPS连接尽可能的少；
  • 如果采用上面的条件出现了平局的情况，则优化器选择尽可能少出现SORT MERGE操作的执行计划；
  • 如果仍然出现平局的情况，则优化器将选择表访问路径中排名最高的表作为驱动表；
  • 如果这时仍然是平局，则优化器会把 FROM 语句中最后出现的表最为驱动表

所以通常情况下驱动表不是由sql中表的位置决定的， 而是优化器结合多多种情况最终决定的
例如：如下的语句无论如何驱动表都是EMP

select * from emp,dept where EMP.DEPTNO=DEPT.DEPTNO and dept.DEPTNO>10;
select * from dept,emp where EMP.DEPTNO=DEPT.DEPTNO and dept.DEPTNO>10;

oracle访问数据的存取方法

1） 全表扫描（Full Table Scans， FTS）
2） 通过ROWID的表存取（Table Access by ROWID或rowid lookup）
3）索引扫描（Index Scan或index lookup）有4种类型的索引扫描：

  • （1） 索引唯一扫描（index unique scan）
  • （2） 索引范围扫描（index range scan）
    在非唯一索引上都使用索引范围扫描。使用index rang scan的3种情况：
    （a） 在唯一索引列上使用了range操作符（> < <> >= <= between）
    （b） 在组合索引上，只使用部分列进行查询，导致查询出多行
    （c） 对非唯一索引列上进行的任何查询。　　
  • （3） 索引全扫描（index full scan）
  • （4） 索引快速扫描（index fast full scan）

1） 全表扫描（Full Table Scans， FTS）
　　为实现全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件一个多块读操作可以使一次I/O能读取多块数据块（db_block_multiblock_read_count参数设定），而不是只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。在这种访问模式下，每个数据块只被读一次。
　　使用FTS的前提条件：在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5% —— 10%，或你想使用并行查询功能时。
　　使用全表扫描的例子：

　　SQL> explain plan for select * from dual;
　　Query Plan
　　-----------------------------------------
　　SELECT STATEMENT[CHOOSE] Cost=
　　TABLE ACCESS FULL DUAL

2） 通过ROWID的表存取（Table Access by ROWID或rowid lookup）
　　行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是Oracle存取单行数据的最快方法。
　　这种存取方法不会用到多块读操作，一次I/O只能读取一个数据块。我们会经常在执行计划中看到该存取方法，如通过索引查询数据。
　　使用ROWID存取的方法：

　　SQL> explain plan for select * from dept where rowid = ''AAAAyGAADAAAAATAAF''；
　　Query Plan
　　------------------------------------
　　SELECT STATEMENT [CHOOSE] Cost=1
　　TABLE ACCESS BY ROWID DEPT [ANALYZED]

3）索引扫描（Index Scan或index lookup）
　　我们先通过index查找到数据对应的rowid值（对于非唯一索引可能返回多个rowid值），然后根据rowid直接从表中得到具体的数据，这种查找方式称为索引扫描或索引查找（index lookup）。一个rowid唯一的表示一行数据，该行对应的数据块是通过一次i/o得到的，在此情况下该次i/o只会读取一个数据库块。
　　在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的ROWID值。
　　索引扫描可以由2步组成：
　　（1） 扫描索引得到对应的rowid值。
　　（2） 通过找到的rowid从表中读出具体的数据。
　　每步都是单独的一次I/O，但是对于索引，由于经常使用，绝大多数都已经CACHE到内存中，所以第1步的 I/O经常是逻辑I/O，即数据可以从内存中得到。但是对于第2步来说，如果表比较大，则其数据不可能全在内存中，所以其I/O很有可能是物理I/O，这 是一个机械操作，相对逻辑I/O来说，是极其费时间的。所以如果多大表进行索引扫描，取出的数据如果大于总量的5% —— 10%，使用索引扫描会效率下降很多。如下列所示：

　　SQL> explain plan for select empno， ename from emp where empno=10；
　　Query Plan
　　------------------------------------
　　SELECT STATEMENT [CHOOSE] Cost=1
　　TABLE ACCESS BY ROWID EMP [ANALYZED]
　　INDEX UNIQUE SCAN EMP_I1

　　但是如果查询的数据能全在索引中找到，就可以避免进行第2步操作，避免了不必要的I/O，此时即使通过索引扫描取出的数据比较多，效率还是很高的

　　SQL> explain plan for select empno from emp where empno=10;-- 只查询empno列值
　　Query Plan
　　------------------------------------
　　SELECT STATEMENT [CHOOSE] Cost=1
　　INDEX UNIQUE SCAN EMP_I1

　　进一步讲，如果sql语句中对索引列进行排序，因为索引已经预先排序好了，所以在执行计划中不需要再对索引列进行排序

　　SQL> explain plan for select empno, ename from emp
　　where empno > 7876 order by empno;
　　Query Plan
　　--------------------------------------------------------------------------------
　　SELECT STATEMENT[CHOOSE] Cost=1
　　TABLE ACCESS BY ROWID EMP [ANALYZED]
　　INDEX RANGE SCAN EMP_I1 [ANALYZED]

　　从这个例子中可以看到：因为索引是已经排序了的，所以将按照索引的顺序查询出符合条件的行，因此避免了进一步排序操作。
　　根据索引的类型与where限制条件的不同，有4种类型的索引扫描：
　　索引唯一扫描（index unique scan）
　　索引范围扫描（index range scan）
　　索引全扫描（index full scan）
　　索引快速扫描（index fast full scan）

（1） 索引唯一扫描（index unique scan）

　　通过唯一索引查找一个数值经常返回单个ROWID.如果存在UNIQUE 或PRIMARY KEY 约束（它保证了语句只存取单行）的话，Oracle经常实现唯一性扫描。
　　使用唯一性约束的例子：

　　SQL> explain plan for
　　select empno，ename from emp where empno=10；
　　Query Plan
　　------------------------------------
　　SELECT STATEMENT [CHOOSE] Cost=1
　　TABLE ACCESS BY ROWID EMP [ANALYZED]
　　INDEX UNIQUE SCAN EMP_I1

（2） 索引范围扫描（index range scan）

　　使用一个索引存取多行数据，在唯一索引上使用索引范围扫描的典型情况下是在谓词（where限制条件）中使用了范围操作符（如>、<、<>、>=、<=、between）
　　使用索引范围扫描的例子：

　　SQL> explain plan for select empno，ename from emp
　　where empno > 7876 order by empno；
　　Query Plan
　　--------------------------------------------------------------------------------
　　SELECT STATEMENT[CHOOSE] Cost=1
　　TABLE ACCESS BY ROWID EMP [ANALYZED]
　　INDEX RANGE SCAN EMP_I1 [ANALYZED]

　　在非唯一索引上，谓词col = 5可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。
　　使用index rang scan的3种情况：
　　（a） 在唯一索引列上使用了range操作符（> < <> >= <= between）
　　（b） 在组合索引上，只使用部分列进行查询，导致查询出多行
　　（c） 对非唯一索引列上进行的任何查询。

　　（3） 索引全扫描（index full scan）
　　与全表扫描对应，也有相应的全索引扫描。而且此时查询出的数据都必须从索引中可以直接得到。
　　全索引扫描的例子：
　　An Index full scan will not perform single block i/o''s and so it may prove to be inefficient.
　　e.g.
　　Index BE_IX is a concatenated index on big_emp （empno， ename）
　　SQL> explain plan for select empno， ename from big_emp order by empno，ename；
　　Query Plan
　　--------------------------------------------------------------------------------
　　SELECT STATEMENT[CHOOSE] Cost=26
　　INDEX FULL SCAN BE_IX [ANALYZED]

　　（4） 索引快速扫描（index fast full scan）
　　扫描索引中的所有的数据块，与 index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。
　　索引快速扫描的例子：
　　BE_IX索引是一个多列索引： big_emp （empno，ename）

　　SQL> explain plan for select empno，ename from big_emp；
　　Query Plan
　　------------------------------------------
　　SELECT STATEMENT[CHOOSE] Cost=1
　　INDEX FAST FULL SCAN BE_IX [ANALYZED]

　　只选择多列索引的第2列：

　　SQL> explain plan for select ename from big_emp；
　　Query Plan
　　------------------------------------------
　　SELECT STATEMENT[CHOOSE] Cost=1
　　INDEX FAST FULL SCAN BE_IX [ANALYZED]

表之间的连接

    1. 排序 - - 合并连接（Sort Merge Join， SMJ）
    1. 嵌套循环（Nested Loops， NL）
    1. 哈希连接（Hash Join， HJ）
    1. 另外，笛卡儿乘积（Cartesian Product）

这里将首先存取的表称作 row source 1，将之后参与连接的表称作 row source 2；

（1） SORT MERGE JOIN（排序-合并连接）：

假设有查询：

select a.name, b.name from table_A a join table_B b on (a.id = b.id)

内部连接过程：

a) 生成 row source 1 需要的数据，按照连接操作关联列（如示例中的a.id）对这些数据进行排序
b) 生成 row source 2 需要的数据，按照与 a) 中对应的连接操作关联列（b.id）对数据进行排序
c) 两边已排序的行放在一起执行合并操作（对两边的数据集进行扫描并判断是否连接）

延伸：
如果示例中的连接操作关联列 a.id，b.id 之前就已经被排过序了的话，连接速度便可大大提高，因为排序是很费时间和资源的操作，尤其对于有大量数据的表。

故可以考虑在 a.id，b.id 上建立索引让其能预先排好序。不过遗憾的是，由于返回的结果集中包括所有字段，所以通常的执行计划中，即使连接列存在索引，也不会进入到执行计划中，除非进行一些特定列处理（如仅仅只查询有索引的列等）。

排序-合并连接的表无驱动顺序，谁在前面都可以；
排序-合并连接适用的连接条件有： < <= = > >= ，不适用的连接条件有： <> like

（2） NESTED LOOPS（嵌套循环）：

内部连接过程：

a) 取出 row source 1 的 row 1（第一行数据），遍历 row source 2 的所有行并检查是否有匹配的，取出匹配的行放入结果集中
b) 取出 row source 1 的 row 2（第二行数据），遍历 row source 2 的所有行并检查是否有匹配的，取出匹配的行放入结果集中
c) ……

若 row source 1 （即驱动表）中返回了 N 行数据，则 row source 2 也相应的会被全表遍历 N 次。
因为 row source 1 的每一行都会去匹配 row source 2 的所有行，所以当 row source 1 返回的行数尽可能少并且能高效访问 row source 2（如建立适当的索引）时，效率较高。

延伸：
嵌套循环的表有驱动顺序，注意选择合适的驱动表。
嵌套循环连接有一个其他连接方式没有的好处是：可以先返回已经连接的行，而不必等所有的连接操作处理完才返回数据，这样可以实现快速响应。

应尽可能使用限制条件（Where过滤条件）使驱动表（row source 1）返回的行数尽可能少，同时在匹配表（row source 2）的连接操作关联列上建立唯一索引（UNIQUE INDEX）或是选择性较好的非唯一索引，此时嵌套循环连接的执行效率会变得很高。若驱动表返回的行数较多，即使匹配表连接操作关联列上存在索引，连接效率也不会很高。

（3）HASH JOIN（哈希连接） :

哈希连接只适用于等值连接（即连接条件为 = ）
HASH JOIN对两个表做连接时并不一定是都进行全表扫描，其并不限制表访问方式；
内部连接过程简述：

a) 取出 row source 1（驱动表，在HASH JOIN中又称为Build Table） 的数据集，然后将其构建成内存中的一个 Hash Table（Hash函数的Hash KEY就是连接操作关联列），创建Hash位图（bitmap）
b) 取出 row source 2（匹配表）的数据集，对其中的每一条数据的连接操作关联列使用相同的Hash函数并找到对应的 a) 里的数据在 Hash Table 中的位置，在该位置上检查能否找到匹配的数据

Hash Table相关

散列（hash）技术：在记录的存储位置和记录具有的关键字key之间建立一个对应关系 f ，使得输入key后，可以得到对应的存储位置 f(key)，这个对应关系 f 就是散列（哈希）函数；
采用散列技术将记录存储在一块连续的存储空间中，这块连续的存储空间就是散列表（哈希表）；

采用散列技术将记录存储在一块连续的存储空间中，这块连续的存储空间就是散列表（哈希表）；

不同的key经同一散列函数散列后得到的散列值理论上应该不同，但是实际中有可能相同，相同时即是发生了散列（哈希）冲突，解决散列冲突的办法有很多，比如HashMap中就是用链地址法来解决哈希冲突；

哈希表是一种面向查找的数据结构，在输入给定值后查找给定值对应的记录在表中的位置以获取特定记录这个过程的速度很快。

HASH JOIN的三种模式：
  • OPTIMAL HASH JOIN
  • ONEPASS HASH JOIN
  • MULTIPASS HASH JOIN
    1) OPTIMAL HASH JOIN：
    OPTIMAL 模式是从驱动表（也称Build Table）上获取的结果集比较小，可以把根据结果集构建的整个Hash Table都建立在用户可以使用的内存区域里。

optimal_hash_join

连接过程简述：
Ⅰ：首先对Build Table内各行数据的连接操作关联列使用Hash函数，把Build Table的结果集构建成内存中的Hash Table。如图所示，可以把Hash Table看作内存中的一块大的方形区域，里面有很多的小格子，Build Table里的数据就分散分布在这些小格子中，而这些小格子就是Hash Bucket（见上面Wiki的定义）。

Ⅱ：开始读取匹配表（Probed Table）的数据，对其中每行数据的连接操作关联列都使用同上的Hash函数，定位Build Table里使用Hash函数后具有相同值数据所在的Hash Bucket。

Ⅲ：定位到具体的Hash Bucket后，先检查Bucket里是否有数据，没有的话就马上丢掉匹配表（Probed Table）的这一行。如果里面有数据，则继续检查里面的数据（驱动表的数据）是否和匹配表的数据相匹配。

2): ONEPASS HASH JOIN :

从驱动表（也称Build Table）上获取的结果集较大，无法将根据结果集构建的Hash Table全部放入内存中时，会使用 ONEPASS 模式。

one_pass_hash_join

连接过程简述：
Ⅰ：对Build Table内各行数据的连接操作关联列使用Hash函数，根据Build Table的结果集构建Hash Table后，由于内存无法放下所有的Hash Table内容，将导致有的Hash Bucket放在内存里，有的Hash Bucket放在磁盘上，无论放在内存里还是磁盘里，Oracle都使用一个Bitmap结构来反映这些Hash Bucket的状态（包括其位置和是否有数据）。

Ⅱ：读取匹配表数据并对每行的连接操作关联列使用同上的Hash函数，定位Bitmap上Build Table里使用Hash函数后具有相同值数据所在的Bucket。如果该Bucket为空，则丢弃匹配表的这条数据。如果不为空，则需要看该Bucket是在内存里还是在磁盘上。

如果在内存中，就直接访问这个Bucket并检查其中的数据是否匹配，有匹配的话就返回这条查询结果。

如果在磁盘上，就先把这条待匹配数据放到一边，将其先暂存在内存里，等以后积累了一定量的这样的待匹配数据后，再批量的把这些数据写入到磁盘上（上图中的 Dump probe partitions to disk）。

Ⅲ：当把匹配表完整的扫描了一遍后，可能已经返回了一部分匹配的数据了。接下来还有Hash Table中一部分在磁盘上的Hash Bucket数据以及匹配表中部分被写入到磁盘上的待匹配数据未处理，现在Oracle会把磁盘上的这两部分数据重新匹配一次，然后返回最终的查询结果。

3): MULTIPASS HASH JOIN：

当内存特别小或者相对而言Hash Table的数据特别大时，会使用 MULTIPASS 模式。MULTIPASS会多次读取磁盘数据，应尽量避免使用该模式。

posted @ 2023-06-21 15:52  数据库小白（专注）  阅读(2293)  评论(2)  编辑  收藏  举报