Hash Join是Oracle CBO時(shí)代經(jīng)常出現(xiàn)的一種連接方式，對(duì)海量數(shù)據(jù)處理時(shí)經(jīng)常出現(xiàn)在執(zhí)行計(jì)劃里。本篇的上篇（http://space.itpub.net/17203031/viewspace-697442） 介紹了Hash Join的一些外部特征和操作算法流程，下面我們一起看下一些影響到Hash Join的重要參數(shù)和內(nèi)部指標(biāo)。

3、Hash Join相關(guān)參數(shù)

Hash Join是CBO優(yōu)化器才能生成的執(zhí)行計(jì)劃操作，如果是選擇了RBO就不能生成包括Hash Join的執(zhí)行計(jì)劃。此外，與Hash Join相關(guān)的Oracle參數(shù)還包括下面幾個(gè)：

ü       Hash_Join_Enable

該參數(shù)是控制CBO啟用Hash Join的開關(guān)。如果設(shè)置為True，則表示CBO可以使用Hash Join連接方式，否則就不可以使用。在目前的版本中，該參數(shù)已經(jīng)演化為一個(gè)隱含參數(shù)，名稱為“_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

該參數(shù)的隱式化，也就說明了CBO已經(jīng)成熟到一定程度，Oracle官方不希望我們禁用掉這種Hash Join連接方式。當(dāng)然，我們可以從system和session兩層均可以暫時(shí)的禁用掉hash Join。

//此時(shí)_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，此時(shí)參數(shù)環(huán)境已經(jīng)變化；

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

可見，當(dāng)我們session級(jí)別禁用了hash Join連接之后，CBO不能進(jìn)行Hash Join路徑選擇。于是選擇了Merge Join路徑，顯然無論是執(zhí)行時(shí)間還是CPU成本，Merge Join略遜一籌。

ü       Hash_Area_Size

Hash Join操作是依賴獨(dú)立的私有空間，我們稱之為Hash_Area。Hash Area在Join過程中的作用就是將連接小表盡可能的緩存在Hash Area中，供進(jìn)行Hash匹配和Bucket內(nèi)部精確匹配。Hash Area是貯存在PGA中，屬于會(huì)話session獨(dú)立的一塊空間。如果Hash Area較小，不足以存放小表全部數(shù)據(jù)，就會(huì)引起Temp表空間的使用，進(jìn)而影響Hash Join性能。

SQL> show parameter hash

NAME                                TYPE       VALUE

------------------------------------ ----------- ------------------------------

hash_area_size                      integer    131072

因?yàn)槊恳粋€(gè)會(huì)話都會(huì)開啟一個(gè)Hash Area進(jìn)行Hash操作，所以通常Hash Area的大小不會(huì)設(shè)置很大。與Hash Area類似的空間是Sort Area，用于進(jìn)行SQL語句中的Order by操作，也是一個(gè)依賴分配的參數(shù)項(xiàng)目。通常，Hash Area被分配大小為Sort Area的兩倍。

SQL> show parameter sort_area

NAME                                TYPE       VALUE

------------------------------------ ----------- ------------------------------

sort_area_retained_size             integer    0

sort_area_size                      integer    65536

進(jìn)入Oracle 9i之后，特別是10g出現(xiàn)，Oracle共享內(nèi)存和獨(dú)占內(nèi)存分配策略呈現(xiàn)自動(dòng)化和自適應(yīng)化的趨勢(shì)，而且這種技術(shù)也逐漸成熟。DBA只需要確定Oracle數(shù)據(jù)庫總的內(nèi)存使用大?。?/span>memory_target），就會(huì)根據(jù)算法、負(fù)載不斷調(diào)整實(shí)現(xiàn)自適應(yīng)的內(nèi)存分區(qū)調(diào)整。

作為PGA分配，Oracle推出的自動(dòng)調(diào)控參數(shù)是pga_aggregate_target，表示所有會(huì)話的PGA總分配大小。如果不啟用PGA自動(dòng)分配，該參數(shù)值就是設(shè)置為0。

SQL> show parameter pga

NAME                                TYPE       VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target                big integer 0

ü       Hash_multiblock_io_count

該參數(shù)表示在進(jìn)行Hash Join連接操作的時(shí)候，一次可以讀取的塊個(gè)數(shù)。在最新的版本中，該參數(shù)已經(jīng)變成了一個(gè)隱含參數(shù)。

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

這個(gè)參數(shù)可以追溯到Oracle 8i時(shí)代，當(dāng)時(shí)設(shè)置的默認(rèn)值為1。在以后的版本中，通常設(shè)置為0。這個(gè)參數(shù)對(duì)IO影響重大，不同的硬件環(huán)境、系統(tǒng)負(fù)載下效果不同。所以，當(dāng)設(shè)置為0的時(shí)候，Oracle是會(huì)每次自動(dòng)計(jì)算該值。

作為我們來講，最好不要進(jìn)行該參數(shù)的設(shè)置。

4、連接三模式

Hash Join比較Merge Sort Join一個(gè)比較優(yōu)勢(shì)的地方，就是對(duì)PGA空間的有限使用上。但是，使用PGA畢竟是一種風(fēng)險(xiǎn)操作。因?yàn)?/span>Hash Area同Sort Area一樣，在小表不能完全裝入系統(tǒng)時(shí)，會(huì)調(diào)用Temp表空間的硬盤空間。這樣，就會(huì)引起一些問題。

下面關(guān)于三種模式的闡述，借鑒八神前輩的《Oracle Hash Join》（http://www.alidba.net/index.php/archives/440）。特此表示感謝。

針對(duì)不同的狀態(tài)，Oracle分別有不同的模式對(duì)應(yīng)。

Optimal模式

這是我們進(jìn)行Hash Join的最理想情況。驅(qū)動(dòng)表（小表）生成的Hash數(shù)據(jù)集合可以完全存放在Hash Area的時(shí)候，我們稱之為Optimal模式。

ü       首先找到驅(qū)動(dòng)表，獲取到驅(qū)動(dòng)表。存放在Hash_Area中；

ü       在Hash Area中，對(duì)驅(qū)動(dòng)表進(jìn)行Hash操作，形成Hash Bulket，形成對(duì)應(yīng)的分區(qū)信息。針對(duì)多個(gè)Bulket，同時(shí)形成一個(gè)Bitmap列表，做到Bulket與Bitmap位的聯(lián)系；

ü       在各個(gè)Bulket中，分布著不同的數(shù)據(jù)行。如果連接列分布比較均勻，Bulket中數(shù)據(jù)也就比較均勻。如果Bulket中包括數(shù)據(jù)，對(duì)應(yīng)該Bulket的Bitmap位上為1，否則為0；

ü       找被驅(qū)動(dòng)表的每一列，將連接列值進(jìn)行Hash處理。匹配Bitmap位，如果Bitmap為0，表示該列值沒有存在，直接拋棄。否則進(jìn)入Bulket進(jìn)行精確匹配；

Onepass模式

如果我們?cè)O(shè)置的PGA空間小，或者連接的小表體積就已經(jīng)很大了，那么就會(huì)利用到臨時(shí)表空間。具體處理，就是進(jìn)行兩次的Hash處理，在Bulket層面的上面建立Partition分區(qū)。

當(dāng)進(jìn)行Hash操作的時(shí)候，出現(xiàn)的情形是一部分的Partition在內(nèi)存中，另一部分Partition被存放在Temp表空間上。

在進(jìn)行連接匹配的時(shí)候，如果能夠在Bitmap中確定到Partition在內(nèi)存中，那么直接在內(nèi)存中進(jìn)行檢索和精確匹配過程。否則從Temp表空間中將對(duì)應(yīng)的Partition調(diào)取到內(nèi)存中，進(jìn)行匹配操作。

Multipass模式

這是一種很極端的情況，如果Hash Area小到一個(gè)Partition都裝不下。當(dāng)進(jìn)行Hash操作后，只有半個(gè)Partition能裝入到Hash Area。

這種情況下，如果一個(gè)Partition匹配沒有做到，還不能夠放棄操作，要將剩下一半的Partition獲取到進(jìn)行Hash Join匹配。也就是一個(gè)Partition要經(jīng)過兩次的Bitmap匹配過程。

5、結(jié)論

Hash Join是一種效率很高，CBO時(shí)代很常見的連接方式。但是，相對(duì)于其他古典算法，Hash Join的綜合效率很高，特別在海量數(shù)據(jù)時(shí)代。