Oracle12C TABLE ACCESS BY INDEX ROWID BATCHED引发的疑问

今天,公司同事问了我一个问题,Oracle 12c在执行计划中出现了TABLE ACCESS BY INDEX ROWID BATCHED“批量回表”特性,是否等同于我们消除了Nested Loop时被驱动表扫描多少次就需要回表多少次的现状?

答案是否定的,我们先看一下如下测试实验:

我们在12c环境下执行如下SQL:

select /*+ gather_plan_statistics use_hash(a,t2) */
a.owner,a.object_name,a.object_id from t a,t2 where a.owner=t2.username
and a.owner='SYSTEM'

因12c中TABLE ACCESS BY INDEX ROWID BATCHED特性受隐含参数控制(12C 默认为TRUE),我们分别将该参数改为FLASE,与TRUE:

(1)关闭TABLE ACCESS BY INDEX ROWID BATCHED特性,同ora11g版本:

2019203-2

继续阅读

分页语句SQL优化思路总结-3(多表JOIN)

前两篇文章中讲述总结了几种分页语句的优化方法后,碰巧生产系统中有如下SQL需要优化,我们看到这个SQL是多表JOIN的需求,也是我们在生产系统中常见的分页语句场景:

经过将分页框架等价改写正确后,生产系统SQL如下:

SELECT  * FROM ( SELECT * FROM (
 SELECT rownum AS rn, r.owner, r.object_id
 FROM ( SELECT t.owner, t.object_id, t1.account_status
 FROM t, t1 WHERE t.owner = t1.username AND t.owner = 'SYS'
 ORDER BY t.owner) r)
 WHERE rownum <= 10)
WHERE rn >= 1;

多表JOJN-1 20191117

继续阅读

分页语句SQL优化思路总结-2(分页语句中存在谓词条件)

继《分页语句SQL优化思路总结-1(分页语句中无谓词条件)》中我们实验了目标分页SQL存在排序的优化,该分页SQL是不存在谓词过滤条件的“select owner,object_id from t order by object_id”,但是我们99%的情况遇到的应用SQL都是存在谓词过滤条件,例如”select owner,object_id from t where owner=’TRAVELSKYDBA’ order by object_id;”的情况。我们上文提到,可以利用索引有序特性将object_id(排序列)创建索引,但是如果遇到等值查询,我们是否仍需要将排序列创建索引呢?我们继续试验论证,我们来看一下测试数据表T的OWNER列的数据分布:

20191117-分页(4)

我们执行如下目标SQL:

SELECT /*+ gather_plan_statistics */ *
  FROM (
    SELECT *
        FROM (
   SELECT rownum AS rn, r.owner, r.object_id
      FROM (
   SELECT /*+index(t t_idx01) */ owner, object_id
   FROM t WHERE OWNER='TRAVELSKYDBA'
    ORDER BY object_id) r
  )
   WHERE rownum <= 10)
WHERE rn >= 1;

20191117-分页(5)

继续阅读

分页语句SQL优化思路总结-1(分页语句中无谓词条件)

今日,继续总结SQL优化相关原理总结-分页语句。现有如下SQL:

20191117-分页(1)

此SQL的目的是在T表中取出已排序后的10条记录,该SQL中走的是T表的全表扫描执行计划,并且进行排序后才取出10条记录,显然此执行计划并非是最优的,如果表的数据量很大,会造成严重的性能问题(全表扫描+排序),也就是说将表中数据全部扫描处理后再进行排序。但是,该SQL的目的是取出已排序好的10条记录,那么我们是否可以利用INDEX索引有序的特征,扫描到第10条后进行count stopkey停止扫描呢?

因为目标SQL是以OBJECT_ID列作为排序列,我们可以在此列上建立索引,根据索引原理,索引数据本身是有序的,我们可以利用这一特性减少目标SQL对表的访问block

我们将目标SQL先改写为如下形式:

create index t_idx01 on t(object_id,0)

select /*+ gather_plan_statistics */ * from (select /*+ index(t
t_idx01) */ owner,object_id from t order by object_id) where rownum <=10

我们来看一下执行计划:

20191117-分页(2)

我们用HINT强制SQL走了T_IDX01的索引的执行计划,并CBO选择了INDEX_FULL_SCAN索引全扫描,但是我们发现,A-ROWS只扫描了10行数据,处理的总行数等同于我们需要返回的行数,并且执行计划中我们没有看到SORT ORDER BY,在此案例中并没有进行排序,而是利用索引有序的特性,直接扫描INDEX返回即可。

我们将目标SQL改写成如下方式即可:

SELECT /*+ gather_plan_statistics */ *
  FROM (
   SELECT *
      FROM (
    SELECT rownum AS rn, r.owner, r.object_id
   FROM (
    SELECT /*+index(t t_idx01) --分页语句 */ owner, object_id
   FROM t
  ORDER BY object_id) r
 )
 WHERE rownum <= 10)
WHERE rn >= 1;

20191117-分页(3)

犹如预期所愿,目标SQL只扫描处理了10行便COUNT STOPKEY,这是理想的执行计划。

经过以上实验,我们可以得出几个个结论,

(1)分页语句中如果存在排序列(本例中为order by object_id),可以在此列上建立索引,可以避免排序SORT ORDER BY;

(2)例如本文开头的案例,扫描了所有的行数后最后却只要10行数据,那么在错误的分页框架中即使存在索引时,执行计划也会走INDEX FULL SCAN扫描所有的INDEX LEAF BLOCK,所以我们要利用ROWNUM来实现COUNT STOPKEY从而控制扫描的行数,避免扫描多余的行数,尽量只需要处理我们需要返回的行数即可。

 

 

一则系统迁移过后的SQL缓慢案例

11月初,某重要航司的一套生产库进行了机房迁移工作,原数据库实例A(Oracle 11.2.0.3版本)利用DG迁移至新机房,数据库实例A(Oracle 11.2.0.3),数据库版本维持原样,没有进行软件升级,但是,一个应用SQL在新环境下运行时非常缓慢,数据库服务器CPU飙高,Idle接近于0,SQL信息如下:

SELECT /*+ parallel(16) */
 nodeValue
 from (SELECT DISTINCT LPAD(BEGTIME, 4, '0') || '-' ||
 LPAD(ENDTIME, 4, '0') nodeValue
 FROM TRAVELSKYDBA
 WHERE CATEGORY = 'OND'
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA1
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA2
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA3
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA4
 UNION
 SELECT DISTINCT DEPARTURE_TIME
 from TRAVELSKYDBA5)
 WHERE UPPER(nodeValue) <> 'ALL'

既然数据库并没有进行版本升级,CBO优化器方面应该没有不同之处,新老环境的执行计划如下:

老环境:

od-1

继续阅读

DELETE与SELECT计算回表COST的区别

前几日,一位同事发来微信,发送过来一个问题共同探讨,SELECT时采用了全表扫描,忽略了索引,为何在DELETE时选择了此索引?具体SQL如下图所示:

1028-2

其中,A_SCHEMA表记录数量为:176941,B表记录数量为:5224320,通过SELECT的查询计划来看,A,B表进行HASH_JOIN过滤时,A表作为驱动表,未采用索引,采用了全表扫描的执行计划,我们看一下把SELECT改成DELETE,如下图SQL:

1028-3

从上图中的执行计划中看到,DELETE时,A表走了A_SCHEMA_ID索引,问题1,为什么SELECT时不走索引呢?

1028-4

可以看到,该索引的CLUSTERING_FACTOR非常大,基本接近于表的记录数,而表的BLOCK数仅为755,说明在SELECT用到此索引进行回表时,IO COST影响了此路径最终的成本:

SELECT采用此索引时的成本,表连接方式为Nested_loop:

1028-7

继续阅读

如何找出两个结果集中不同的数据?

周末,一个朋友微信我咨询了一个问题,简单描述来说:利用CTAS方式同步一张表时,where rownum 条件写错,现在源表(A)与目标表数据(B)不一致,并且无法直观的看到A,B表的差异,并且A表没有停止数据写入,通过应用逻辑规则原本可以明确得知需要补充的数据,但是因为ROWNUM条件写错,无法确认。此时需要对比两个表中不同的数据,采取补救数据措施。

举例说明:

现有emp(源表),emp2(问题目标表):

full_join_1可以利用SQL表连接的FULL JOIN方式,找寻emp与emp2表的不同记录:

SELECT r1.ename r1_ename,r1.job r1_job,r1.sal r1_sal,r1.num r1_num,
 r2.ename r2_ename,r2.job r2_job,r2.sal r2_sal,r2.num r2_num
FROM 
 (SELECT a.ename, a.job,a.sal,count(*) AS num FROM emp a
 GROUP BY ename,job,sal) r1 full JOIN 
 (SELECT b.ename,b.job,b.sal,count(*) AS num
 FROM emp2 b GROUP BY ename,job,sal) r2
 ON (r1.ename=r2.ename AND r1.job=r2.job AND r1.sal=r2.sal AND r1.num=r2.num);

继续阅读

SQL关联方式-视图合并 (View Merging)

周末时近日有网友朋友向我提问什么是视图合并?多次看到这个名词,但是每次都是扎入到执行计划中去分析了,但是对这个概念和功能完全不清楚,干啥用的?” 对于这个概念,趁赶着这个问题,自己也好好复习一下。

一. 什么是视图合并(View Merging)?

我们通过一个SQL案例来说明,现有如下SQL:

SQL1:
select emp1.empno, emp1.ename, emp1.comm
 from emp1,
 (select emp.ename, emp.deptno
 from emp, dept
 where emp.deptno = dept.deptno) a
 where emp1.ename = a.ename

执行计划如下:

view-merge-2

我们看到,SQ中存在如下的子查询结果集,区别于标量子查询,该子查询是在from 与Where之间的,从执行计划上来看,emp表和dep表进行关联过滤限定后在再EMP1进行最后关联,也就是说如下查询结果集作为一个整体看待,这个比较符合SQL写法的认知逻辑,但是强大的Oracle CBO默认会按照如此的逻辑进行关联么?

select emp.ename, emp.deptno
 from emp, dept
 where emp.deptno = dept.deptno

可以看到,我在SQL中添加了一个 Hint:no_merge,说明此执行计划是人为控制后的产物,我们来看一下去除掉Hint,CBO默认选择什么执行计划?

view-merge-1

我们看到,CBO默认进行的Table Join方案为,EMP与EMP1进行 HASH JOIN后,最后一步进行与DEPT的JOIN工作。这便是Oracle的视图合并。

视图合并是Oracle CBO针对SQL的自动优化措施进行了查询转换,Oracle会保证转换前后的SQL语义是等价的,不再将视图内部(子查询)单独处理与执行,进行视图(子查询)进行拆分,把视图定义或子查询中的表与外部查询的表进行合并(emp与emp1),说了这么多,目的是什么,目的只有1个:让CBO有更多执行路径可以选择,更多路径成本比较,选择出一种最优的执行路径。

视图合并有诸多前提条件,但是宏观上讲,视图或子查询中不能出现UNION,MINUS等集合运算,start with connect by,rownum,cube,rollup等关键字,因为视图合并后,会影响结果集,无法确认合并后语义是否一致。

二. 视图合并的种类 (观点结论引用于著名的 《基于Oracle的SQL优化》- 崔华 著 一书):

(1)简单视图合并:

简单视图合并,是指针对那些不含外连接,以及所带视图的视图定义SQL语句中不包含distinct,group by等聚合行数的目标SQL进行视图合并;进行简单视图合并的SQL,经过CBO的等价改写,一定是小于或等于未做简单视图合并的原SQL成本值,但是如果成本值相同的情况,CBO更倾向于选择进行视图合并的执行路径。

(2)外连接视图合并:

外连接视图合并,是指针对于使用了Outer Join,并且自带视图定义的SQL语句中不含DISTINCT,GROUP BY等聚合函数的目标SQL的视图合并,当目标SQL在和外部查询的表连接时(上例的emp1.ename = a.ename)变为视图与外表之间的连接,有2个前提条件

a.视图(子查询)作为外连接的驱动表;

or

b.视图如果作为被驱动表,但是视图定义SQL语句中只包含一个表;

并且

(3)复杂视图合并:

复杂视图合并,为所带视图定义的SQL语句中含有,外连接、简单视图合并中的禁忌条件:DISTINCT与GROUP BY 的情形。对于复杂视图合并的意义在于,可以将DISTINCT与GROUP BY在视图内部(子查询)中先过滤一部分数据,试图将嫌少中间结果集。但是合并结果并不能一定带来目标SQL的性能提升,CBO选择复杂视图合并的情况:会采取合并改写后的SQL的Cost < 未合并改写后的Cost前提下。

表连接方式(4)-标量子查询、半连接、反链接优化方案总结

一.标量子查询

数据库经常能看到如下类形式的SQL:

select t.OWNER,t.OBJECT_NAME,(select TABLESPACE_NAME from t1 where 
t.object_name=t1.segment_name) travelskydba from t;

我们看到,该SQL中的查询列是由一个单独的SQL查询组成,查询出T表与T1表关联Join后的T1表中的数据。可以看到,T表与T1表是存在关联关系的,t.object_name=t1.segment_name,此类SQL我们成为标量子查询。

sub-1我们从执行计划中看到,T1表扫描了5012次,T表扫描了1次,是否有点类似于嵌套循环(Nested Loop)的感觉?标量子查询类似于嵌套循环,本例子中,T表为主表,T1为子表,主表通过t.object_name=t1.segment_name传值给子表进行Filter过滤,不同于嵌套循的是,标量子查询的驱动表固定为主表,并不可更改,需要T表传值与T1表。

(1)标量子查询在SQL优化的过程中需要注意,子表(被驱动表)扫描次数的问题,也就是执行计划中的starts列,主表放回多少数据,就要传值与子表,如果数据量很大,子表扫描次数会非常庞大,造成SQL的性能问题。同嵌套循环一致,被驱动表连接列上需要创建选择性好的索引。

(2)在Oracle 12c中,标量子查询会等价改写为外连接,从而可以改变执行计划进行HASH_JOIN

SUB-2

 

为什么是外连接而不是内连接呢?我们往EMP表中插入一行数据:

20191102-1

如果存在主表EMP.DEPTNO的情况,改写为内连接会丢失ALBERT此行数据,所以标量子查询在不确定表业务特性时,一定要改写为外连接。

二.半连接:

当SQL出现in ,exists时,我们称为半连接,例如:

select * from emp where DEPTNO in (select DEPTNO from dept)
select * from emp where exists (select 1 from dept where emp.DEPTNO =dept.DEPTNO);

in-1

exist-1

我们看到,EMP为驱动表,DEPT为被驱动表,我们可以改变内连接的执行计划:

qbname-11

此执行计划为EMP为驱动表,DEPT为被启动表进行嵌套循环。

qb_name-2

我们通过添加HINT控制执行计划后,将DEPT表作为驱动该表,此方法可以帮助我们在办连接中,控制其执行计划。

半连接通常可以等价改写成内连接的方式,例如:

select *  from dept where deptno in (select deptno from emp)可以等价改写成

select dept.* from dept,(select deptno from emp group by deptno)  where dept.deptno=emp.depto(因为返回的是DEPT表的数据,所以要将EMP进行去重操作,避免重复数据,改变结果集)此改写方式因多了去重的操作,所以执行效率并不如返连接高。

三.反连接:

AB表进行关联,只返回A表其中没有成功关联上B表的数据,我们称为反连接,not in ,not exists关键字,我们看如下例子:

1011-1

我们观察结果是NULL值,1400和0并没有如愿得出,是因为not in对空值敏感,如果该列包含空值,结果集会全部显示为空,因为NULL不等于任何值。剔除空值,则便显示正确结果:

1011-2

exists对空值不敏感,空值皆可返回。

1011-3

反连接执行计划可以改写成外连接+子表连接列 is null的情况,也同样可以利用qb_name hint改变执行计划,但是改写成外连接后,启动表被固定为主表,无法更换驱动表(主表存在NULL无法传值的情况)。

1011-4

表连接方式(3)Sort Merge Join优化方案总结

一. 什么是Sort Merge Join(排序连接合并)?

表连接方式中,排序连接合并个人理解为多数处理非等值连接的表连接情况,例如<,> >=,<=,执行效率上看与HASH JOIN比有所降低(因有排序过程),但是如果两个表的连接列上均有索引,则有些场景下则可以避免排序过程(索引有序)。工作过程原理如下,A,B两表进行排序连接合并:

(1)根据谓词条件访问A表,对A表的结果集按照连接列进行排序,形成R1

(2)根据谓词条件访问B表,对B表的结果集按照连接列进行排序,形成R2

(3)Oracle官方认为Sort Merge Join是不存在驱动表与被启动表的概念的,但是最终的R1,R2进行合并工作时翻阅了诸多资料后,个人感觉是存在的,R1中取出第一条记录去R2中进行匹配,第二条记录..第三条记录..直到匹配完成R1所有记录。也就是驱动表对应结果集R1有多少条记录,R2则需要被匹配多少次,但是被驱动表只需要被扫描1次(PGA存入排序后的结果集就好,无需再次访问被驱动表)。该过程是在PGA中需进行匹配的,之前上文中提高,排序连接合并多数情况下执行效率是差与HASH JOIN的,因为HASH JOIN只需要把驱动表放入PGA中,但是排序连接合并需要将两表结果集均放入PGA中。

SMJ-2

smj-1

实验环境中,模拟了一个SMJ的表连接SQL,可以看到,T1为“驱动表”,T为被“驱动表”,之所以加上引号,是因为Oracle官方认为SMJ连接方式是不存在驱动表与被驱动表的概念的,可以看到,T1首先进行排序操作,T表进行排序操作,ID=4的执行计划中,T1结果集去T结果集中进行数据匹配过滤。

优化方案总结:

(1)两表关联如果是等值连接,则尽量不需要走Sort Merge Join,数据关联结果集大走HASH JOIN,数据返回量少走Nested Loop

(2)如过两表关联时非等值连接,可以在连接列上建立索引,避免昂贵Cost的排序的工作。如果结果集很大,则可以尝试先过滤将结果集变小,再进行Sort Merge Join。