首页 > 资讯 > 数据库 >Oracle里另外一些典型的执行计划

395

分享到

Oracle里另外一些典型的执行计划

2024-04-02 19:04:59 395人浏览薄情痞子

摘要

在之前的文章里写了oracle里常见的执行计划，可以参考文章：Http://hbxztc.blog.51cto.com/1587495/1901416，这篇文章里介绍的是其他的一些典型的执行计划。1. AN

在之前的文章里写了oracle里常见的执行计划，可以参考文章：Http://hbxztc.blog.51cto.com/1587495/1901416，这篇文章里介绍的是其他的一些典型的执行计划。

1. AND-EQUAL(INDEX MERGE)

AND-EQUAL又称为INDEX MERGE,顾名思义，INDEX MERGE就是指如果where条件里出现了多个针对不同单列的等值条件，并且这些列上都有单键值的索引，则Oracle可能会以相应的单个等值条件去分别扫描这些索引；然后Oracle会合并这些扫描单个索引所得到的rowid集合，如果能从这些集合中找到相同的rowid，那么这个rowid就是目标sql最终执行结果所对应的rowid。最后，Oracle只需要用这些rowid回表就能得到目标SQL的最终执行结果。

AND-EQUAL在执行计划中对应的关键字就是“AND-EQUAL”，我们可以使用Hint来强制让Oracle走AND-EQUAL。

看一个实例：

zx@MYDB>create table emp_temp as select * from scott.emp;

Table created.

zx@MYDB>create index idx_mgr on emp_temp(mgr);

Index created.

zx@MYDB>create index idx_deptno on emp_temp(deptno);

Index created.

zx@MYDB>select  empno,job from emp_temp where mgr=7902 and deptno=20;

     EMPNO JOB
---------- ---------------------------
      7369 CLERK

Oracle里另外一些典型的执行计划

从上述显示内容中可以看出，现在此SQL的执行计划走的是对索引IDX_MGR和IDX_DEPTNO的AND-EQUAL。

2. INDEX JOIN

INDEX JOIN很容易引起误解，因为它并不是指通常意义上针对多表的表连接。这里INDEX JOIN指的是针对单表上的不同索引之间的连接。

还以上面的EMP_TEMP为例，已经在列MGR和DEPTNO上分别创建了两个单键值的B*Tree索引，如果此时执行SQL语句“select mgr,deptno from emp_temp”，因为这里要查询的列MGR和DEPTNO均可来源于索引IDX_MGR和IDX_DEPTNO(不考虑NULL值)，不用回表，所以除了常规的执行方法之外，Oracle还可以采用如下方法：分别扫描索引IDX_MGR和IDX_DEPTNO，得到的结果集分别记为结果集1和结果集2，然后将结果集1和2做一个连接，连接条件就是“结果集1.rowid=结果集2.rowid”，这样得到的最终连接结果(不用回表)就是上述SQL的执行结果。

很显然，针对上述SQL的INDEX JOIN的执行效率是不如我们直接在列MGR和DEPTNO上建一个组合索引，然后直接扫描该组全索引的效率高。INDEX JOIN只是为CBO提供了一种可选的执行路径，大多数情况下，它只是额外多出的一种选择而已。

看一下例子：

zx@MYDB>delete from emp_temp where mgr is null;

1 row deleted.

zx@MYDB>commit;

Commit complete.

zx@MYDB>alter table emp_temp modify mgr not null;

Table altered.

zx@MYDB>alter table emp_temp modify deptno not null;

Table altered.

zx@MYDB>select mgr,deptno from emp_temp;

       MGR     DEPTNO
---------- ----------
      7839         10
......
      7698         30

13 rows selected.

Oracle里另外一些典型的执行计划

从上述显示内容可以看出，现在目标SQL的执行计划走的是对索引IDX_MGR和IDX_DEPTNO的HASH JOIN。

3. VIEW

Oracle在处理包含视图的SQL时，根据该视图是否能做为视图合并(View Merging)，其对应的执行计划有如下两种形式。

如果可以做视图合并，则Oracle在执行该SQL时可以直接针对该视图的基表，此时SQL的执行计划中很可能不会出现关键字“VIEW”(不能完全依赖关键字“VIEW”的出现与否来判断Oracle是否做了视图合并，因为对于某些SQL而言，即使Oracle已经做了视图合并但其所对应的执行计划中可能还会显示关键字“VIEW”)。
如果不能做视图合并，则Oracle将把该视图看作一个整体并独立地执行它，此时SQL的执行计划中将会出现关键字“VIEW”。

看一个实例，还是使用上面的EMP_TEMP表：

zx@MYDB>create view emp_mgr_view as select * from emp_temp where job='MANAGER';

View created.

zx@MYDB>select empno,sal from emp_mgr_view where ename='CLARK';

     EMPNO        SAL
---------- ----------
      7782       2450

Oracle里另外一些典型的执行计划

从上述显示内容中可以看出，现在SQL的执行计划走的是对表EMP_TEMP的全表扫描，并且全表扫描进的过滤查询条件是filter(("ENAME"='CLARK' AND "JOB"='MANAGER')).显然这里Oracle做了视图合并，直接查询的视图EMP_MGR_VIEW的基表EMP_TEMP，并且把针对视图的where条件推到了视图的内部，和原先创建视图时的限制条件做了合并。

现在修改视图EMP_MGR_VIEW的定义，其创建语句中加入ROWNUM关键字，这样新创建的同名视图EMP_MGR_VIEW将不能再做视图合并：

zx@MYDB>create or replace view emp_mgr_view as select * from emp_temp where job='MANAGER' and rownum<10;

View created.

zx@MYDB>select empno,sal from emp_mgr_view where ename='CLARK';

     EMPNO        SAL
---------- ----------
      7782       2450

Oracle里另外一些典型的执行计划

从上述显示内容中可以看出，现在该SQL的执行计划中包含了关键字“VIEW”，即表明这里Oracle并没有对视图EMP_MGR_VIEW做视图合并，视图EMP_MGR_VIEW被Oracle当作一个整体来独立执行。

4. FILTER

FILTER直译过来就是过滤、筛选的意思，它是一种特殊的执行计划，所对应的执行过程就是如下三步：

得到一个驱动结果集

根据一定的过滤条件从上述驱动结果集中滤除不满足条件的记录

结果集中剩下的记录就会返回给最终用户或者继续参与一下个执行步骤。

看一个实例，还是使用上面的视图EMP_MGR_VIEW：

zx@MYDB>select empno,ename from emp where empno in (select empno from emp_mgr_view);

     EMPNO ENAME
---------- ------------------------------
      7566 JONES
      7698 BLAKE
      7782 CLARK

Oracle里另外一些典型的执行计划

从上述的显示内容可以看出，现在该SQL的执行计划走的是嵌套循环连接，并没有出现我们希望的FILTER类型的执行计划。这是因为Oracle在这里做了子查询展开(Subquery Unnexting)，即把子查询和它外部的SQL做了合并，转化成视图VW_NOS_1和表EMP做连接。

这里使用Hint禁掉子查询展开后重新执行上述SQL:

zx@MYDB>select empno,ename from emp where empno in (select  empno from emp_mgr_view);

     EMPNO ENAME
---------- ------------------------------
      7566 JONES
      7698 BLAKE
      7782 CLARK

Oracle里另外一些典型的执行计划

从上述显示内容中可以看出，现在该SQL走的就是我们希望的FILTER类型执行计划。

FILTER类型的执行计划实际上是种改良的嵌套循环连接，它并不像嵌套循环连接那样，驱动结果集中的有多少记录就得访问多少次被驱动表。

用一个实验验证：

zx@MYDB>select * from t1;

      COL1 COL2
---------- ----
         1 A
         2 B
         3 B

zx@MYDB>select * from t2;

COL2 COL3
---- ------
A    A2
B    B2
D    D2

zx@MYDB>select  * from t1 where col2 in(select  col2 from t2);

      COL1 COL2
---------- ----
         1 A
         2 B
         3 B

Oracle里另外一些典型的执行计划

注意到上述显示内容中id=2的执行步骤所对应的列A-Rows的值为3，id=3的执行步骤所对应的列Starts的值为2，说明虽然全表扫描T1所得到的驱动结果集的数量为3，但走Filter类型的执行计划时访问被驱动表T2的实际次数却不是3，而是2.这是因为表T数量虽然是3，但其列COL2的distinct值的数量却只有2，所以在用过滤条件“where col2 in(select col2 from t2)”去过滤表T1中的数据时，只用访问两次表T2就可以了。

5. SORT

SORT就是排序的意思，执行计划中的SORT通常会以组合的方式出现，这些组合方式包括但不限于如下这几种：

SORT AGGREGATE
SORT UNIQUE
SORT JOIN
SORT GROUP BY
SORT ORDER BY
BUFFER SORT

执行计划中即使出现了关键字“SORT”，也不一定意味着就需要排序，比如SORT AGGREGATE和BUFFER SORT就不一定需要排序。

看一个实例，还是使用上面的EMP_TEMP表：

zx@MYDB>set autotrace traceonly
zx@MYDB>select sum(sal) from emp_temp where job='MANAGER';

Oracle里另外一些典型的执行计划

从上述显示内容可以看出，现在SQL的执行计划走的是SORT AGGREGATE，这里执行的SQL只是求了一个sum值，很显然这里不需要排序的。统计信息中的sort(memroy)和sort(disk)的值均为0，也说明Oracle在执行此SQL时并没有做任何排序操作，所以我们说SORT AGGREGATE并不一定需要排序，这其中的关键字“SORT”具有一定的迷惑性。

下面再做实例：

zx@MYDB>set autotrace off
zx@MYDB>select distinct ename from emp_temp where job='MANAGER' order by ename;

ENAME
------------------------------
BLAKE
CLARK
JONES

上述SQL的含义是既要排序又要去重，它对应的执行计划就会是SORT UNIQUE

Oracle里另外一些典型的执行计划

zx@MYDB>select t1.empno,t1.ename,t2.sal from scott.emp t1,emp_temp t2 where t1.empno=t2.empno;

Oracle里另外一些典型的执行计划

从上述显示内容中可以看出，现在该SQL的执行计划走的是对EMP和EMP_TEMP的排序合并连接。SORT JOIN类型的执行计划通常会出现在排序合并连接中，它是排序合并连接所对应的执行计划第一步要做的事情。

再执行如下SQL：

zx@MYDB>select ename from emp_temp where job='MANAGER' order by ename;

ENAME
------------------------------
BLAKE
CLARK
JONES

上述SQL的含义是只需要单纯的排序，它对应的执行计划就会是SORT ORDER BY:

Oracle里另外一些典型的执行计划

接着执行下面的SQL:

select ename from emp_temp where job='MANAGER' group by ename order by ename;

ENAME
------------------------------
BLAKE
CLARK
JONES

上述SQL的含义是既要排序又要分组，所以它对应的执行计划就会是SORT GROUP BY:

Oracle里另外一些典型的执行计划

最后执行如下SQL：

select t1.empno,t2.ename from scott.emp t1,emp_temp t2;

Oracle里另外一些典型的执行计划

从上述显示内容可以看出，现在该SQL的执行计划走的是对表EMP_TEMP和表EMP上主键PK_EMP的笛卡儿连接，因为上述SQL中没有指定连接条件。此处执行计划的步骤是首先全表扫描表EMP_TEMP，扫描结果记为结果集1；接着对表EMP上的主键PK_EMP做索引快速全扫描，并将扫描结果load进PGA中，然后对结果集1和结果集2做笛卡儿连接，最后笛卡儿连接的结果就是上述SQL的最终执行结果。执行计划中关键字“BUFFER SORT”就是表示Oracle会借用PGA并把扫描结果load进去，这样做的好处是省掉了相应的缓存在SGA中所带来的种种额外开销(如持有、释放相关Latch等)。PGA常常用来做排序，这可能就是“BUFFER SORT”中关键字SORT的由来。

需要注意的是，BUFFER SORT不一定会排序，也可能会排序，也可能不会。

看一个SQL是否排序，最直观的方法就是查看其统计信息中"sorts(memory)"和"sorts(disk)"的值，如果这两个指标的值大于0，则说明该SQL在执行时经历过排序。但遗憾的是，这两个指标对BUFFER SORT而言是不准的，此时我们就需要借助目标SQL真实执行计划中"Column Projection InfORMation"部分"keys"的值来判断到底所对应的BUFFER SORT有没有排序。"#keys"的值就表示该执行步骤实际排序列的数量，如果"#keys"值大于0时，则表示该执行步骤确实排过序了。

看如下SQL：

set autotrace traceonly
zx@MYDB>select t1.ename,t2.loc from scott.emp t1,scott.dept t2;

56 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2034389985

-----------------------------------------------------------------------------
| Id  | Operation	     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |	    |	 56 |	784 |	 10   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|	    |	 56 |	784 |	 10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | DEPT |	  4 |	 32 |	  3   (0)| 00:00:01 |
|   3 |   BUFFER SORT	     |	    |	 14 |	 84 |	  7   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | EMP  |	 14 |	 84 |	  2   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
	315  recursive calls
	  0  db block gets
	 70  consistent gets
	 11  physical reads
	  0  redo size
       1831  bytes sent via SQL*Net to client
	557  bytes received via SQL*Net from client
	  5  SQL*Net roundtrips to/from client
	  7  sorts (memory)
	  0  sorts (disk)
	 56  rows processed

注意到上述显示内容中“统计信息”部分的sorts(memory)的值为7，但由于该SQL中出现了ID=3的执行步骤“BUFFER SORT”，所以这并不一定能说明该SQL在执行时经历过排序。

我们来看一下执行坟墓中id=3的执行步骤“BUFFER SORT”所对应的“#keys”的值：

zx@MYDB>select sql_id,sql_text from v$sql where sql_text = 'select t1.ename,t2.loc from scott.emp t1,scott.dept t2';

SQL_ID		     SQL_TEXT
-------------------- ----------------------------------------------------------------------------------------------------
3Dmxcxk72fwr4	     select t1.ename,t2.loc from scott.emp t1,scott.dept t2

zx@MYDB>select * from table(dbms_xplan.display_cursor('3dmxcxk72fwr4',0,'advanced'));

Oracle里另外一些典型的执行计划

从上述显示内容中可以看出，Id=3的执行步骤“BUFFER SORT”所对应的“#keys”的值为0，说明该SQL在执行“BUFFER SORT”时确实没有排序，排序的数量为0。

这就验证了我们之前提到的观点：统计信息中sorts(memory)和sorts(disk)的值对于BUFFER SORT而言是不准的，Oracle在执行BUFFER SORT时可能不需要排序。

6. UNION/UNION ALL

UNION/UNION ALL表示对两个结果集进行合并，如果它们出现在执行计划中也表示相同的含义。

UNION和UNION ALL的区别是：UNION ALL仅仅是简单地将两个结果集合并，并不做任何额外的处理；而UNION除了将两个结果集简单合并之外，还会对合并后的结果集做排序和去重，即UNION相当于先做UNION ALL，然后再对UNION ALL之后的结果集做SORT UNIQUE

看一个实例：

select empno,ename from scott.emp union all select empno,ename from emp_temp;

     EMPNO ENAME
---------- ------------------------------
      7369 SMITH
......
      7934 MILLER

27 rows selected.

Oracle里另外一些典型的执行计划

从上述显示内容中可以看出，现在该SQL的执行计划走的是对表EMP和EMP_TEMP全表扫描后的结果集的UNION ALL,UNION ALL在执行计划中对应的关键字就是UNION-ALL。表EMP有13条记录，表EMP_TEMP有12条记录，UNION ALL合并后的结果集总是25。

把UNION ALL改为UNION：

zx@MYDB>select empno,ename from scott.emp union select empno,ename from emp_temp;

     EMPNO ENAME
---------- ------------------------------
      7369 SMITH
......
      7934 MILLER

14 rows selected.

Oracle里另外一些典型的执行计划

从上述显示内容可以看出，现在该SQL的执行计划走的是对EMP和EMP_TEMP全表扫描的结果集的UNION，UNION在执行计划中对应的关键字就是"UNION-ALL"和"SORT UNIQUE",即表示UNION相当于在UNION ALL的基础上做排序和去重。表EMP_TEMP的数据全部来源于表EMP，所以这里UNION操作返回结果集的复数就是表EMP的行数14。

7. CONCAT

CONCAT就是IN-List扩展(IN-List Expansion)或OR扩展(OR Expansion)，IN-List扩展/OR扩展在执行计划中对应的关键字是“CONCATENATION”，使用Hint来强制让Oracle走IN-List扩展/OR扩展。

看一下实例：

zx@MYDB>select empno,ename from scott.emp where empno in (7654,7698,7782);

     EMPNO ENAME
---------- ------------------------------
      7654 MARTIN
      7698 BLAKE
      7782 CLARK

Oracle里另外一些典型的执行计划

从上述显示内容可以看出，现在该SQL的执行计划走的是对表EMP和主键索引PK_EMP的IN-List迭代。

使用Hint让Oracle强制走IN-List扩展

zx@MYDB>select empno,ename from scott.emp where empno in (7654,7698,7782);

     EMPNO ENAME
---------- ------------------------------
      7654 MARTIN
      7698 BLAKE
      7782 CLARK

Oracle里另外一些典型的执行计划

从上面显示内容可以看出，Hint失效了，还是走IN-List迭代。使用如下两个事件在当前Session中将IN-List迭代禁掉，并将输入参数no_invalidate的值设为false后重新收集一下统计信息，以便后续再次执行上述SQL时不会没用之前走IN-List迭代的执行计划：

zx@MYDB>alter session set events '10142 trace name context forever';

Session altered.

zx@MYDB>alter session set events '10157 trace name context forever';

Session altered.

zx@MYDB>exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);

PL/SQL procedure successfully completed.

zx@MYDB>select  empno,ename from scott.emp where empno in (7654,7698,7782);

     EMPNO ENAME
---------- ------------------------------
      7654 MARTIN
      7698 BLAKE
      7782 CLARK

Oracle里另外一些典型的执行计划

从上述显示内容中可以看出，现在该SQL的执行计划变成了我们想要的IN-List扩展，在执行计划中对应的关键字就是CONCATENATION。这里CONCATENATION的含义就相当于UNION ALL，即上述SQL就相当于UNION ALL改写为如下的形式：

select empno,ename from emp where empno=7782

union all

select empno,ename from emp where empno=7698

union all

select empno,ename from emp where empno=7654

8. CONNECT BY

CONNECT BY是Oracle数据库中层次查询(Hierachical Queries)所对应的关键字，如果出现在执行中也是表示同样的含义。

看一下实例：

zx@MYDB>select empno,ename,job,mgr from scott.emp;

     EMPNO ENAME			  JOB				     MGR
---------- ------------------------------ --------------------------- ----------
      7369 SMITH			  CLERK 			    7902
      7499 ALLEN			  SALESMAN			    7698
      7521 WARD 			  SALESMAN			    7698
      7566 JONES			  MANAGER			    7839
      7654 MARTIN			  SALESMAN			    7698
      7698 BLAKE			  MANAGER			    7839
      7782 CLARK			  MANAGER			    7839
      7788 SCOTT			  ANALYST			    7566
      7839 KING 			  PRESIDENT
      7844 TURNER			  SALESMAN			    7698
      7876 ADAMS			  CLERK 			    7788
      7900 JAMES			  CLERK 			    7698
      7902 FORD 			  ANALYST			    7566
      7934 MILLER			  CLERK 			    7782

从上述内容可以看到KING是PRESIDENT，它所在记录的MGR的值为NULL，表示KING没有上级。

我们执行如下SQL，从KING所在的记录开始，将所有人按照上下级关系分成显示出来：


zx@MYDB>select empno,ename,mgr from emp start with empno=7839 connect by prior empno=mgr;

     EMPNO ENAME				 MGR
---------- ------------------------------ ----------
      7839 KING
      7566 JONES				7839
      7788 SCOTT				7566
      7876 ADAMS				7788
      7902 FORD 				7566
      7369 SMITH				7902
      7698 BLAKE				7839
      7499 ALLEN				7698
      7521 WARD 				7698
      7654 MARTIN				7698
      7844 TURNER				7698
      7900 JAMES				7698
      7782 CLARK				7839
      7934 MILLER				7782

查看执行计划：

Oracle里另外一些典型的执行计划