Oracle中_hash_join_enabled有何作用？优化器如何选择嵌套循环与哈希连接？

_hash_join_enabled是Oracle的隐藏初始化参数，核心作用是控制优化器是否允许将哈希连接（Hash Join）纳入连接算法的可选范围：

• 默认值为true，优化器可以根据成本计算结果选择是否使用哈希连接
• 设置为false后，优化器会完全排除哈希连接的选项，仅能在嵌套循环（Nested Loop）、排序合并连接（Sort Merge Join）两种算法中选择执行计划

你执行的alter session set "_hash_join_enabled" = false;属于会话级修改，仅对当前连接生效，不会影响其他业务会话的执行逻辑。

Oracle默认使用基于成本的优化器（CBO），会优先选择成本最低的连接算法，核心判定依据包括：

• 数据集大小：哈希连接适合大数据量的等值连接场景，会选择更小的表构建内存哈希表，再扫描大表匹配数据，整体IO和CPU成本更低；嵌套循环适合驱动表结果集小（通常小于1万行）、被驱动表连接字段存在高效索引的场景，每次循环通过索引快速查询匹配行，总成本低于哈希连接
• 连接条件类型：哈希连接仅支持等值连接，非等值连接场景下CBO不会考虑哈希连接
• 统计信息准确性：表的行数、占用块数、连接字段的distinct值、数据分布等统计信息的准确性，会直接影响CBO的成本计算结果，统计信息失真时很容易出现连接算法选择错误的问题，比如你遇到的误选哈希连接触发全表扫描的情况
• 人工配置优先级：hint、参数配置的优先级高于CBO默认成本计算，比如关闭_hash_join_enabled后，无论哈希连接成本多低都不会被选择

该参数一般仅用于临时应急或故障排查，不建议长期全局开启/关闭，常见使用场景如下：

• 执行计划故障应急：当CBO因统计信息失真、计算bug错误选择哈希连接，导致查询性能大幅下降，短时间内无法通过修改SQL、更新统计信息修复时，可以临时在对应业务会话中关闭该参数，强制优化器选择嵌套循环快速恢复性能
• OLTP系统性能稳定性优化：高并发OLTP场景下的查询大多是返回少量数据的短查询，连接字段普遍建有索引，理论上都应该走嵌套循环，部分场景下CBO可能出现执行计划跳变误选哈希连接，导致性能抖动，可以在业务连接池配置中统一会话级关闭该参数，避免执行计划异常
• 性能问题定位排查：出现连接相关的性能问题时，通过开关该参数对比两种连接算法的执行效率、执行计划差异，可以快速定位是否为连接算法选择错误导致的性能问题

内容的提问来源于stack exchange，提问作者GaneshBen