表连接方式(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情况.

一则ORA-15041: diskgroup space exhausted问题处理

近期,公司生产数据库遇到表空间使用率高报警,按照流程resize时遭遇ORA-15041: diskgroup space exhausted报警,无法扩充表空间;但是根据报错,ASM DISKGROUP磁盘组还有大量FREE空闲空间,但是等待一会后便resize成功,险些造成生产数据库数据无法入库故障。

根据故障现象,登陆数据库排查问题,发现了奇怪的地方:

FAILGROUP-1

此数据库DG_DATA DISKGROUP 存在4个FAILGROUP,观察发现规律,安装初始时必要的磁盘DATA_01/02磁盘在A/B FAILGROUP,后续业务投产因数据增长后续添加的磁盘在C,D FAILGROUP中,应该是后续操作添加ASM DISK时没有注意FAILGROUP的命名规则,直接执行了alter diskgroup DG_DATA add failgroup “想当然的名字” 进行了添加磁盘操作。从而带来了副作用:因整个DG_DATA还有大量空闲空间,但是经发现:A,B FAILGROUP中,DATA_02只剩下24MB空闲空间,后续添加多块磁盘时使用alter diskgroup dg_data add failgroup C/D disk ‘****’  rebalance power 0后,没有进行统一的rebalance工作,导致触发木桶效应,无法resize或添加add datafile.

修复工作:

FAILGROUP-2

我在测试系统中模拟处理故障现象,DG_DATA_003在非规范命名的ALBERT FAILGROUP中,需要将此磁盘剔除FAILGROUP,重新加入到TRAVELSKYDBA419中。

继续阅读

蹊跷的CheckPoint Not Complete

上周临近周末,一个应用业务人员发来邮件,告知所属业务后台数据库在18点左右出现业务查询与事务操作响应时间变长的现象,如下图所示:

0923-1

从应用反馈与来看,18:00左右数据库外部用户无法登陆存在积压现象,数据库内部应用SQL缓慢,感觉此时数据库“夯死”了。我们来看一下ASH的情况:

0923-2

继续阅读

表连接方式-(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速度越慢,要综合考量,不要局限于单个的业务场景,优化索引,一定是综合考量,选出“相对”合适的索引优化方案

 

 

 

enq: US – contention 等待事件

近期做数据库优化时,发现数据库AWR报告TOP 10等待事件中出现了一个enq:US-CONTENTION等待事件,性能瓶颈时AWR信息如下:

US-1

虽然该等待事件并不是引起数据库性能瓶颈的主要矛盾,主要矛盾分析请见《普通Index改造为Hash Partition后对涉及索引列的SQL查询影响有多大?》,那么什么enq: US – contention 等待事件?

继续阅读

中秋节快乐–中秋节前DataGuard切换演练产生的问题

中秋节前公司核心业务数据库进行DataGuard切换演练时,同事进行主备库Switch Over操作后,测试应用状态时,报出大量ORA-01033: ORACLE initialization or shutdown in progress报错,应用无法正常连接切换后的新主库。

什么是ORA-1033错误?来看一下官方解释:

dg-1033-1    数据库正在初始化启动与关闭过程中出现过程中,有用户连接数据库时便会报出ORA-01033错误,关于此错误很好理解,与现场操作切换的同事沟通交流后得知:switchover切换正常,数据库本身没有出现异常现象,OK,综合以上信息,已经知道了到产生此现象的原因了。我们来做了一个实验,重现一下这个现象:

测试信息与应用TNS如下:

dg-1033-2

 

继续阅读

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

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

20190910-SQL-1

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

SQL1:SQL_ID:f6bya75x9pm6s

20190910-SQL-2

继续阅读

普通Index改造为Hash Partition后对涉及索引列的SQL查询影响有多大?

公司一套数据库中在压测过程中出现严重的性能瓶颈,导致压测没有达到预计TPS,相关信息如下:

数据库: Liunx x86 Oracle 11.2.0.4 RAC 2节点 32 CPU

节点1:Cpu Idle信息,可以看到在压测过程中,CPU使用率接近于100%,节点2到达80%左右

165-CPU-1

165-CPU-2

数据库AWR报告信息:

165-AWR1

继续阅读

DataGuard常见问题( 二)注册日志恢备库GAP

DataGuard常见问题(一)增量备份恢复主备库GAP实验后,在我们维护Dataguard工作还经常需要用到另一种恢复备库日志GAP的方式-alter database register logfile。通常,这种方法相比增量备份恢复方式更为简单,成本也相对较少,但是前提是主库的归档没有被删除仍然可见的情况下,所以自然就不需要主库增量备份来帮忙,直接将主库的GAP归档日志传输至备库进行恢复即可。

实验内容:

(1)破坏DG关系,模拟GAP产生:

2GAP-1

2gap-2

主库目前Sequence为8543,备库等待8542 Sequnece出现Wait_For_Log状态:

将备库缺失归档传输至主库并进行 alter database register logfile,使得备库识别到缺失归档,否则该文件传输至相关目标路径后,仍然只是个文件而已,对于备库来说只不过是个路人甲。

2gap-3

向备库注册日志后,备库实时应用恢复正常,Wait_For_Log状态消失。

2gap-4