当前位置:  数据库>oracle

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

    来源: 互联网  发布时间:2017-06-27

    本文导语: 在之前的文章里写了Oracle里常见的执行计划,可以参考文章:,这篇文章里介绍的是其他的一些典型的执行计划。 1. AND-EQUAL(INDEX MERGE) AND-EQUAL又称为INDEX MERGE,顾名思义,INDEX MERGE就是指如果where条件里出现了多个针对不同单列的...

在之前的文章里写了Oracle里常见的执行计划,可以参考文章:,这篇文章里介绍的是其他的一些典型的执行计划。

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 /*+ and_equal(emp_temp idx_mgr idx_deptno) */ empno,job from emp_temp where mgr=7902 and deptno=20;
 
     EMPNO JOB
---------- ---------------------------
      7369 CLERK

从上述显示内容中可以看出,现在此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.

从上述显示内容可以看出,现在目标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

从上述显示内容中可以看出,现在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 rownumselect empno,sal from emp_mgr_view where ename='CLARK';
 
     EMPNO        SAL
---------- ----------
      7782       2450

从上述显示内容中可以看出,现在该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

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

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

zx@MYDB>select empno,ename from emp where empno in (select /*+ NO_UNNEST */ empno from emp_mgr_view);
 
     EMPNO ENAME
---------- ------------------------------
      7566 JONES
      7698 BLAKE
      7782 CLARK

从上述显示内容中可以看出,现在该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 /*+ gather_plan_statistics */ * from t1 where col2 in(select /*+ no_unnest */ col2 from t2);
 
      COL1 COL2
---------- ----
         1 A
         2 B
         3 B

注意到上述显示内容中id=2的执行步骤所对应的列A-Rows的值为3,id=3的执行步骤所对应的列Starts的值为2,说明虽然全表扫描T1所得到的驱动结果集的数量为3,但走Filter类型的执行计划时访问被驱动表T2的实际次数却不是3,而是2.这是因为表T数量虽然是3,但其列COL2的distinct值的数量却只有2,所以在用过滤条件“where col2 in(select /*+ no_unnest */ 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';

从上述显示内容可以看出,现在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

zx@MYDB>select /*+ use_merge(t1 t2) */t1.empno,t1.ename,t2.sal from scott.emp t1,emp_temp t2 where t1.empno=t2.empno;

从上述显示内容中可以看出,现在该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:

接着执行下面的SQL:

select ename from emp_temp where job='MANAGER' group by ename order by ename;
 
ENAME
------------------------------
BLAKE
CLARK
JONES

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

最后执行如下SQL:

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

从上述显示内容可以看出,现在该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'));

从上述显示内容中可以看出,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.

从上述显示内容中可以看出,现在该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.

从上述显示内容可以看出,现在该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

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

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

zx@MYDB>select /*+ USE_CONCAT */empno,ename from scott.emp where empno in (7654,7698,7782);
 
     EMPNO ENAME
---------- ------------------------------
      7654 MARTIN
      7698 BLAKE
      7782 CLARK

从上面显示内容可以看出,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 /*+ USE_CONCAT */ empno,ename from scott.emp where empno in (7654,7698,7782);
 
     EMPNO ENAME
---------- ------------------------------
      7654 MARTIN
      7698 BLAKE
      7782 CLARK

从上述显示内容中可以看出,现在该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

查看执行计划:

从上述显示内容可以看出,现在该SQL的执行计划走的就是CONNECT BY,在执行计划中我们也能看到CONNECT BY 关键字。


    
 
 

您可能感兴趣的文章:

  • 怎么写一个Shell来执行这样的功能,访问Oracle数据库,然后执行一个SQL脚本,生成一个文件。急!
  • win2000+jbuilder6+oracle817编出的程序,在win2000下执行很好,在win98下却访问不了oracle数据库
  • oracle 可以在crontab 中定时执行吗?
  • oracle sql执行过程(流程图)
  • linux能够通过执行脚本添加oracle数据库的用户吗
  • ORACLE安装时/tmp/orainstRoot.sh 执行发生错误
  • 求救:HPUNIX下的ORACLE7执行select * from tablename提示权限不足!!
  • 为什么 export ORACLE_SID=test写在程序里面就不会执行?
  • 请问在 Linux 下如何用代码实现连接oracle数据库 并 执行 SQL 语句?
  • shell调用oracle储存过程,怎么判断储存过程执行结果是否正确
  • shell 执行oracle sql脚本的问题
  • oracle单库彻底删除干净的执行步骤
  • 查看Oracle的执行计划一句话命令
  • 关于ORACLE中执行批处理的问题
  • oracle中得到一条SQL语句的执行时间的两种方式
  • oracle导出sql语句的结果集和保存执行的sql语句(深入分析)
  • Nagios check_oracle_health 关于执行SQL问题
  • 执行Commit时Oracle做哪些工作
  • RedHat AS 4 安装oracle9i的时候,执行Disk1下的runInstaller后提示正在初始化虚拟机,请等待后就再无反应
  • JBUILDER如何执行ORACLE的储存过程
  •  
    本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
    本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • 为Oracle增加计划任务
  • 利用windows任务计划实现oracle的定期备份
  • Oracle数据库设置任务计划备份一周的备份记录
  • Oracle公布SPARC五年计划和Solaris 11上市时间
  • Oracle密谋B计划 JDK 7或成跳票王
  • Oracle中获取执行计划的几种方法分析
  • 解析Oracle 8i/9i的计划稳定性
  • Oracle中使用DBMS_XPLAN处理执行计划详解
  • ORACLE数据库查看执行计划的方法
  • Oracle中基于hint的3种执行计划控制方法详细介绍
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动!也就是让oracle那个服务自动启动,不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同?
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍
  • SCO unix下安装oracle,但没有光盘,请大家推荐一个oracle下载站点(unix版本的)。谢谢!!!!
  • oracle中如何把表中具有相同值列的多行数据合并成一行
  • 请问大家用oracle数据库, 用import oracle.*;下的东西么? 还是用标准库?
  • Oracle 数据库(oracle Database)性能调优技术详解
  • Linux /$ORACLE_HOME $ORACLE_HOME
  • ORACLE日期相关操作
  • Linux系统下Oracle的启动与Oracle监听的启动
  • ORACLE数据库常用字段数据类型介绍
  • 请问在solaris下安装ORACLE,用root用户和用oracle用户安装有什么区别么?
  • Oracle 12c的九大最新技术特性介绍


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3