oracle性能优化篇-_optimizer_cost_based_transformation参数设置导致系统慢了1000倍

一、用户反馈前台某个模块慢

通过跟踪定位到了该sql（sqltrace）

二、排查服务器环境

该参数是控制优化器是否对 SQL 进行基于代价的查询转换，以及转换的强度。这类转换会尝试将 SQL 改写为语义等价但执行代价更低的形式（如子查询展开、连接重排、谓词下推等），OFF代表完全禁用基于代价的查询转换。表示优化器不会尝试复杂的代价计算和改写。使用传统保守的方式产生执行计划。

第一张图（OPT_PARAM(
'OFF')）

• 关键问题：病人结帐记录 表出现 TABLE ACCESS FULL（全表扫描），耗费 1,018，是性能瓶颈。

• 原因：禁用代价转换后，优化器无法通过改写将过滤条件下推到索引，只能扫描全表。

第二张图（OPT_PARAM(
'linear')）

• 关键优化：病人结帐记录 表变为 TABLE ACCESS BY INDEX ROWID + INDEX UNIQUE SCAN，耗费降至 1。

• 原因：启用 linear 模式后，优化器通过代价转换将过滤条件下推，命中了 病人结帐记录_PK 主键索引，避免了全表扫描。

从执行计划看，linear（默认值）是更优选择，它让优化器成功利用了 病人结帐记录_PK 索引，将全表扫描转为索引唯一扫描，性能提升了约 1000 倍。

三、处理方式

还原该参数为默认值；

四、建议

1、dba不能随便去设置参数，按照网上的经验应该怎么怎么调，很多参数不适合你当前的业务系统，没有经验的情况下不能随意调整，系统层面的参数是全局的，影响全部的sql执行计划。往往可能最原始的参数才是最适合你的系统。 2、本案例就是错误的调整oracle隐含参数，导致执行效率相差1000倍的案例。 3、在相同的sql不同库执行效率不同，我们可以通过观察sql执行计划中的outline部分，看是否有不同的优化器参数，通过hint的方式尝试改变执行计划来处理。 4、文末贴上本人常用的hint优化参数收藏，如有问题欢迎指正。

 /*+ OPT_PARAM(
'_optimizer_adaptive_plans',
'false') */ --关闭自适应执行计划，默认值
true，修改为
false（自适应执行计划功能（Oracle 12c+ 新特性），运行时根据实际数据量动态切换执行计划（如嵌套循环 / 哈希连接））

 /*+ OPT_PARAM(
'_optimizer_enhanced_join_elimination',
'false') */ --增强版连接消除优化，优化器自动删除无意义的表连接（如外连接无过滤 / 无取值时） 设置为
false，默认
true

 /*+ OPT_PARAM(
'_optimizer_reduce_groupby_key',
'false') */ --优化器简化 GROUP BY 键，自动移除重复 / 冗余的分组列。默认值：
true（开启），可取值：
true / 
false  关闭效果：严格按照 SQL 编写的 GROUP BY 列执行，不做简化

 /*+ OPT_PARAM(
'_optimizer_squ_bottomup',
'false') */ --作用：控制子查询优化方式，自底向上解析子查询（SQU = Subquery）。默认值：
true（开启）可取值：
true / 
false 关闭效果：切换为自顶向下解析子查询，可能改变子查询执行计划

 /*+ OPT_PARAM(
'_optimizer_aggr_groupby_elim',
'false') */  --作用：聚合 / GROUP BY 消除优化，无意义的聚合 / 分组时自动删除逻辑。默认值：
true（开启）可取值：
true / 
false 关闭效果：强制保留所有 GROUP BY 和聚合函数，不优化

 /*+ OPT_PARAM(
'_optimizer_gather_feedback',
'false') */ --作用：执行计划反馈优化，收集运行时统计信息，自动重新生成更优计划。默认值：
true（开启）可取值：
true / 
false 关闭效果：禁用计划反馈，执行计划固定不变

 /*+ OPT_PARAM(
'_optimizer_null_accepting_semijoin',
'false') */  --作用：支持空值半连接优化，处理 IN/EXISTS 子查询中的 NULL 值匹配。默认值：
true（开启）可取值：
true / 
false  关闭效果：禁用空值半连接优化，NULL 值不参与半连接匹配

/*+ OPT_PARAM(
'_optimizer_peek_user_binds',
'true') */  --作用：绑定变量窥探，优化器根据传入的绑定变量值生成执行计划。默认值：
true（开启）可取值：
true / 
false 开启效果：使用绑定变量实际值生成计划；关闭则使用通用默认值

 /*+ OPT_PARAM(
'_and_pruning_enabled',
'false') */ --设置为FALSE。默认值为TRUE，这有助于在某些特定的查询场景下，避免不必要的谓词修剪操作，确保查询结果的准确性和性能的稳定性。

 /*+ OPT_PARAM(
'_optimizer_partial_join_eval',
'false') */ --作用：部分连接评估优化，提前终止无效连接计算，提升执行效率。默认值：
true（开启）可取值：
true / 
false  关闭效果：完整执行所有连接计算，不做提前终止

 /*+ OPT_PARAM(
'_optimizer_unnest_corr_set_subq',
'false') */ --作用：相关子查询展开优化，将子查询展开为连接，提升执行速度。默认值：
true（开启）可取值：
true / 
false  关闭效果：禁止子查询展开，按原始子查询方式执行

 /*+ OPT_PARAM(
'_optimizer_unnest_scalar_sq',
'false') */ --建议：设置为FALSE。默认值为TRUE，这有助于在处理标量子查询时，根据实际情况选择更合适的执行计划，避免不必要的嵌套查询展开。

acs的三个参数：

 /*+ OPT_PARAM(
'_optimizer_adaptive_cursor_sharing',
'false') */

 /*+ OPT_PARAM(
'_optimizer_extended_cursor_sharing',
'none') */ 

 /*+ OPT_PARAM(
'_optimizer_extended_cursor_sharing_rel',
'none') */ --关闭自适应游标（它的引入是为了解决使用绑定变量与数据倾斜值，要产生多样性执行计划。因为绑定变量是为了共享执行计划，但是数据倾斜了，有的值要求走索引，有的值要求走全表，这样与使用绑定变量就产生了矛盾。）

 /*+ OPT_PARAM(
'_optimizer_mjc_enabled',
'false') */ --merge join Cartesia(合并笛卡尔链接) 默认值
true，--默认
true，表示打开笛卡尔乘积链接方式，
false表示关闭。生产建议关闭。

 /*+ OPT_PARAM(_optimizer_cartesian_enabled,
'false') */ --普通笛卡尔链接，将其设置为FALSE。默认值为TRUE，禁用笛卡尔积连接优化可以避免意外的、可能导致性能问题的查询执行计划，特别是在多表连接场景中，防止不必要的全表扫描组合。

 /*+ OPT_PARAM(
'_optimizer_cost_based_transformation',
'false') */  --查询转换相关，默认值为linear。参数值如下：
"exhaustive", 
"iterative", 
"linear", 
"on", 
"off"。这个参数和_optimizer_squ_bottomup参数同时作用，和案例中的filter关系密切。



/*+ opt_param(
'_b_tree_bitmap_plans', 
'false') */  --设置为FALSE。默认值为TRUE，根据具体的查询负载和索引结构，这种设置可能有助于减少不必要的位图转换操作，提高查询性能。