Oracle表连接操作——HashJoin(哈希连接)下

建站服务器

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”。

安阳县网站建设公司创新互联,安阳县网站设计制作,有大型网站制作公司丰富经验。已为安阳县1000多家提供企业网站建设服务。企业网站搭建\外贸网站建设要多少钱,请找那个售后服务好的安阳县做网站的公司定做!

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的综合效率很高,特别在海量数据时代。


本文名称:Oracle表连接操作——HashJoin(哈希连接)下
文章位置:http://myzitong.com/article/cpcchc.html