OB优化器问题

[未来的OBCE]

一个线上TP业务系统，每天跑批时有一个复杂的关联查询（涉及5张表的分区表连接）。平时运行耗时稳定在20秒，但从上周开始，每周五下午会突然飙升到200秒以上。通过监控发现，执行计划从原本的HASH JOIN变为了NESTED LOOP JOIN 。在确保表统计信息都是最新且准确的前提下，导致优化器“固执地”选择次优计划的根本原因可能是什么？如何在不修改SQL的情况下进行干预？

[缺德的预言家]

可能是某张表的统计信息变了，踩到某个优化器代价估算的缺陷（场景挺多的说不准）导致计划走偏，如果sql_id不变手动绑outline就行

[缺德的预言家]

执行频率应该不高吧，平均执行20s 都不是tp的sql了吧。。。

[AntTech_JQU7C2]

期待大佬

[AntTech_JQU7C2]

[AntTech_7NFZRT]

查看优化器，查看具体成本代价

[AntTech_RFKTWX]

是的，绑定outline

[淇铭]

@论坛小助手

[论坛小助手]

在确保表统计信息都是最新且准确的前提下，导致优化器选择次优计划的根本原因可能是：

1. 优化器的内部参数或环境变化导致了执行计划的选择改变。例如，系统统计信息（如I/O延迟、CPU速度等）的变化，或某些初始化参数和会话设置的变化（如PARALLEL、ENABLE_ROWSETS等），这些都可能影响优化器的成本估算。
2. 特定时间段（如每周五下午）可能存在额外的负载或竞争条件，导致优化器误判执行计划。
3. 存在未预见的bug或异常情况，例如优化器在特定版本中的行为异常。

在不修改SQL的情况下进行干预的方法有：

1. 使用优化器提示（Hint）强制指定连接方法，例如使用 /*+ USE_HASH_JOIN(t1 t2 t3 t4 t5) */ 提示来明确指定使用Hash Join。
2. 通过设置优化器参数来调整优化器的行为，例如增大或减小特定连接方法的成本权重，或者关闭可能导致问题的优化特性。
3. 对于OceanBase数据库，可以尝试开启或关闭 _NLJ_BATCHING_ENABLED 参数来控制是否使用batch join优化，以减少rescan次数提高效率。

更多信息请查看:

1. 常见的业务问题
2. 连接次序
3. 诊断实践：使用 SQL_PLAN_MONITOR 分析性能问题
4. 连接简介
5. 联接算法
6. OceanBase 集群运维管理之用户实操