崔华,网名 dbsnake
Oracle ACE Director,ACOUG 核心专家
编辑手记:感谢崔华授权我们独家转载其精品文章,也欢迎大家向“Oracle”社区投稿。
哈希连接(HASH JOIN)是一种两个表在做表连接时主要依靠哈希运算来得到连接结果集的表连接方法。
在 Oracle 7.3之前,Oracle 数据库中的常用表连接方法就只有排序合并连接和嵌套循环连接这两种,但这两种表连接方法都有其明显缺陷:
为了解决排序合并连接和嵌套循环连接在上述情形下执行效率不高的问题,同时也为了给优化器提供一种新的选择,Oracle 在 Oracle 7.3 中引入了哈希连接。从理论上来说,哈希连接的执行效率会比排序合并连接和嵌套循环连接的执行效率要高,当然,实际情况并不总是这样。
在 Oracle 10g 及其以后的 Oracle 数据库版本中,优化器(实际上是 CBO,因为哈希连接仅适用于 CBO)在解析目标 SQL 时是否考虑哈希连接是受限于隐含参数 _HASH_JOIN_ENABLED,而在 Oracle 10g 以前的 Oracle 数据库版本中,CBO 在解析目标 SQL 时是否考虑哈希连接是受限于参数 HASH_JOIN_ENABLED。
_HASH_JOIN_ENABLED 的默认值是 TRUE,表示允许 CBO 在解析目标 SQL时考虑哈希连接。当然,即使你将该参数的值改成了 FALSE,我们使用 USE_HASH Hint 依然可以让 CBO 在解析目标 SQL 时考虑哈希连接,这说明 USE_HASH Hint 的优先级高于参数 _HASH_JOIN_ENABLED。
如果两个表(这里将它们分别命名为表 T1 和表 T2)在做表连接时使用的是哈希连接,则 Oracle 在做哈希连接时会依次顺序执行如下步骤:
对于哈希连接的优缺点及适用场景,我们有如下总结:
可以借助于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: old=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:0 rows:120 clusters:1 slots:1 kept=1
Partition:1 rows:122 clusters:1 slots:1 kept=1
……省略显示部分内容
Partition:6 rows:118 clusters:1 slots:1 kept=1
Partition:7 rows:137 clusters:1 slots:1 kept=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 with 0 rows: 1249
Number of buckets with 1 rows: 626
Number of buckets with 2 rows: 149
Number of buckets with 3 rows: 21
Number of buckets with 4 rows: 3
Number of buckets with 5 rows: 0
……省略显示部分内容
Number of buckets with between 90 and 99 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 of=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 of=1000”
等,这说明上述哈希连接驱动结果集的记录数为1000,共有8个 Hash Partition、2048个 Hash Bucket,这2048个 Hash Bucket 中有1249个是空的(即没有记录)、799个有记录,包含记录数最多的一个 Hash Bucket 所含记录的数量为4以及上述哈希连接并没有启用位图过滤。
近期文章
从Approx_Count_Distinct到M7的CPU集成
Cloud时代DBA的DevOps最佳实践 - SQL 审核
数据驱动,成就未来。整合业界顶尖的技术与合作伙伴资源,围绕数据及相关领域,提供解决方案和专业服务。
业务架构
电子渠道(网络销售)分析系统、数据治理
IT基础架构
分布式存储解决方案
数据架构
Oracle DB2 MySQL NoSQL
专项服务:架构/安全/容灾/优化/整合/升级/迁移
运维服务:运维服务 代维服务
人才培养:个人认证 企业内训
软件产品:SQL审核、监控、数据恢复
应用架构
应用软件和中间件:数据建模 | SQL审核和优化 | 中间件服务