问题现象同一关键 SQL一厂平均执行 12ms三厂平均执行 700ms三厂数据量更小根因三厂数据库设置了隐含参数 _b_tree_bitmap_plansFALSE禁用了 BITMAP CONVERSION TO ROWIDS 访问路径优化器退化为全表扫描解决方案通过 SQL Profile 为三厂绑定含 BITMAP CONVERSION 的较优执行计划执行时间降至 1ms 以内1. 问题现象业务反馈某个关键 SQL 在一厂和三厂的执行时间差距较大。三厂数据量更小理论上应该更快但实际表现相反。1.1 执行时间对比工厂平均执行时间执行计划一厂~12msBITMAP CONVERSION TO ROWIDS索引访问三厂~700msFULL TABLE SCAN全表扫描1.2 执行计划差异一厂执行计划三厂执行计划关键差异访问路径就是一厂走的BITMAP 三厂走的全表2. 根因分析2.1 关键参数三厂为新建工厂数据库实施参数标准中配置了隐含参数_b_tree_bitmap_plans FALSE。该参数在 OLTP 最佳实践中建议设为 FALSE但在本案例中恰好阻止了优化器选择最优执行计划。参数说明_b_tree_bitmap_plans 控制优化器是否考虑 BITMAP CONVERSION TO ROWIDS / FROM ROWIDS 以及 BITMAP AND/OR/MINUS 等执行计划。默认为TRUE允许设为FALSE后所有 B-tree 索引转 Bitmap 的访问路径均被禁用。2.2 影响链路一厂执行计划访问路径h : SYS.SQLPROF_ATTR( q[BEGIN_OUTLINE_DATA], q[IGNORE_OPTIM_EMBEDDED_HINTS], q[OPTIMIZER_FEATURES_ENABLE(19.1.0)], q[DB_VERSION(19.1.0)], q[OPT_PARAM(_optimizer_extended_cursor_sharing none)], q[OPT_PARAM(_optimizer_extended_cursor_sharing_rel none)], q[OPT_PARAM(_optimizer_adaptive_cursor_sharing false)], q[OPT_PARAM(_optimizer_use_feedback false)], q[OPT_PARAM(_optimizer_gather_feedback false)], q[ALL_ROWS], q[OUTLINE_LEAF(SEL$1)], q[OUTLINE_LEAF(SEL$2)], q[NO_ACCESS(SEL$2 from$_subquery$_002SEL$2)], q[BITMAP_TREE(SEL$1 LXSEL$1 OR(1 1 (TEST.SN) 2 (TEST.SUBSN) 3 (TEST.XPSN)))], q[BATCH_TABLE_ACCESS_BY_ROWID(SEL$1 LXSEL$1)], q[END_OUTLINE_DATA]); :signature : DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt); :signaturef : DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);三厂执行计划访问路径h : SYS.SQLPROF_ATTR( q[BEGIN_OUTLINE_DATA], q[IGNORE_OPTIM_EMBEDDED_HINTS], q[OPTIMIZER_FEATURES_ENABLE(19.1.0)], q[DB_VERSION(19.1.0)],q[OPT_PARAM(_b_tree_bitmap_plans false)], --该隐含参数阻止了优化器选择BITMAPq[OPT_PARAM(_optim_peek_user_binds false)], q[OPT_PARAM(_bloom_filter_enabled false)], q[OPT_PARAM(_optimizer_extended_cursor_sharing none)], q[OPT_PARAM(_optimizer_outer_to_anti_enabled false)], q[OPT_PARAM(_bloom_pruning_enabled false)], q[OPT_PARAM(_optimizer_extended_cursor_sharing_rel none)], q[OPT_PARAM(_optimizer_adaptive_cursor_sharing false)], q[OPT_PARAM(_and_pruning_enabled false)], q[OPT_PARAM(_optimizer_use_feedback false)], q[OPT_PARAM(_px_adaptive_dist_method off)], q[OPT_PARAM(_optimizer_strans_adaptive_pruning false)], q[OPT_PARAM(_optimizer_null_accepting_semijoin false)], q[OPT_PARAM(_optimizer_gather_feedback false)], q[OPT_PARAM(_optimizer_reduce_groupby_key false)], q[OPT_PARAM(_optimizer_nlj_hj_adaptive_join false)], q[ALL_ROWS], q[OUTLINE_LEAF(SEL$1)], q[OUTLINE_LEAF(SEL$2)], q[NO_ACCESS(SEL$2 from$_subquery$_002SEL$2)], q[FULL(SEL$1 LXSEL$1)], q[END_OUTLINE_DATA]); :signature : DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt); :signaturef : DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);2.3 为何 OLTP 建议设为 FALSE该参数设为 FALSE 的初衷是避免 OLTP 场景下产生不合适的 Bitmap 转换计划。当 SQL 包含多个 B-tree 索引条件尤其是星型转换、多索引 AND/OR,本案例sql为多个or查询时优化器可能生成次优的 BITMAP CONVERSION 计划。此外19c 中存在已知 BugBug 30102774— ORA-7445 [kkosbn] Error With SQL With Bitmap Plans设为 FALSE 可作为 workaround 规避该类 Bug。但对于需要使用 BITMAP CONVERSION 的特定 SQL该设置会产生负面影响。3. 解决方案3.1 方案选择最简单且影响最小的方式是使用SQL Profile为该 SQL 绑定含 BITMAP CONVERSION 的较优执行计划无需修改全局参数不影响其他 SQL 的执行计划。3.2 一厂 SQL Profile Outline较优计划从一厂获取该 SQL 的较优执行计划 Outline通过 SQL Profile 绑定到三厂。关键 Hint 如下BITMAP_TREE(SEL$1 LXSEL$1 OR(1 1 (TEST.SN) 2 (TEST.SUBSN) 3 (TEST.XPSN)))BATCH_TABLE_ACCESS_BY_ROWID(SEL$1 LXSEL$1)一厂 Outline 中包含的优化器参数绑定OPT_PARAM(_optimizer_extended_cursor_sharing none)OPT_PARAM(_optimizer_extended_cursor_sharing_rel none)OPT_PARAM(_optimizer_adaptive_cursor_sharing false)OPT_PARAM(_optimizer_use_feedback false)OPT_PARAM(_optimizer_gather_feedback false)3.3 三厂当前 SQL Profile Outline较差计划三厂执行计划 Outline 中包含的关键差异OPT_PARAM(_b_tree_bitmap_plans false)— 直接导致无法使用 BITMAP CONVERSIONFULL(SEL$1 LXSEL$1)— 全表扫描替换了 BITMAP_TREE此外还包含以下参数绑定OPT_PARAM(_optim_peek_user_binds false)OPT_PARAM(_bloom_filter_enabled false)OPT_PARAM(_bloom_pruning_enabled false)OPT_PARAM(_and_pruning_enabled false)OPT_PARAM(_optimizer_outer_to_anti_enabled false)OPT_PARAM(_optimizer_null_accepting_semijoin false)OPT_PARAM(_optimizer_reduce_groupby_key false)OPT_PARAM(_optimizer_nlj_hj_adaptive_join false)OPT_PARAM(_px_adaptive_dist_method off)OPT_PARAM(_optimizer_strans_adaptive_pruning false)3.4 效果验证阶段执行计划平均执行时间优化前三厂原始FULL TABLE SCAN~700ms一厂参考值BITMAP CONVERSION TO ROWIDS~12ms优化后绑定 SQL ProfileBITMAP CONVERSION TO ROWIDS小于 1ms绑定 SQL Profile 后三厂该 SQL 的执行时间从 700ms 降至 1ms 以内性能提升约700 倍。4. _b_tree_bitmap_plans 参数详解4.1 控制范围该隐藏参数控制优化器是否考虑以下执行计划BITMAP CONVERSION TO ROWIDSBITMAP CONVERSION FROM ROWIDSBITMAP AND / OR / MINUS这类 B-tree 索引转 Bitmap 再运算的执行计划。4.2 参数值说明参数值行为TRUE默认允许优化器使用 BITMAP CONVERSION 相关计划FALSE禁止所有 BITMAP CONVERSION 计划不再出现 BITMAP CONVERSION TO ROWIDS 等路径4.3 典型执行计划场景当 SQL 包含多个 B-tree 索引条件尤其是星型转换、多索引 AND/OR时优化器可能生成如下计划BITMAP CONVERSION TO ROWIDSBITMAP ANDBITMAP CONVERSION FROM ROWIDS - INDEX RANGE SCANBITMAP CONVERSION FROM ROWIDS - INDEX RANGE SCAN将 _b_tree_bitmap_plans 设为 FALSE 后上述计划全部被禁用。4.4 查看与修改查看当前值select x.ksppinm name, y.ksppstvl value, y.ksppstdf isdefault, decode(bitand(y.ksppstvf, 7), 1, MODIFIED, 4, SYSTEM_MOD, FALSE) ismod, decode(bitand(y.ksppstvf, 2), 2, TRUE, FALSE) isadj from sys.x$ksppi x, sys.x$ksppcv y where x.inst_id userenv(Instance) and y.inst_id userenv(Instance) and x.indx y.indx and x.ksppinm like %b_tree_bitmap% order by translate(x.ksppinm, _, );会话级测试ALTER SESSION SET _b_tree_bitmap_plans FALSE;Hint方式禁用/启用SELECT /* OPT_PARAM(_b_tree_bitmap_plans, TRUE) */ SELECT /* OPT_PARAM(_b_tree_bitmap_plans, FALSE) */实例级修改需重启ALTER SYSTEM SET _b_tree_bitmap_plans FALSE SCOPESPFILE;5. 经验总结1. 参数标准不能一刀切OLTP 最佳实践中建议禁用 _b_tree_bitmap_plans 以规避已知 Bug 和次优计划但需评估业务 SQL 是否依赖 BITMAP CONVERSION 路径。新建工厂实施参数标准时建议先用一厂的执行计划基线做回归测试。2. SQL Profile 是精准调优利器当全局参数调整会影响其他 SQL 时SQL Profile 可以针对单条 SQL 绑定最优执行计划影响范围最小。适合「大部分 SQL 正常个别 SQL 受影响」的场景。3. 隐含参数变更需评估影响面修改隐含参数前建议在测试环境对关键 SQL 做执行计划对比explain plan / SQL Tuning Advisor确认不会产生回归。
问题现象同一关键 SQL一厂平均执行 12ms三厂平均执行 700ms三厂数据量更小根因三厂数据库设置了隐含参数 _b_tree_bitmap_plansFALSE禁用了 BITMAP CONVERSION TO ROWIDS 访问路径优化器退化为全表扫描解决方案通过 SQL Profile 为三厂绑定含 BITMAP CONVERSION 的较优执行计划执行时间降至 1ms 以内1. 问题现象业务反馈某个关键 SQL 在一厂和三厂的执行时间差距较大。三厂数据量更小理论上应该更快但实际表现相反。1.1 执行时间对比工厂平均执行时间执行计划一厂~12msBITMAP CONVERSION TO ROWIDS索引访问三厂~700msFULL TABLE SCAN全表扫描1.2 执行计划差异一厂执行计划三厂执行计划关键差异访问路径就是一厂走的BITMAP 三厂走的全表2. 根因分析2.1 关键参数三厂为新建工厂数据库实施参数标准中配置了隐含参数_b_tree_bitmap_plans FALSE。该参数在 OLTP 最佳实践中建议设为 FALSE但在本案例中恰好阻止了优化器选择最优执行计划。参数说明_b_tree_bitmap_plans 控制优化器是否考虑 BITMAP CONVERSION TO ROWIDS / FROM ROWIDS 以及 BITMAP AND/OR/MINUS 等执行计划。默认为TRUE允许设为FALSE后所有 B-tree 索引转 Bitmap 的访问路径均被禁用。2.2 影响链路一厂执行计划访问路径h : SYS.SQLPROF_ATTR( q[BEGIN_OUTLINE_DATA], q[IGNORE_OPTIM_EMBEDDED_HINTS], q[OPTIMIZER_FEATURES_ENABLE(19.1.0)], q[DB_VERSION(19.1.0)], q[OPT_PARAM(_optimizer_extended_cursor_sharing none)], q[OPT_PARAM(_optimizer_extended_cursor_sharing_rel none)], q[OPT_PARAM(_optimizer_adaptive_cursor_sharing false)], q[OPT_PARAM(_optimizer_use_feedback false)], q[OPT_PARAM(_optimizer_gather_feedback false)], q[ALL_ROWS], q[OUTLINE_LEAF(SEL$1)], q[OUTLINE_LEAF(SEL$2)], q[NO_ACCESS(SEL$2 from$_subquery$_002SEL$2)], q[BITMAP_TREE(SEL$1 LXSEL$1 OR(1 1 (TEST.SN) 2 (TEST.SUBSN) 3 (TEST.XPSN)))], q[BATCH_TABLE_ACCESS_BY_ROWID(SEL$1 LXSEL$1)], q[END_OUTLINE_DATA]); :signature : DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt); :signaturef : DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);三厂执行计划访问路径h : SYS.SQLPROF_ATTR( q[BEGIN_OUTLINE_DATA], q[IGNORE_OPTIM_EMBEDDED_HINTS], q[OPTIMIZER_FEATURES_ENABLE(19.1.0)], q[DB_VERSION(19.1.0)],q[OPT_PARAM(_b_tree_bitmap_plans false)], --该隐含参数阻止了优化器选择BITMAPq[OPT_PARAM(_optim_peek_user_binds false)], q[OPT_PARAM(_bloom_filter_enabled false)], q[OPT_PARAM(_optimizer_extended_cursor_sharing none)], q[OPT_PARAM(_optimizer_outer_to_anti_enabled false)], q[OPT_PARAM(_bloom_pruning_enabled false)], q[OPT_PARAM(_optimizer_extended_cursor_sharing_rel none)], q[OPT_PARAM(_optimizer_adaptive_cursor_sharing false)], q[OPT_PARAM(_and_pruning_enabled false)], q[OPT_PARAM(_optimizer_use_feedback false)], q[OPT_PARAM(_px_adaptive_dist_method off)], q[OPT_PARAM(_optimizer_strans_adaptive_pruning false)], q[OPT_PARAM(_optimizer_null_accepting_semijoin false)], q[OPT_PARAM(_optimizer_gather_feedback false)], q[OPT_PARAM(_optimizer_reduce_groupby_key false)], q[OPT_PARAM(_optimizer_nlj_hj_adaptive_join false)], q[ALL_ROWS], q[OUTLINE_LEAF(SEL$1)], q[OUTLINE_LEAF(SEL$2)], q[NO_ACCESS(SEL$2 from$_subquery$_002SEL$2)], q[FULL(SEL$1 LXSEL$1)], q[END_OUTLINE_DATA]); :signature : DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt); :signaturef : DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);2.3 为何 OLTP 建议设为 FALSE该参数设为 FALSE 的初衷是避免 OLTP 场景下产生不合适的 Bitmap 转换计划。当 SQL 包含多个 B-tree 索引条件尤其是星型转换、多索引 AND/OR,本案例sql为多个or查询时优化器可能生成次优的 BITMAP CONVERSION 计划。此外19c 中存在已知 BugBug 30102774— ORA-7445 [kkosbn] Error With SQL With Bitmap Plans设为 FALSE 可作为 workaround 规避该类 Bug。但对于需要使用 BITMAP CONVERSION 的特定 SQL该设置会产生负面影响。3. 解决方案3.1 方案选择最简单且影响最小的方式是使用SQL Profile为该 SQL 绑定含 BITMAP CONVERSION 的较优执行计划无需修改全局参数不影响其他 SQL 的执行计划。3.2 一厂 SQL Profile Outline较优计划从一厂获取该 SQL 的较优执行计划 Outline通过 SQL Profile 绑定到三厂。关键 Hint 如下BITMAP_TREE(SEL$1 LXSEL$1 OR(1 1 (TEST.SN) 2 (TEST.SUBSN) 3 (TEST.XPSN)))BATCH_TABLE_ACCESS_BY_ROWID(SEL$1 LXSEL$1)一厂 Outline 中包含的优化器参数绑定OPT_PARAM(_optimizer_extended_cursor_sharing none)OPT_PARAM(_optimizer_extended_cursor_sharing_rel none)OPT_PARAM(_optimizer_adaptive_cursor_sharing false)OPT_PARAM(_optimizer_use_feedback false)OPT_PARAM(_optimizer_gather_feedback false)3.3 三厂当前 SQL Profile Outline较差计划三厂执行计划 Outline 中包含的关键差异OPT_PARAM(_b_tree_bitmap_plans false)— 直接导致无法使用 BITMAP CONVERSIONFULL(SEL$1 LXSEL$1)— 全表扫描替换了 BITMAP_TREE此外还包含以下参数绑定OPT_PARAM(_optim_peek_user_binds false)OPT_PARAM(_bloom_filter_enabled false)OPT_PARAM(_bloom_pruning_enabled false)OPT_PARAM(_and_pruning_enabled false)OPT_PARAM(_optimizer_outer_to_anti_enabled false)OPT_PARAM(_optimizer_null_accepting_semijoin false)OPT_PARAM(_optimizer_reduce_groupby_key false)OPT_PARAM(_optimizer_nlj_hj_adaptive_join false)OPT_PARAM(_px_adaptive_dist_method off)OPT_PARAM(_optimizer_strans_adaptive_pruning false)3.4 效果验证阶段执行计划平均执行时间优化前三厂原始FULL TABLE SCAN~700ms一厂参考值BITMAP CONVERSION TO ROWIDS~12ms优化后绑定 SQL ProfileBITMAP CONVERSION TO ROWIDS小于 1ms绑定 SQL Profile 后三厂该 SQL 的执行时间从 700ms 降至 1ms 以内性能提升约700 倍。4. _b_tree_bitmap_plans 参数详解4.1 控制范围该隐藏参数控制优化器是否考虑以下执行计划BITMAP CONVERSION TO ROWIDSBITMAP CONVERSION FROM ROWIDSBITMAP AND / OR / MINUS这类 B-tree 索引转 Bitmap 再运算的执行计划。4.2 参数值说明参数值行为TRUE默认允许优化器使用 BITMAP CONVERSION 相关计划FALSE禁止所有 BITMAP CONVERSION 计划不再出现 BITMAP CONVERSION TO ROWIDS 等路径4.3 典型执行计划场景当 SQL 包含多个 B-tree 索引条件尤其是星型转换、多索引 AND/OR时优化器可能生成如下计划BITMAP CONVERSION TO ROWIDSBITMAP ANDBITMAP CONVERSION FROM ROWIDS - INDEX RANGE SCANBITMAP CONVERSION FROM ROWIDS - INDEX RANGE SCAN将 _b_tree_bitmap_plans 设为 FALSE 后上述计划全部被禁用。4.4 查看与修改查看当前值select x.ksppinm name, y.ksppstvl value, y.ksppstdf isdefault, decode(bitand(y.ksppstvf, 7), 1, MODIFIED, 4, SYSTEM_MOD, FALSE) ismod, decode(bitand(y.ksppstvf, 2), 2, TRUE, FALSE) isadj from sys.x$ksppi x, sys.x$ksppcv y where x.inst_id userenv(Instance) and y.inst_id userenv(Instance) and x.indx y.indx and x.ksppinm like %b_tree_bitmap% order by translate(x.ksppinm, _, );会话级测试ALTER SESSION SET _b_tree_bitmap_plans FALSE;Hint方式禁用/启用SELECT /* OPT_PARAM(_b_tree_bitmap_plans, TRUE) */ SELECT /* OPT_PARAM(_b_tree_bitmap_plans, FALSE) */实例级修改需重启ALTER SYSTEM SET _b_tree_bitmap_plans FALSE SCOPESPFILE;5. 经验总结1. 参数标准不能一刀切OLTP 最佳实践中建议禁用 _b_tree_bitmap_plans 以规避已知 Bug 和次优计划但需评估业务 SQL 是否依赖 BITMAP CONVERSION 路径。新建工厂实施参数标准时建议先用一厂的执行计划基线做回归测试。2. SQL Profile 是精准调优利器当全局参数调整会影响其他 SQL 时SQL Profile 可以针对单条 SQL 绑定最优执行计划影响范围最小。适合「大部分 SQL 正常个别 SQL 受影响」的场景。3. 隐含参数变更需评估影响面修改隐含参数前建议在测试环境对关键 SQL 做执行计划对比explain plan / SQL Tuning Advisor确认不会产生回归。
建模、参数整定与调试全攻略)
