Oracle 19c哈希连接查询性能异常及无提示优化咨询

问题原因解析 #

1. 执行超时的核心原因 #

第一个执行计划采用哈希外连接，需要对TABLE_TD执行全索引快速扫描（涉及231K行数据），再与前面的结果集做哈希匹配。哈希连接需要将TABLE_TD的全量数据加载到内存（内存不足时会写入临时表），导致大量磁盘IO，这是30分钟执行时间的直接瓶颈。

2. 为何关联未被引用的TABLE_TD #

Oracle优化器默认不会自动移除未被引用的左连接表，即使该表不影响查询结果。这种情况通常是因为优化器未识别出该表的连接对结果无贡献，或者受限于内部规则（比如误判表存在触发器等副作用）。

3. Oracle选择哈希连接计划的原因 #

Oracle成本优化器（CBO）基于统计信息计算执行路径成本：第一个计划的成本（35213）低于嵌套循环计划（42448），CBO认为哈希连接的CPU和IO成本更低。但实际执行中，哈希连接需要全量扫描TABLE_TD，而嵌套循环仅需对前面结果集的3745行做单次索引范围扫描，实际开销远低于CBO的估计——这种偏差源于统计信息对TABLE_TD索引选择性的估计不准，或对哈希连接内存使用的判断有误。

无需强制提示的优化方案 #

1. 移除冗余左连接（最直接有效） #

直接删除LEFT JOIN TABLE_TD TD ON (TD.PROCESSOR = t.PROCESSOR AND TD.ABC = t.ID)语句，该表既不在SELECT列表，也不影响WHERE条件与结果集，完全属于冗余连接，移除后可彻底消除其带来的开销。

2. 重新收集精准统计信息 #

尽管你已更新统计信息，可尝试收集更细粒度的数据，确保CBO准确评估执行成本：

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'TABLE_T', CASCADE => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'TABLE_TD', CASCADE => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE);

3. 优化TABLE_TD的索引 #

确保TABLE_TD_IDX是包含PROCESSOR和ABC的复合索引（建议顺序为(ABC, PROCESSOR)或(PROCESSOR, ABC)），提升嵌套循环时的索引扫描效率，让CBO更倾向于选择嵌套循环计划。

4. 调整优化器模式 #

如果业务更关注快速返回结果，可设置优化器模式为FIRST_ROWS_n，引导CBO优先选择能快速返回前N行的计划（通常为嵌套循环）：

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_100;

或在查询中添加灵活提示：

SELECT /*+ FIRST_ROWS(100) */
TU.ID AS RecordID
FROM TABLE_U TU
-- 剩余查询语句不变

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