一则系统迁移过后的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。

 

 

表连接方式(2)-Hash Join优化方案总结

一. 什么是HASH JOIN?

我们在《表连接方式-(1)Nested_loop优化方案总结》中提到,嵌套循环应为SQL返回少量数据的需求的最佳连接方式(仅限于等值查询)但是如果表连接关联返回了大量的数据需求时,SQL的执行计划应该走HASH JOIN,并且HASH连接检索内存中的数据要比访问索引的速度快的多,HASH JOIN的实现原理简单来如下(算法非常复杂,节选了相关重要步骤):

1.当两表进行关联时,将对象结果集相对较小(或过滤后结果集相对较小)的表设定为驱动表,将驱动表的查询列(结果集)与Join列读入PGA中

2.将连接列(驱动表结果集)进行HASH 运算,构建HASH TABLE,存储连接列与JOIN列,存入HASH BUCKET,同时构建标记位图,标记每个HASH BUCKET是否存在记录

3.访问被驱动表,读取被驱动表每一行记录,并按照驱动表的连接列做HASH运算,匹配驱动表中已计算好的HASH BUCKET,匹配连接列是否相等,如果相等则将查询列与匹配记录一并返回,如果不能匹配,则将重复构建驱动表标记位图。

二.执行计划相关:

select /*+ gather_plan_statistics */ t.owner,t.object_id from t,t1
where t.owner=t1.username

HASH-1

1.根据HASH JOIN原理,驱动表与被驱动表只需要访问1次即可,所以不像Nested Loop,HASH JOIN的连接列驱动表与被驱动表不需要创建索引。

2.Used-Mem表示消耗多少PGA内存,OMem表示当前操作完成所有内存工作区(Work Aera)操作所总共使用私有内存(PGA)中工作区的大小,1Mem表示当工作区大小无法满足操作所需的大小时,需要将部分数据写入到临时磁盘空间中,写入一次完成操作,为One-Pass;多次写入则为:Multi_Pass,该值为CBO估算,并不能代表真实的使用情况,仅供参考。

三,优化方案总结:

1.HASH连接的驱动表锁对应的连接列选择性要高,如果选择列存在严重倾斜,则会出现驱动表中的HASH BUCKET存在过多的记录,导致很长时间耗费在遍历HASH BUCKET的记录上,现象为SQL长时间不结束,CPU居高不下,逻辑读反而没有正向升高。小表与大表进行HASH JOIN时,小表连接列选择性不错,HASH JOIN的效率可以约等于大表全表扫描的时间(遍历大表,小表HASH BUCKET记录数较少,几乎可以忽略不计)

HASH-2

2.如果PGA采用自动管理,单个进程的PGA work area Limit 为1GB,手动管理则Limit为2GB,如果驱动表超过PGA限制,可以采取并行parallel(x)进行拆分,单个进程能够容纳拆分的数据,避免出现One Pass,Multi_Pass情况.

表连接方式-(1)Nested_loop优化方案总结

一. 什么是Nested_loop:

Nested_loop(嵌套循环)是Oracle中一种表连接方式,该算法为,两个表进行关联时(Oracle中,无论SQL中出现多少表,连接方式总是两两进行关联),驱动表每返回一行数据,通过连接列将值传给被驱动表,也就是以为着,驱动表返回多少行,被驱动表需要将传值带入,就需要被扫描多少次。

现有如下SQL:

select /*+ use_nl(a,b) leading (a) */ b.EMPNO,b.ENAME,b.job,b.SAL,b.DEPTNO,a.DNAME,a.LOC from dept a,emp b where a.DEPTNO=b.DEPTNO;

Nest-1

看到执行计划中,DEPT为驱动表,EMP为被驱动表,DEPT全表扫描后,将值传值给表B(EMP),

NEST-2

可以看到,驱动表DEPT执行了1次(STARTS=1),被驱动表EMP执行了4次,也就是传值4次,因为DEPT有4行记录:

NEST-3

继续阅读

什么时候有必要创建索引?

近期,论坛有个网友提问,在做SQL优化时经常感觉到困惑,他有个深信不疑的理论” 80%的引起性能问题的SQL都是由缺乏索引导致“,创建索引就好了,但是如何创建索引,创建什么索引?却没有一个固定的方法论。且先不80%的数字是否科学,创建索引就可以解决SQL的性能问题可以说是过于绝对的结论,那么,什么时候有必要创建索引,什么时候可以创建索引?虽然这个问题相对基础,但是再高深的优化方式也必须也终将回归于基础本质,所以我准备写一篇文章回复给他。

SQL_index_1    可以看到,Travelskydba表上的OWNER列分布并不均衡,OWNER=‘SYS’ 与OWNER=’ALBERT‘的行数相差甚远,那么请问,如果谓词条件中选择了SYS或ALBERT,你觉得CBO应该选择什么样的执行计划?

如果这么问起来,有经验的DBA一定会反问,表的行数是多少啊?是的没错,基数这个指标脱离表行数单独来看毫无意义,那么便引出了一个新的概念:”选择率”,选择率便是基数与总行数的比*100%就是某个列的选择率。

SQL-INDEX-2

 

我们可以看到该表上,OBJECT_ID列选择率为100%,STATUS列选择率为0,说明什么问题?OBJECT_ID没有重复的值,STATUS列选择率为0,基本都是统一个值,那么如果在OBJECT_ID列创建索引,效率一定会很高,直接定位到一条数据,如果在STATUS列创建索引,基本毫无意义,不如全表扫描来的多快读来的痛快,因为只有同一个值。所以创建索引时要看选择率的大小,如果选择率大于20%,则可以考虑创建索引,并不绝对,只是一个相对直观的数值参考。当然,你的表中至多才有几百条数据,那么索引不会排上什么用场,只有大表才会因为索引问题产生性能问题。所以,要在选择率较高的列上创建索引,谓词用到该列时才会起到索引的作用,否则,一本书的目录不同内容对应相同的页数,你知道要找的内容在哪里?还不是要把书从头翻到尾?

 创建索引带来的代价:

创建索引目的是为了帮助对相关数据的查询变得更加有效率,但是数据库中并不是只有SELECT需求,在OLTP系统中,DML高并发环境对索引的维护也是创建索引前需要考量的内容,例如INSERT频繁的TABLE对象,我们不难看到经常存在buffer busy wait,enq-ind-contention之类的等待,高并发INSERT时,一次只能由一个会话在进行INSERT工作,其余SESSION会出与等待状态,索引左右叶子快可能需要不断的分裂,形成了索引热点快争用。对于此现象,可以利用reverse反向索引与hash-partition进行改造,但是会带来其他的副作用,例如反向索引会无法使用index range scan功能,索引Cluster_factor会变得很大,接近于表的总行数;索引hash_partition缓解了insert的等待压力,但是对于非等值查询的逻辑读与物理读会少量增加,等值查询会带来成倍的增长,所以这都是索引的考量范围,一个INSERT表中有1个块发生变化,但是有多少个索引,就会有多少个叶子块发生变化,表中索引越多,INSERT速度越慢,要综合考量,不要局限于单个的业务场景,优化索引,一定是综合考量,选出“相对”合适的索引优化方案

 

 

 

压测过程中识别出的待优化SQL

继《普通Index改造为Hash Partition后对涉及索引列的SQL查询影响有多大》文章中提到,一套数据库进行压测后出现严重的性能瓶颈,分析完主要矛盾后识别出AWR中存在两个查询存在较大优化空间,详情如下:

20190910-SQL-1

SQL单次执行需要5-6秒时间,CPU%  81.50,我们分开来看:

SQL1:SQL_ID:f6bya75x9pm6s

20190910-SQL-2

继续阅读