执行计划跳变排查思路整理

执行计划跳变排查思路

为什么会执行计划跳变

图中，针对同一 SQL_ID， SQL_1 对应生成的执行计划为 Plan_1，SQL_2～SQL_100 对应生成的执行计划为 Plan_2。当 plan cache 缓存了 Plan_1 时，就会导致执行计划从 Plan_2 跳变到 Plan_1，SQL_2～SQL_100 执行计划非最优的执行计划，性能大幅度降低。

如何找到跳变的 SQL

通常在执行环境中，我们能通过 SQL 诊断拿到OB的 plan_hash_1 和 plan_hash_2。通过查询 GV$OB_PLAN_CACHE_PLAN_STAT 视图，获取第一次生成执行计划时的 SQL 语句如下。

select query_sql from oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT where plan_hash = 'plan_hash_1';

select query_sql from oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT where plan_hash = 'plan_hash_2';

如何排查并解决执行计划跳变问题

发生执行计划跳变，大致原因是：统计信息失效、大小账号问题、buffer 表、优化器算子代价评估不准。

统计信息过期

确认问题

统计信息是优化器生成最优执行计划的关键，是表和列信息的数据集合。 OceanBase 数据库每天都会定时采集统计信息，当分区表的某些分区的增/删/改的比例超过了 10%，也会重新收集这些分区的统计信息。 所以说，如果统计信息过期，可能会导致优化器估行不准，进而导致执行计划偏差。

• 确认表的统计信息是否失效。

  select distinct table_name from oceanbase.DBA_OB_TABLE_STAT_STALE_INFO where IS_STALE = 'YES' and database_name != 'oceanbase';
  

  

  通常失效的原因是这些表的插入/更新量比较大，可以通过 DBA_TAB_MODIFICATIONS 来查询从上次收集信息以来的修改信息。

  select v1.table_name, sum(INSERTS) from  oceanbase.DBA_TAB_MODIFICATIONS v1 , (select distinct table_name as table_name from oceanbase.DBA_OB_TABLE_STAT_STALE_INFO where IS_STALE = 'YES' and database != 'oceanbase') v2 where v1.table_name = v2.table_name group by table_name;
  

  

• 查看某张表自动统计任务是否成功。

  执行下面的 SQL 语句，如果近期有 status=failed 的任务，则明确是由于自动收集任务失败导致的。

  ## 查询某张表统计信息收集是否成功
  select * from oceanbase.DBA_OB_TABLE_OPT_STAT_GATHER_HISTORY WHERE STATUS = 'FAILED' and table_name = 'table_name' order by start_time desc;
  

  

解决方案

• 手动进行统计信息采集&修改自动收集策略。

  使用 GATHER_TABLE_STATS 和 GATHER_SCHEMA_STATS 分别可以采集表、库级别的统计信息。

  call dbms_stats.gather_table_stats('TEST', 'T1', granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE 128');
  

  自动收集策略建议找研发进行评估。

• 临时绑定执行计划。

• 查询 SQL 带上 hint。

  select /*+ index(table_name index_name)*/ xxx
  

• 开启 SPM。

  set global optimizer_use_sql_plan_baselines = true;
  set global optimizer_capture_sql_plan_baselines = true;
  

大小账号

确认问题

分别对 SQL_1 和 SQL_2 的查询条件做 count 查询，观察是否数据集有数量级的差异。如果存在，则是大小账号问题。

create table t1(c1 int, c2 int)
create index idx_1 on t1(c1);
create index idx_2 on t2(c2);

SQL1: select * from t1 where c1 = 1 and c2 > 100;
SQL2: select * from t1 where c1 = 3 and c2 > 100000000;

select count(*) from t1 where c1 = 3; -- 结果是20
select count(*) from t2 where c2 > 100000000 -- 结果是0

由于 SQL2 走 c2 索引只需要扫描 0 行，所以执行计划会跳变到 idx_2 上，而对于大多数向 SQL1 的 SQL，其实会 query_range 大量的数据，导致 RT 性能下降。

解决方案

• 临时绑定执行计划。

• 查询 SQL 带上 hint。

  select /*+ index(table_name index_name)*/ xxx
  

• 开启 SPM。

  set global optimizer_use_sql_plan_baselines = true;
  set global optimizer_capture_sql_plan_baselines = true;
  

buffer 表

确认问题

buffer 表是指频繁插入删除表，由于 LSM-Tree 架构下被删除的数据是标记来删除，只有在每日合并后才会物理生效。这导致了，业务实际数据比较少，但范围查询时会扫描大量的数据行。 通过下面这张表查询，来查看自上次统计信息收集任务后的插入、删除、更新的量级。

select sum(INSERTS), sum(DELETES), sum(UPDATES)  from oceanbase.DBA_TAB_MODIFICATIONS where table_name = 'picking_order_detail';

输出结果如下：

+--------------+--------------+--------------+
| sum(INSERTS) | sum(DELETES) | sum(UPDATES) |
+--------------+--------------+--------------+
|   3058142860 |   1451032567 |      0       |
+--------------+--------------+--------------+
1 row in set (0.045 sec)

解决方案

临时绑定执行计划。

• 查询 SQL 带上 hint。

  select /*+ index(table_name index_name)*/ xxx
  

• 开启 SPM。

  set global optimizer_use_sql_plan_baselines = true;
  set global optimizer_capture_sql_plan_baselines = true;
  

优化器代价预估不准

确认问题

当前 order by + limit 的流式计划代价评估不准，可能会导致走到非流式执行计划。

解决方案

临时绑定执行计划。

• 查询 SQL 带上 hint。

  select /*+ index(table_name index_name)*/ xxx
  

• 开启 SPM。

  set global optimizer_use_sql_plan_baselines = true;
  set global optimizer_capture_sql_plan_baselines = true;
  

适用版本

OceanBase 数据库 V4.x 版本。