阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

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

208次阅读
没有评论

共计 12101 个字符,预计需要花费 31 分钟才能阅读完成。

在之前的文章里写了 Oracle 里常见的执行计划,可以参考文章:http://www.linuxidc.com/Linux/2017-02/141090.htm,这篇文章里介绍的是其他的一些典型的执行计划。

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

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 /*+ NO_UNNEST */ 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 /*+ gather_plan_statistics */ * from t1 where col2 in(select /*+ no_unnest */ 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 /*+ 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';

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 /*+ use_merge(t1 t2) */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 /*+ USE_CONCAT */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 /*+ USE_CONCAT */ 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 里另外一些典型的执行计划

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

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-02/141118.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计12101字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中