11 哈希连接(HASH JOIN)--优化主题系列

哈希连接(HASHJOIN)

 

前文提到，嵌套循环只适合输出少量结果集。如果要返回大量结果集(比如返回100W数据)，根据嵌套循环算法，被驱动表可能会被扫描100W次，显然这是不对的。看到这里你应该明白为什么有些SQL优化了跑几秒，没优化跑几个小时甚至跑1天都不出结果。返回大量结果集适合走HASHJOIN。HASHJOIN算法非常复杂，这里就不讨论了。

 

提问某个查询一个超大型SQL 返回几十万条记录你去看执行计划里面有NL 有问题嘛？？

你们去看一个查询逻辑读超大上千万你们觉得可能是什么引起的？？

 

假设 1000W的逻辑读块大小是8KB 要扫描多少GB的数据？？要扫描76GB数据正常吗？？

select ceil(1000000*8*1024/1024/1024/1024) from dual; --76

 

假设你去监控一个SQL 跑了1个小时等待事件是db filesequential read那么很可能是什么原因？？

 

下面看一个HASH JOIN的例子(基于SCOTT,Oracle11gR2)

alter session set statistics_level=all;

select /*+full(dept) */e.ename,e.job,d.dname from emp e,deptd  where e.deptno=d.deptno;

select * fromtable(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

DEPT离HASHJOIN关键字最近，表示DEPT是驱动表。 Starts等于1，表示两个表都只扫描了一次。再次强调NESTEDLOOPS被驱动表会扫描多次。注意观察Omem,1Mem,Used-Mem，它表示HASH JOIN会消耗PGA，当驱动表太大，PGA不能容纳驱动表时，就会产生on-diskHASH JOIN(temp表空间)。现在再回去看看NESTEDLOOPS，它没有Omem,1Mem,Used-Mem，也就是说NESTEDLOOPS不消耗PGA。我们再看执行计划中 ID=1 这步，它有*号，前面提到，执行计划中有*表示这个操作有过滤(filter)或者是有(access)。HASH JOIN属于access。通过谓词过滤信息，我们可以知道HASHJOIN的JOIN列是哪些列在做JOIN。这里就是emp.deptno和dept.deptno做JOIN。

 

提问 HASHJOIN关键字有*此处不是过滤条件是join列access

提问 HASHJOIN哪个表是驱动表？？离关键字最近的

 

提问为什么OLTP系统PGA设置较小？？

OLTP 系统的PGA 大多数消耗在连接数上面一个连接大概消耗10MB

OLTP 系统绝大多数SQL返回数量少都是大量的 NL 因为NL 不消耗 PGA 所以说OLTP 系统PGA 设置小

提问为什么OLAP 系统 PGA 要设置大？？

OLAP HASH JOIN 特别多如果PGA 设置小了会导致大量的on-diskHASH JOIN或on-disksort

一般OLTP系统80%SGA20%PGA OLAP系统50%SGA50%PGA

 

提问为什么OLTP系统要设置SGA很大？？系统有运行SQL个数多需要缓存的buffercache大

OLTP系统优化的牛逼思想两个字缓存

 

HASH JOIN 需要注意的地方：

1.HASH JOIN 在OLTP环境一般没什么优化的地方，在OLAP环境中可以利用并行优化HASH JOIN。

2.利用等待事件监控HASHJOIN的时候，如果发现在做on-diskHASH JOIN(direct path read/write temp)，可以加大PGA,或者手工设置 workarea 分配较大的PGA内存。

3.在做SQL优化的时候，你要去检查HASH JOIN的JOIN列(通过HASH JOIN前面的ID去找ACCESS)选择性，如果HASH JOIN连接列选择性很低，那么HASHJOIN可能跑很久，这个时候可以自己尝试构造伪列进行JOIN，如果无法构造伪列，这个时候看看能否从业务上优化，就不要想着用SQL优化了。

4.HASH JOIN 选择小表做驱动表，小表指的不是表的行数，而是指的是行数*列宽度

  例子中，选择dept作为驱动表是因为

  dept大小4*(dname+loc+deptno)宽度< emp大小14*(ename,job,sal,deptno)宽度

  在做HASHJOIN优化的时候要特别注意这点。

5.HASH JOIN只能用于等值连接。

 

解释第五条 select *from a,b where a.id<=b.id;能走HASH JOIN吗？？不能

解释第四条 HASHJOIN是看segment_size而不是看返回行数

解释第三条如果HASH JOIN列选择性很低那么需要耗费大量的PGA 这个基本上没法优化