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

继续阅读

又见Log File Sync~

上周四,应用部门中负责数据库维护的同事发来求助邮件,所属业务后台数据库出现严重性能异常,希望帮忙协助一下排查一下数据库问题,应用部门监控到了具体的异常时间点,如下图所示:

log_file_sync_1024-1

图中涉及到了两类数据库关键性能指标,1.DB_TIME,2.Active Session数据,从同事中邮件中反馈得知,24日的 16:00开始 数据库DB_TIME开始飙升,但是图中的ACTIVE_SESSION显示的异常事件点为14:00-15:00左右。两类指标异常的时间点并不吻合。具体数据库发生了什么? 登录到数据库后,查看了具体的信息:

ASH信息:

从ASH中看到,从13:57分开始,数据库中陆续出现log file sync等待事件,与同事提供的监控数据基本吻合。

1026-2

 

继续阅读

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

周末,一个朋友微信我咨询了一个问题,简单描述来说:利用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);

继续阅读

断开DataGuard操作产生的问题

前几日,同事在重启一套数据库其中1个节点的CRS时出现问题,数据库instance Mounted后自动Crach,实例被后台进程LGWR KILL掉。数据库信息与特点如下:

Linux x86-64  RAC 2节点,曾经配置过DataGuard但是因为备库IO性能不佳取消了DG配置,将log_archive_config与log_archive_dest_2(我们采用dest_2为DG配置)设置为空(很久以前的事了)。所以数据库长时间都是无DG配置运行。

不巧,今日此库1节点因后台日志报出大量ORA-00600,值班同事决定重启数据库节点尝试修复异常状态,但是发现数据库1节点无法启动,报出如下错误:

DG-1018-1

数据库成功Mount后,被lgwr进程Kill掉进程,并且报出有关log_archive_config 实例间参数不值不一致的错误。 同事将log_archive_config参数设置为’NODG_CONFIG’配置后解决,成功启动了实例,但是问题来了,DG配置已经摘掉了(包括Broker配置),为何还会抛出log_archive_config此参数相关报错?

想到这里,头脑中已经猜测到了一种可能性,准备在一套未投产并且同样为标准安装配置的相同环境下模拟验证:

(1)在主库1节点上尝试断开DG配置:

DG-1018-2

alter system set log_archive_config='' scope=both;
alter system set log_archive_dest_2 ='' scope=both;

--重启数据库CRS:
/opt/app/11.2.0/grid/bin/crsctl stop crs
/opt/app/11.2.0/grid/bin/crsctl start crs

DG-1018-3

继续阅读

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

核心生产上一个严重且有趣的Bug(二)

继《核心生产上一个严重且有趣的Bug(一)》所描述的问题,昨日我们进行了第二次实际生产Bug测试工作:

本次测试分为三个阶段:

(1)阶段一:基于Space Serach Cache是PGA管理的前提下,启动执行一次性提交夜维(简称“夜维1”)成功复现问题:

1010-1

1010-2

与故障当晚现象基本一致,INDEX UNQIUE SCAN 单块读一行 居然需要22万左右逻辑读,成功复现了异常现象。

(2)阶段二:设置10019 event,随后应用切换至备集群重新连接生产数据库,使得重新初始化PGA,执行夜维程序与正常updaet应用叠加:

1010-3

1010-4

 

逻辑读有显著的降低,但是因为第一次测试与第二次测试执行的夜维程序所删除的数据不同,会有这么一种情况存在,执行过delete后,extent中存在大量的null block,所以涉及第二次update时,首先要重用之前的空间并非申请新空间,是否经过space serach cache不好说,但是个人理解,只要申请块存放空间,都需要经过space serach cache的,无论初始化新块还是重用原来的空块。

(3)阶段三:将应用切回主集群,再次执行夜维程序与update进行叠加,因为主应用集群未重启,PGA未重新初始化,10019应该只对后续连接生效,对未清空PGA的不生效,但是也存在对新连接update生效的可能性。

1st:第二次测试,清空PGA后

2nd:第三次测试,重新连接主集群,未重启过应用,PGA未初始化。

1010-5

1010-6

undo一致读,逻辑读均有所提升,但是提升的非常有限。

结论:经过本次测试,认为event 10019还是有效果的,但是不排除三次删除的夜维数据特征不一样的客观影响,所以目前已将10019在数据库中持久化生效,并且应用将夜维程序更改为分批提交,再进行后续观察。

 

表连接方式(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。

 

 

核心生产上一个严重且有趣的Bug(一)

上周,公司核心生产数据库凌晨时出现异常,一条update语句响应缓慢,严重影响了核心数据库事务交易,详情如下:

数据库架构为3节点(主库)+3节点(备库),11.2.0.4 / Linux x86-64,故障期间短为00:31-00:32,TPS:10-20左右

SS-1

SS-2

AWR前台等待事件上来看,除去resmgr:cpu quantum event平均等待216ms外,并无其他较为突出的性能问题,故障期间段ASH信息如下:SS-3

什么是resmgr:cpu quantum event?

SS-4

从该等待事件释义中可以得到信息,可得知Oracle资源管理器在调度CPU时发生的等待。说明此时刻CPU资源产生了严重的瓶颈,资源管理器该等待事件存在的意义是当resource manager控制CPU调度时,此时数据库系统活动会话对CPU资源消耗需求较大,但是Oracle要保证对应的Group得到相应的CPU资源,对使用CPU进程进行队列排队机制,进入队列wait等待再次获得CPU资源,队列中wait阶段时,产生resmgr:cpu quantum event

SS-5

继续阅读