一、基本概念

执行计划表示了SQL语句执行顺序与方法。

执行计划的存储方式：

一但产生执行计划。这个计划就会和sql语句一起存储在library cache中。sql语句按照hash的算法，产生hash值，这里，我们可以把hash值当做一个PK值，然后根据这个值去寻找library cache相同的hash值。如果有则应用这个hash所对应的执行计划。如果没有则再生成一个新的执行计划。

二、执行计划的使用

2.1启用执行计划

1)        sysdba执行/home/oracle/product/9.2.0/sqlplus/admin/plustrce.sql脚本，建立可以访问v_$sesstat，v_$statname ，v_$mystat这三个视图的role---plustrace，并把plustrace角色赋给执行用户

2)        执行用户必须运行/home/oracle/product/9.2.0/rdbms/admin/utlxplan.sql，建立存储执行计划的表

2.2、显示执行计划的方法

2.2.1启用autotrace

sql>set autotrace on

2.2.2用UTLXPLS.SQL和UTLXPLP.SQL

UTLXPLS.SQL：显示串行的进程的执行计划

UTLXPLP.SQL：显示并行的进程的执行计划

EG：

SQL> explain plan for select * from dual;

SQL> @/home/oracle/9.2.0.1/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------

| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

-------------------------------------------------------------------|   0 | SELECT STATEMENT     |             |       |       |       |

|   1 |  TABLE ACCESS FULL   | DUAL        |       |       |       |

-------------------------------------------------------------------

Note: rule based optimization

2.2.3 用dbms_xplan.display

function display (table_name   varchar2 default 'PLAN_TABLE',

                  statement_id varchar2 default null,

                  format       varchar2 default 'TYPICAL')

- format:

     'BASIC':   只显示基本信息，如操作ID，操作名等

     'TYPICAL': 这是默认值，显示内容包括operation id, name and option,                 #rows, #bytes and optimizer cost.

'ALL':   比typical多了PX server的一些信息，只在并行机制有效

     'SERIAL':只显示serial的信息。不显示并行的信息

 

SQL>  explain plan for SELECT /*+ PARALLEL(e) PARALLEL(d) */ dname,

      MAX(sal), AVG(sal) FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY

      dname;

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

SQL> select * from table(dbms_xplan.display('EMP',null,'ALL'));

三、sql的执行顺序

1、语法分析

       检查语句的语法

2、语义分析

       检查对像是否存和是否可用

3、解析视图

       把查询里的视图分解成基本的表

4、事务说明

       把复杂的查询语句分解成简单的语句

5、决定用RBO（用规则）还是CBO（用分析表后的统计信息来估计成本）

6、产生执行计划

7、执行SQL语句

四、执行计划的执行顺序

select ename,dname   

from emp, dept  

where emp.deptno=dept.deptno    

and dept.dname in ('ACCOUNTING','RESEARCH','SALES','OPERATIONS');

This produces the following explain plan: 

Execution Plan ----------------------------------------------------------   

0      SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=8 Bytes=248)   

1    0   HASH JOIN (Cost=3 Card=8 Bytes=248)    

2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=3 Bytes=36)   

3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=304)

 

在执行计划前有两列数值。第一列是该行计划的ID。第二列是该行计划的父ID。

下面就来看看语句的执行顺序

0      SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=8 Bytes=248)   

ID=0没有父ID，但有一个子ID,子ID=1。所以必须先执行子ID

1    0   HASH JOIN (Cost=3 Card=8 Bytes=248)   

ID=1的父ID是0，子ID是2、3。所以要行执行子ID

2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=3 Bytes=36) 

ID=2的负ID是1，没有子ID，所以可以执行，然后把结果集传给ID=1 

3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=304)

ID=2的负ID是1，没有子ID，所以可以执行，然后把结果集传给ID=1，再由1把结果传给0

所以执行顺序是2,3,1,0

 

 

五、SQL语句的执行方式

每一次向硬盘的读取，最小单元为一个oracle的数据块。最大则是OS的极限。

 

如果返回的是全表扫描的结果，oracle会根据multiblock i/o的大小把HWM下的所有数据都读到内存中。这里，HWM表示记录着该表所写过的最后的块。FTS操作所读入的块会放在LRU（最近最少使用）的末端，确保让它尽快换出内存。

 

如果返回的是index查找的结果，每次的物理读取是一个数据库的数据块，也就是db_block_size。

 

5.1 Full Table Scan (FTS):全表扫描

EG:

Query Plan -----------------------------------------

SELECT STATEMENT     [CHOOSE] Cost=1234  

TABLE ACCESS FULL LARGE [:Q65001] [ANALYZED]

CBO：cost=1234

RBO:cost=

[:Q65001]：说明了这个查询语句是并行执行的

[ANALYZED] ：说明了这个对像是被分析过的

 

5.2 Index Lookup (unique & non-unique)

有5种索引的查找方式

5.2.1 index unique scan

通过提供一个字段的数值，在唯一性索引中返回相应的信息。因为它的唯一性，所以通常返回的是一个值。

EG：

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

5.2.2 index range scan

对索引的一个范围进行查找，这时，SQL通常包含了>、 <、 <>、 >=、 <= 、between

另外。对不唯一的索引的查找也会使用index range scan。

EG:

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]  

 

如果order by 后面的是已经index的字段，则不需要再进行排序操作，因为index已对该字段进行了排序。

EG:

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]

 

5.2.3 index full scan

这是对整个index进行扫描的操作，操作结果是已经排序好的。这种方式只被CBO支持，每次的物理读取是一个数据库的数据块，

EG：

BE_IX是在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]

5.2.4 index fast full scan  

这也是对整个index进行扫描的操作，但返回数据的顺序是无规则的。FFS从7。3开始支持，并需要设置V733_PLANS_ENABLED=TRUE，CBO用INDEX_FFS的 hint才会启用FFS，FFS每次的传输大小是multiblock i/o 。它还可以并行执行。

EG：

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]

SQL> explain plan for select ename from big_emp;

 Query Plan

------------------------------------------

SELECT STATEMENT   [CHOOSE] Cost=1

  INDEX FAST FULL SCAN BE_IX [ANALYZED]

5.2.5 index skip scan

如果要查询的字段不是组合索引的leading column。

如：create index i_emp on emp(empno, ename);

这里ename就不是leading column

那么index skip scan 将会忽略第一个字段而直接去查找第二个字段

EG：

select /*+ index_ss(emp i_emp)*/ job from emp where ename='SMITH';

 

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------

| Id  | Operation         |  Name       | Rows  | Bytes | Cost  |

----------------------------------------------------------------------

|  0 | SELECT STATEMENT   |             |     1 |    12 |    61 |

|  1 |  TABLE ACCESS BY INDEX ROWID| EMP|     1 |    12 |    61 |

|* 2 |   INDEX SKIP SCAN   | I_EMP      |     1 |       |    11 |

5.3 Rowid

这是最快的访问方式，它直接指定行的ID，使oracle可以马上得到该行的块信息从而找到该行。

EG1：

SQL> explain plan for select * from dept where rowid = ':x';

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=18)

   1    0   TABLE ACCESS (BY USER ROWID) OF 'DEPT' (Cost=1 Card=1 Bytes=18)

EG2：根据index返回的rowid去查询table

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    

这里对emp并没有进行全表扫描，而是在index中去查找empno=10的值，    由于ename字段在索引EMP_I1中没有相应的值，所以就返回rowid给table，查找empno=10所对应的ename。

   

如果该语句该成如下。结果就不会返会rowid给table。

EG2：

    SQL> explain plan for select empno from emp where empno=10;

    Query Plan

  ------------------------------------

  SELECT STATEMENT [CHOOSE] Cost=1

INDEX UNIQUE SCAN EMP_I1

5.4 JOINS

join就是把两行的信息结合在一起，如果有多行一起进行join，那么每次只会处理两行的join。

5.4.1 Sort Merge Join (SMJ)

sort merge join的原理是先把表1按sort key进行排序，再把表2按sort key进行排序，最后把两个表merge在一起。Sort是一个很耗资源的操作，尤其是对一些大型的表而言，所以sort merge不是一个特别有效的结合方式。

在缺乏数据的选择性或者可用的索引时，或者两个源表都过于庞大时合并连接将比嵌套连接更加高效。但是，排序合并连接只能用于等价连接。

SQL> explain plan for

select /*+ ordered */ e.deptno,d.deptno

from emp e,dept d

where e.deptno = d.deptno

order by e.deptno,d.deptno; 

Query Plan

-------------------------------------

SELECT STATEMENT [CHOOSE] Cost=17

  MERGE JOIN

    SORT JOIN

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7436977/viewspace-430190/ ，如需转载，请注明出处，否则将追究法律责任。