oracle 主键 hash,Oracle里的哈希连接原理(二)

13、因为在构建Si和Bj时用的是同样的哈希函数hash_func_1和hash_func_2，所以Oracle在处理位于磁盘上的Si和Bj的时候可以放心的配对处理，即只有对应Hash Partition Number值相同的Si和Bj才可能会产生满足连接条件的记录；这里我们用Sn和Bn来表示位于磁盘上且对应Hash Partition Number值相同的Si和Bj；

14、对于每一对儿Sn和Bn，它们之中记录数较少的会被当作驱动结果集，然后Oracle会用这个驱动结果集的Hash Bucket里记录的hash_value_2来构建新的Hash Table，另外一个记录数较大的会被当作被驱动结果集，然后Oracle会用这个被驱动结果集的Hash Bucket里记录的hash_value_2去上述构建的新Hash Table中找匹配记录；注意，对每一对儿Sn和Bn而言，Oracle始终会选择它们中记录数较少的来作为驱动结果集，所以每一对儿Sn和Bn的驱动结果集都可能会发生变化，这就是所谓的“动态角色互换”；

15、步骤14中如果存在匹配记录，则该匹配记录也会作为满足目标SQL连接条件的记录返回；

16、上述处理Sn和Bn的过程会一直持续下去，直到遍历完所有的Sn和Bn为止。

对于哈希连接的优缺点及适用场景，我们有如下总结：

Ÿ哈希连接不一定会排序，或者说大多数情况下都不需要排序；

Ÿ哈希连接的驱动表所对应的连接列的可选择性应尽可能的好，因为这个可选择性会影响对应Hash Bucket中的记录数，而Hash Bucket中的记录数又会直接影响从该Hash Bucket中查找匹配记录的效率；如果一个Hash Bucket里所包含的记录数过多，则可能会严重降低所对应哈希连接的执行效率，此时典型的表现就是该哈希连接执行了很长时间都没有结束，数据库所在database server上的CPU占用率很高，但目标SQL所消耗的逻辑读却很低，因为此时大部分时间都耗费在了遍历上述Hash Bucket里的所有记录上，而遍历Hash Bucket里记录这个动作是发生在PGA的工作区里，所以不耗费逻辑读；

Ÿ哈希连接只适用于CBO、它也只能用于等值连接条件(即使是哈希反连接，Oracle实际上也是将其转换成了等价的等值连接)；

Ÿ哈希连接很适合于一个小表和大表之间的表连接，特别是在小表的连接列的可选择性非常好的情况下，这时候哈希连接的执行时间就可以近似看作是和全表扫描那个大表所耗费的时间相当；

Ÿ当两个表做哈希连接时，如果这两个表在施加了目标SQL中指定的谓词条件(如果有的话)后得到的结果集中数据量较小的那个结果集所对应的Hash Table能够完全被容纳在内存中时(PGA的工作区)，则此时的哈希连接的执行效率会非常高。

我们可以借助于10104事件所产生的trace文件来观察目标SQL在做哈希连接时的大致过程和一些统计信息(比如用了多少个Hash Partition、多个少Hash Bucket以及各个Hash Bucket都分别有多少条记录等)，10104事件在我们实际诊断哈希连接的性能问题时非常有用。

使用10104事件观察目标SQL做哈希连接的具体过程为：

oradebug setmypid

oradebug event 10104 trace name context forever, level 1

set autotrace traceonly

实际执行目标SQL(必须要实际执行该SQL，不能用explain plan for)

oradebug tracefile_name

一个典型的10104事件所产生的trace文件内容为如下所示：

kxhfInit(): enter

kxhfInit(): exit

*** RowSrcId: 1 HASH JOIN STATISTICS (INITIALIZATION) ***

Join Type: INNER join

Original hash-area size: 3642760

Memory for slot table: 2826240

Calculated overhead for partitions and row/slot managers: 816520

Hash-join fanout: 8

Number of partitions: 8

Number of slots: 23

Multiblock IO: 15

Block size(KB): 8

Cluster (slot) size(KB): 120

Minimum number of bytes per block: 8160

Bit vector memory allocation(KB): 128

Per partition bit vector length(KB): 16

……省略显示部分内容

Slot table resized: ld=23 wanted=12 got=12 unload=0

*** RowSrcId: 1 HASH JOIN RESIZE BUILD (PHASE 1) ***

Total number of partitions: 8

Number of partitions which could fit in memory: 8

Number of partitions left in memory: 8

Total number of slots in in-memory partitions: 8

kxhfResize(enter): resize to 14 slots (numAlloc=8, max=12)

kxhfResize(exit): resized to 14 slots (numAlloc=8, max=14)

set work area size to: 2215K (14 slots)

*** RowSrcId: 1 HASH JOIN BUILD HASH TABLE (PHASE 1) ***

Total number of partitions: 8

Number of partitions left in memory: 8

Total number of rows in in-memory partitions: 1000

(used as preliminary number of buckets in hash table)

Estimated max # of build rows that can fit in avail memory: 79800

### Partition Distribution ###

Partition:0rows:120clusters:1slots:1kept=1

Partition:1rows:122clusters:1slots:1kept=1

……省略显示部分内容

Partition:6rows:118clusters:1slots:1kept=1

Partition:7rows:137clusters:1slots:1kept=1

*** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***

Revised number of hash buckets (after flushing): 1000

Allocating new hash table.

*** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***

Requested size of hash table: 256

Actual size of hash table: 256

Number of buckets: 2048

Match bit vector allocated: FALSE

*** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***

Total number of rows (may have changed): 1000

Number of in-memory partitions (may have changed): 8

Final number of hash buckets: 2048

Size (in bytes) of hash table: 8192

qerhjBuildHashTable(): done hash-table on partition=7, index=0 last_slot#=3 rows=137 total_rows=137

qerhjBuildHashTable(): done hash-table on partition=6, index=1 last_slot#=4 rows=118 total_rows=255

……省略显示部分内容

qerhjBuildHashTable(): done hash-table on partition=1, index=6 last_slot#=2 rows=122 total_rows=880

qerhjBuildHashTable(): done hash-table on partition=0, index=7 last_slot#=5 rows=120 total_rows=1000

kxhfIterate(end_iterate): numAlloc=8, maxSlots=14

*** (continued) HASH JOIN BUILD HASH TABLE (PHASE 1) ***

### Hash table ###

# NOTE: The calculated number of rows in non-empty buckets may be smaller

#than the true number.

Number of buckets with0 rows:1249

Number of buckets with1 rows:626

Number of buckets with2 rows:149

Number of buckets with3 rows: 21

Number of buckets with4 rows:3

Number of buckets with5 rows:0

……省略显示部分内容

Number of buckets with between90 and99 rows:0

Number of buckets with 100 or more rows:0

### Hash table overall statistics ###

Total buckets: 2048 Empty buckets: 1249 Non-empty buckets: 799

Total number of rows: 1000

Maximum number of rows in a bucket: 4

Average number of rows in non-empty buckets: 1.251564

Disabled bitmap filtering: filtered rows=0 minimum required=50 out f=1000

qerhjFetch: max probe row length (mpl=0)

*** RowSrcId: 1, qerhjFreeSpace(): free hash-join memory

kxhfRemoveChunk: remove chunk 0 from slot table

注意到上述显示内容中我粗体标出的部分，如“Number of in-memory partitions (may have changed): 8”、“Final number of hash buckets: 2048”、“Total buckets: 2048 Empty buckets: 1249 Non-empty buckets: 799”、“Total number of rows: 1000”、“Maximum number of rows in a bucket: 4”、“Disabled bitmap filtering: filtered rows=0 minimum required=50 out f=1000”等，这说明上述哈希连接驱动结果集的记录数为1000，共有8个Hash Partition、2048个Hash Bucket，这2048个Hash Bucket中有1249个是空的(即没有记录)、799个有记录，包含记录数最多的一个Hash Bucket所含记录的数量为4以及上述哈希连接并没有启用位图过滤。