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

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

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

| 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

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

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

| 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》（）。特此表示感謝。

針對不同的狀態，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表連線操作——Hash Join（雜湊連線）下

相關文章