来源url
一:什么是 Oracle 执行计划?
执行计划是一条查询语句在 Oracle 中的执行过程或访问路径的描述
二:怎样查看 Oracle 执行计划?
这里以 PLSQL 为例:
①:配置执行计划需要显示的项:
工具 —> 首选项 —> 窗口类型 —> 计划窗口 —> 根据需要配置要显示在执行计划中的列
执行计划的常用列字段解释:
基数(Rows):Oracle 估计的当前操作的返回结果集行数
字节(Bytes):执行该步骤后返回的字节数
耗费(COST)、CPU 耗费:Oracle 估计的该步骤的执行成本,用于说明 SQL 执行的代价,理论上越小越好(该值可能与实际有出入)
时间(Time):Oracle 估计的当前操作所需的时间
②:打开执行计划:
在 SQL 窗口执行完一条 select 语句后按 F5 即可查看刚刚执行的这条查询语句的执行计划
select sys_guid() LSH, a.EMPI as EMPI, a.SJLY,XM, XBDM,coalesce(a.XBMC,ZD_XB.ZMC) XBMC, CSRQ,
(case ZJLXDM when '01' then left(a.ZJHM,18) else b.SFZHM end ) SFZHM, (case ZJLXDM when '02' then a.ZJHM else b.HKBHM end ) HKBHM,
(case ZJLXDM when '03' then a.ZJHM else b.HZHM end ) HZHM, (case ZJLXDM when '04' then a.ZJHM else b.JGZHM end ) JGZHM,
(case ZJLXDM when '05' then left(a.ZJHM,18) else b.JSZHM end ) JSZHM, (case ZJLXDM when '06' then a.ZJHM else b.GATXZHM end ) GATXZHM,
(case ZJLXDM when '07' then a.ZJHM else b.TWTXZHM end ) TWTXZHM, (case ZJLXDM when '08' then a.ZJHM else b.SBZHM end ) SBZHM,
(case ZJLXDM when '99' then a.ZJHM else b.QTZJHM end ) QTZJHM,
(case KLXDM when '0' then a.KH else b.SBKH end ) SBKH, (case KLXDM when '1' then a.KH else b.YBKH end ) YBKH,
(case KLXDM when '2' then a.KH else b.YLKH end ) YLKH, (case KLXDM when '3' then a.KH else b.TYZFJZKH end ) TYZFJZKH,
(case KLXDM when '4' then a.KH else b.YYZFKH end ) YYZFKH, (case KLXDM when '5' then a.KH else b.XNHKH end ) XNHKH,
(case KLXDM when '6' then a.KH else b.JKKH end ) JKKH, (case KLXDM when '7' then a.KH else b.SMKH end ) SMKH, c.XTNBH as XTNBH,
(case KLXDM when '9' then a.KH else b.QTKH end ) QTKH,
JMJKDAH, JDJGDM, JDJGMC, JDYSGH, JDYSXM, JDRQ, DAGLJGDM, DAGLJGMC, JKKFKJGDM, JKKFKJGMC, ZRYSGH, ZRYSXM, GZDWMC,
GZDWLXDH, DZYJDZ, LXRGXDM,coalesce(a.LXRGXMC,ZD_LXRGX.ZMC) LXRGXMC, LXRXM, LXRDHHM,CZRKBZ, GJDM,
coalesce(a.GJMC,ZD_GJDM.ZMC) GJMC, MZDM, coalesce(a.MZMC,ZD_GJDM.ZMC) MZMC, HYZKDM, coalesce(a.HYZKMC,ZD_HYZK.ZMC) HYZKMC,
BRDHHM as DHHM,ABOXXDM, coalesce(a.ABOXXMC,ZD_ABOXX.ZMC) ABOXXMC, RHXXDM, coalesce(a.RHXXMC,ZD_RHXX.ZMC) RHXXMC, WHCDDM,
coalesce(a.WHCDMC,ZD_WHCD.ZMC) WHCDMC, a.ZYDM, coalesce(a.ZYMC,ZD_ZYDM.ZMC) ZYMC, YLFYZFFSDM,coalesce(a.YLFYZFFSMC,ZD_YLZF.ZMC) YLFYZFFSMC,
YLFYZFFSQT, YWGMSBZ, YWGMDM, YWGMMC, YWGMQT, BLSDM, BLSMC, BLSQT, SSSBZ, WSSBZ, SXSBZ, YCBSBZ, YCJBMC, CJBZ, CJDM, CJMC,
CJQT, CFPFSSBZ, CFPFSSLBDM, CFPFSSLBMC, RLLXDM, RLLXMC, YSLBDM, YSLBMC, CSLBDM, CSLBMC, QCLLBDM, QCLLBMC,
CSDXXDZ, CSDXZQHDM, CSDDZBM, CSDSSBM, CSDSSMC, CSDDSBM, CSDDSMC, CSDQXBM, CSDQXMC, CSDJDBM, CSDJDMC, CSDCBM,CSDCMC, CSDMPHM, CSDYZBM, CZDZHJBZ,
JZDXXDZ, JZDXZQHDM, JZDDZBM, JZDSSBM, JZDSSMC, JZDDSBM, JZDDSMC, JZDQXBM, JZDQXMC, JZDJDBM, JZDJDMC, JZDCBM,JZDCMC, JZDMPHM, JZDYZBM,
HJDXXDZ, HJDXZQHDM, HJDDZBM, HJDSSBM, HJDSSMC, HJDDSBM, HJDDSMC, HJDQXBM, HJDQXMC, HJDJDBM, HJDJDMC, HJDCBM,HJDCMC, HJDMPHM, HJDYZBM,
GXJGBM, DAHGBZ, DAWSBZ, SWBZ, SWRQ, SWYY,
XT_DJSJ as DJSJ, XT_DJRYGH as DJRYBM, XT_DJRYXM as DJRYXM, XT_DJJGDM as DJJGDM, XT_DJJGMC as DJJGMC,
XT_XGSJ as XGSJ, XT_XGRYGH as XGRYBM, XT_XGRYXM as XGRYXM, XT_XGJGDM as XGJGDM, XT_XGJGMC as XGJGMC,
HZLXDM, (case a.XGBZ when '3' then '5' else '4' end) as JLZT --用4、5表示新插入的数据,后面更新为0、1
from TMP_sp_Empi_tempEmpi c, GR_JBXX a
left join JBXX_TMP_EMPI b on a.EMPI=b.EMPI and b.JLZT=0
left join SYS_SJYZYML ZD_XB on GLND=to_char(sysdate,'yyyy') and ZD_XB.ZYDM='GB/T 2261.1-2003' and ZD_XB.ZDM=a.XBDM --性别
left join SYS_SJYZYML ZD_LXRGX on ZD_LXRGX.GLND=to_char(sysdate,'yyyy') and ZD_LXRGX.ZYDM='GB/T 4761-2008' and ZD_LXRGX.ZDM=a.LXRGXDM --联系人关系
left join SYS_SJYZYML ZD_GJDM on ZD_GJDM.GLND=to_char(sysdate,'yyyy') and ZD_GJDM.ZYDM='GB/T 2659-2000' and ZD_GJDM.ZDM=a.GJDM --国籍
left join SYS_SJYZYML ZD_MZDM on ZD_MZDM.GLND=to_char(sysdate,'yyyy') and ZD_MZDM.ZYDM='GB/T 3304-1991' and ZD_MZDM.ZDM=a.MZDM --民族
left join SYS_SJYZYML ZD_HYZK on ZD_HYZK.GLND=to_char(sysdate,'yyyy') and ZD_HYZK.ZYDM='GB/T 2261.2-2003' and ZD_HYZK.ZDM=a.HYZKDM --婚姻状况
left join SYS_SJYZYML ZD_ABOXX on ZD_ABOXX.GLND=to_char(sysdate,'yyyy') and ZD_ABOXX.ZYDM='CV04.50.005' and ZD_ABOXX.ZDM=a.ABOXXDM --ABO血型
left join SYS_SJYZYML ZD_RHXX on ZD_RHXX.GLND=to_char(sysdate,'yyyy') and ZD_RHXX.ZYDM='CV04.50.020' and ZD_RHXX.ZDM=a.RHXXDM --RH血型
left join SYS_SJYZYML ZD_WHCD on ZD_WHCD.GLND=to_char(sysdate,'yyyy') and ZD_WHCD.ZYDM='GB/T 4658-2006' and ZD_WHCD.ZDM=a.WHCDDM --文化程度
left join SYS_SJYZYML ZD_ZYDM on ZD_ZYDM.GLND=to_char(sysdate,'yyyy') and ZD_ZYDM.ZYDM='GB/T 6565-2009' and ZD_ZYDM.ZDM=a.ZYDM --职业
left join SYS_SJYZYML ZD_YLZF on ZD_YLZF.GLND=to_char(sysdate,'yyyy') and ZD_YLZF.ZYDM='CV07.10.003' and ZD_YLZF.ZDM=a.YLFYZFFSDM --医疗费用支付方式
where a.SCZT='3' --and not exists(select 1 from JBXX_INDEX_LOG where EMPI=a.EMPI)
and a.YLJGDM=c.YLJGDM and a.GRJBXXBSH=c.GRJBXXBSH and a.XGBZ=c.XGBZ and a.EMPI is not null
①:执行顺序:
根据 Operation 缩进来判断,缩进最多的最先执行;(缩进相同时,最上面的最先执行)
同一级如果某个动作没有子 ID 就最先执行
同一级的动作执行时遵循最上最右先执行的原则
上图中 table access full 和 index unique scan 缩进相同最多,从上往下,table access full 先执行 全表扫描 index unique scan 唯一索引查找 图中也就是主键查找之后执行
( 注:PLSQL 提供了查看执行顺序的功能按钮 (上图中的红框部分) )
②:对图中动作的一些说明:
1. 上图中 TABLE ACCESS BY … 即描述的是该动作执行时表访问(或者说 Oracle 访问数据)的方式;
表访问的几种方式:(非全部)
- TABLE ACCESS FULL(全表扫描)
- TABLE ACCESS BY ROWID(通过 ROWID 的表存取)
- TABLE ACCESS BY INDEX SCAN(索引扫描)
(1) TABLE ACCESS FULL(全表扫描):
Oracle 会读取表中所有的行,并检查每一行是否满足 SQL 语句中的 Where 限制条件;
全表扫描时可以使用多块读(即一次 I/O 读取多块数据块)操作,提升吞吐量;
使用建议:数据量太大的表不建议使用全表扫描,除非本身需要取出的数据较多,占到表数据总量的 5% ~ 10% 或以上
(2) TABLE ACCESS BY ROWID(通过 ROWID 的表存取) :
先说一下什么是 ROWID?
ROWID 是由 Oracle 自动加在表中每行最后的一列伪列,既然是伪列,就说明表中并不会物理存储 ROWID 的值;
你可以像使用其它列一样使用它,只是不能对该列的值进行增、删、改操作;
一旦一行数据插入后,则其对应的 ROWID 在该行的生命周期内是唯一的,即使发生行迁移,该行的 ROWID 值也不变。
让我们再回到 TABLE ACCESS BY ROWID 来:
行的 ROWID 指出了该行所在的数据文件、数据块以及行在该块中的位置,所以通过 ROWID 可以快速定位到目标数据上,这也是 Oracle 中存取单行数据最快的方法;
(3) TABLE ACCESS BY INDEX SCAN(索引扫描):
在索引块中,既存储每个索引的键值,也存储具有该键值的行的 ROWID。
一个数字列上建索引后该索引可能的概念结构如下图:
所以索引扫描其实分为两步:
Ⅰ:扫描索引得到对应的 ROWID
Ⅱ:通过 ROWID 定位到具体的行读取数据
---------------- 索引扫描延伸 -------------------
索引扫描又分五种:
- INDEX UNIQUE SCAN(索引唯一扫描)
- INDEX RANGE SCAN(索引范围扫描)
- INDEX FULL SCAN(索引全扫描)
- INDEX FAST FULL SCAN(索引快速扫描)
- INDEX SKIP SCAN(索引跳跃扫描)
a) INDEX UNIQUE SCAN(索引唯一扫描):
针对唯一性索引(UNIQUE INDEX)的扫描,每次至多只返回一条记录;
表中某字段存在 UNIQUE、PRIMARY KEY 约束时,Oracle 常实现唯一性扫描;
发生索引范围扫描的三种情况:
- 在唯一索引列上使用了范围操作符(如:> < <> >= <= between)
- 在组合索引上,只使用部分列进行查询(查询时必须包含前导列,否则会走全表扫描)
- 对非唯一索引列上进行的任何查询
----------------------- 延伸阅读:Oracle 优化器简述 -----------------------
Oracle 中的优化器是 SQL 分析和执行的优化工具,它负责生成、制定 SQL 的执行计划。
Oracle 的优化器有两种:
- RBO(Rule-Based Optimization) 基于规则的优化器
- CBO(Cost-Based Optimization) 基于代价的优化器
RBO:
RBO 有严格的使用规则,只要按照这套规则去写 SQL 语句,无论数据表中的内容怎样,也不会影响到你的执行计划;
换句话说,RBO 对数据 “不敏感”,它要求 SQL 编写人员必须要了解各项细则;
RBO 一直沿用至 ORACLE 9i,从 ORACLE 10g 开始,RBO 已经彻底被抛弃。
CBO:
CBO 是一种比 RBO 更加合理、可靠的优化器,在 ORACLE 10g 中完全取代 RBO;
CBO 通过计算各种可能的执行计划的 “代价”,即 COST,从中选用 COST 最低的执行方案作为实际运行方案;
它依赖数据库对象的统计信息,统计信息的准确与否会影响 CBO 做出最优的选择,也就是对数据 “敏感”。
---------------------------------------------------------------------
d) INDEX FAST FULL SCAN(索引快速扫描):
扫描索引中的所有的数据块,与 INDEX FULL SCAN 类似,但是一个显著的区别是它不对查询出的数据进行排序(即数据不是以排序顺序被返回)
e) INDEX SKIP SCAN(索引跳跃扫描):
Oracle 9i 后提供,有时候复合索引的前导列(索引包含的第一列)没有在查询语句中出现,oralce 也会使用该复合索引,这时候就使用的 INDEX SKIP SCAN;
什么时候会触发 INDEX SKIP SCAN 呢?
前提条件:表有一个复合索引,且在查询时有除了前导列(索引中第一列)外的其他列作为条件,并且优化器模式为 CBO 时
当 Oracle 发现前导列的唯一值个数很少时,会将每个唯一值都作为常规扫描的入口,在此基础上做一次查找,最后合并这些查询;
例如:
假设表 emp 有 ename(雇员名称)、job(职位名)、sex(性别)三个字段,并且建立了如 create index idx_emp on emp (sex, ename, job) 的复合索引;
因为性别只有 ' 男 ' 和 ' 女 ' 两个值,所以为了提高索引的利用率,Oracle 可将这个复合索引拆成 (' 男 ', ename, job),(' 女 ', ename, job) 这两个复合索引;
当查询 select * from emp where job = 'Programmer' 时,该查询发出后:
Oracle 先进入 sex 为 ' 男' 的入口,这时候使用到了 (' 男 ', ename, job) 这条复合索引,查找 job = 'Programmer' 的条目;
再进入 sex 为 ' 女' 的入口,这时候使用到了 (' 女 ', ename, job) 这条复合索引,查找 job = 'Programmer' 的条目;
最后合并查询到的来自两个入口的结果集。
----------------------------------------------
2. 上图中的 NESTED LOOPS … 描述的是表连接方式;
JOIN 关键字用于将两张表作连接,一次只能连接两张表,JOIN 操作的各步骤一般是串行的(在读取做连接的两张表的数据时可以并行读取);
表(row source)之间的连接顺序对于查询效率有很大的影响,对首先存取的表(驱动表)先应用某些限制条件(Where 过滤条件)以得到一个较小的 row source,可以使得连接效率提高。
------------------------- 延伸阅读:驱动表(Driving Table)与匹配表(Probed Table)-------------------------
驱动表(Driving Table):
表连接时首先存取的表,又称外层表(Outer Table),这个概念用于 NESTED LOOPS(嵌套循环) 与 HASH JOIN(哈希连接)中;
如果驱动表返回较多的行数据,则对所有的后续操作有负面影响,故一般选择小表(应用 Where 限制条件后返回较少行数的表)作为驱动表。
匹配表(Probed Table):
又称为内层表(Inner Table),从驱动表获取一行具体数据后,会到该表中寻找符合连接条件的行。故该表一般为大表(应用 Where 限制条件后返回较多行数的表)。
---------------------------------------------------------------------------------------------------------
表连接的几种方式:
- SORT MERGE JOIN(排序 - 合并连接)
- NESTED LOOPS(嵌套循环)
- HASH JOIN(哈希连接)
- 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