Hash Join是Oracle CBO时代经常出现的一种连接方式，对海量数据处理时经常出现在执行计划里。本篇的上篇（http://space.itpub.net/17203031/viewspace-697442） 介绍了Hash
Join的一些外部特征和操作算法流程，下面我们一起看下一些影响到Hash Join的重要参数和内部指标。
3、Hash
Join相关参数
Hash Join是CBO优化器才能生成的执行计划操作，如果是选择了RBO就不能生成包括Hash
Join的执行计划。此外，与Hash Join相关的Oracle参数还包括下面几个：
ü Hash_Join_Enable
该参数是控制CBO启用Hash
Join的开关。如果设置为True，则表示CBO可以使用Hash
Join连接方式，否则就不可以使用。在目前的版本中，该参数已经演化为一个隐含参数，名称为“_hash_join_enable”。
SQL> col name for a20;
SQL> col value for a10;
SQL> col DESCRIB for a30;
SQL> SELECT x.ksppinm NAME,
y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv
y
3 WHERE x.inst_id = USERENV
('Instance')
4 AND y.inst_id = USERENV
('Instance')
5 AND x.indx = y.indx
6 AND x.ksppinm LIKE
'%hash_join_enable%';
NAME VALUE DESCRIB
-------------------- ----------
------------------------------
_hash_join_enabled TRUE enable/disable
hash join
该参数的隐式化，也就说明了CBO已经成熟到一定程度，Oracle官方不希望我们禁用掉这种Hash
Join连接方式。当然，我们可以从system和session两层均可以暂时的禁用掉hash
Join。
//此时_hash_join_enable=true
SQL>
explain plan for select * from segs, tabs where segs.segment_name=tabs.table_name;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2106473715
---------------------------------------------------------------------------
| Id | Operation |
Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 990
| 354K| 25 (4)| 00:00:01 |
|* 1 | HASH JOIN | | 990
| 354K| 25 (4)| 00:00:01 |
| 2 | TABLE
ACCESS FULL| TABS | 968 | 229K| 11 (0)| 00:00:01 |
| 3 | TABLE
ACCESS FULL| SEGS | 2267 | 274K| 13 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by
operation id):
---------------------------------------------------
1 - access("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
15 rows selected
//session层面禁用hash_join连接
SQL>alter session
set "_hash_join_enabled"=false;
Session altered
NAME VALUE DESCRIB
-------------------- ----------
------------------------------
_hash_join_enabled FALSE enable/disable
hash join
//相同的SQL，此时参数环境已经变化；
SQL>
explain plan for select * from segs, tabs where segs.segment_name=tabs.table_name;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 3475644097
------------------------------------------------------------------------------------
| Id | Operation |
Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 990
| 354K| | 144 (2)| 00:00:02 |
| 1 | MERGE
JOIN | | 990 | 354K| | 144 (2)| 00:00:02 |
| 2 | SORT
JOIN | | 968 | 229K| 712K| 65 (2)| 00:00:01 |
| 3 | TABLE
ACCESS FULL| TABS | 968 | 229K| | 11 (0)| 00:00:01 |
|* 4 | SORT
JOIN | | 2267 | 274K| 824K| 79 (2)| 00:00:01 |
| 5 | TABLE
ACCESS FULL| SEGS | 2267 | 274K| | 13 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by
operation id):
---------------------------------------------------
4 - access("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
filter("SEGS"."SEGMENT_NAME"="TABS"."TABLE_NAME")
已选择18行。
可见，当我们session级别禁用了hash
Join连接之后，CBO不能进行Hash Join路径选择。于是选择了Merge
Join路径，显然无论是执行时间还是CPU成本，Merge Join略逊一筹。
ü Hash_Area_Size
Hash
Join操作是依赖独立的私有空间，我们称之为Hash_Area。Hash Area在Join过程中的作用就是将连接小表尽可能的缓存在Hash
Area中，供进行Hash匹配和Bucket内部精确匹配。Hash
Area是贮存在PGA中，属于会话session独立的一块空间。如果Hash
Area较小，不足以存放小表全部数据，就会引起Temp表空间的使用，进而影响Hash Join性能。
SQL> show parameter hash
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
hash_area_size integer 131072
因为每一个会话都会开启一个Hash
Area进行Hash操作，所以通常Hash Area的大小不会设置很大。与Hash
Area类似的空间是Sort Area，用于进行SQL语句中的Order
by操作，也是一个依赖分配的参数项目。通常，Hash Area被分配大小为Sort Area的两倍。
SQL> show parameter sort_area
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
sort_area_retained_size integer 0
sort_area_size integer 65536
进入Oracle 9i之后，特别是10g出现，Oracle共享内存和独占内存分配策略呈现自动化和自适应化的趋势，而且这种技术也逐渐成熟。DBA只需要确定Oracle数据库总的内存使用大小（memory_target），就会根据算法、负载不断调整实现自适应的内存分区调整。
作为PGA分配，Oracle推出的自动调控参数是pga_aggregate_target，表示所有会话的PGA总分配大小。如果不启用PGA自动分配，该参数值就是设置为0。
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
pga_aggregate_target big
integer 0
ü Hash_multiblock_io_count
该参数表示在进行Hash Join连接操作的时候，一次可以读取的块个数。在最新的版本中，该参数已经变成了一个隐含参数。
SQL> SELECT x.ksppinm NAME,
y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv
y
3 WHERE x.inst_id = USERENV
('Instance')
4 AND y.inst_id = USERENV
('Instance')
5 AND x.indx = y.indx
6 AND x.ksppinm LIKE '%hash_multiblock%';
NAME VALUE DESCRIB
------------------------------
---------- ------------------------------
_hash_multiblock_io_count 0 number
of blocks hash join wil
l read/write
at once
这个参数可以追溯到Oracle 8i时代，当时设置的默认值为1。在以后的版本中，通常设置为0。这个参数对IO影响重大，不同的硬件环境、系统负载下效果不同。所以，当设置为0的时候，Oracle是会每次自动计算该值。
作为我们来讲，最好不要进行该参数的设置。
4、连接三模式
Hash Join比较Merge
Sort Join一个比较优势的地方，就是对PGA空间的有限使用上。但是，使用PGA毕竟是一种风险操作。因为Hash
Area同Sort Area一样，在小表不能完全装入系统时，会调用Temp表空间的硬盘空间。这样，就会引起一些问题。
下面关于三种模式的阐述，借鉴八神前辈的《Oracle
Hash Join》（http://www.alidba.net/index.php/archives/440）。特此表示感谢。
针对不同的状态，Oracle分别有不同的模式对应。
Optimal模式
这是我们进行Hash Join的最理想情况。驱动表（小表）生成的Hash数据集合可以完全存放在Hash
Area的时候，我们称之为Optimal模式。
ü 首先找到驱动表，获取到驱动表。存放在Hash_Area中；
ü 在Hash
Area中，对驱动表进行Hash操作，形成Hash Bulket，形成对应的分区信息。针对多个Bulket，同时形成一个Bitmap列表，做到Bulket与Bitmap位的联系；
ü 在各个Bulket中，分布着不同的数据行。如果连接列分布比较均匀，Bulket中数据也就比较均匀。如果Bulket中包括数据，对应该Bulket的Bitmap位上为1，否则为0；
ü 找被驱动表的每一列，将连接列值进行Hash处理。匹配Bitmap位，如果Bitmap为0，表示该列值没有存在，直接抛弃。否则进入Bulket进行精确匹配；
Onepass模式
如果我们设置的PGA空间小，或者连接的小表体积就已经很大了，那么就会利用到临时表空间。具体处理，就是进行两次的Hash处理，在Bulket层面的上面建立Partition分区。
当进行Hash操作的时候，出现的情形是一部分的Partition在内存中，另一部分Partition被存放在Temp表空间上。
在进行连接匹配的时候，如果能够在Bitmap中确定到Partition在内存中，那么直接在内存中进行检索和精确匹配过程。否则从Temp表空间中将对应的Partition调取到内存中，进行匹配操作。
Multipass模式
这是一种很极端的情况，如果Hash Area小到一个Partition都装不下。当进行Hash操作后，只有半个Partition能装入到Hash
Area。
这种情况下，如果一个Partition匹配没有做到，还不能够放弃操作，要将剩下一半的Partition获取到进行Hash
Join匹配。也就是一个Partition要经过两次的Bitmap匹配过程。
5、结论
Hash Join是一种效率很高，CBO时代很常见的连接方式。但是，相对于其他古典算法，Hash
Join的综合效率很高，特别在海量数据时代。
posted @
2014-01-09 10:57
Pekkle
阅读(
560)
评论()
编辑
收藏
举报
