统计信息自动收集超时导致的估行不准 SQL 选择错索引

问题现象

发现业务 SQL 执行慢，对比历史执行计划发现索引有变化，优化器选择不优的索引，SQL 选择了索引 A 执行耗时久，手动选择索引耗时短效率很好，手动收集表的统计后优化器自动选对了索引。

问题原因

相同租户下的其他表收集统计信息超时影响客户 SQL 对应表统计信息没更新导致走错索引，手动收集统计信息后，优化器走对了索引。

关键信息

1. 业务感知到 SQL 耗时增加，通过 dbms_xplan 获取逻辑计划进行分析，发现存在有表估行不准确的现象。

   # 1. 执行 SQL。
   # 2. 获取 plan_id, svr_ip, svr_port,tenant_id。
   
   select plan_id, svr_ip, svr_port,tenant_id, query_sql from oceanbase.v$OB_SQL_AUDIT where query_sql like "%sql_query%";
   select dbms_xplan.display_cursor(264240,'all','10.XX.XX.87', 2882, 1002);
   

   

   自动收集收集的是统计信息缺失或者统计信息过期的表，按照增量收集的方式进行收集，也就是只收集数据变化的分区，而不用重新收集整个表的统计信息判断需要收集表的 DML 距离上一次收集期间。

   增删改的总量不超过当前表数据量的 10%，例如 INSERTS - LAST_INSERTS > 10% 或者 UPDATES - LAST_UPDATES > 10%。

   (insert + delete + update - last_insert - last_update - last_delete) / (insert - delete) > 10%
   

   

   -- 在 sys 租户中获取的所有租户信息，可以指定租户查询（推荐）。
   SELECT t_opt.tenant_id,
       t_opt.task_id,
       task_opt.start_time AS task_start_time,
       task_opt.end_time   AS task_end_time,
       d.database_name,
       t.table_name,
       t_opt.table_id,
       t_opt.ret_code,
       t_opt.start_time,
       t_opt.end_time,
       t_opt.memory_used,
       t_opt.stat_refresh_failed_list,
       t_opt.properties
   FROM   (
              SELECT task_id,
                     start_time,
                     end_time,
                     table_count
              FROM   oceanbase.__all_virtual_task_opt_stat_gather_history
              WHERE  type = 1
   --         AND    tenant_id = {tenant_id}
              AND    start_time > date_sub(Now(), interval 30 day)) task_opt
   JOIN   oceanbase.__all_virtual_table_opt_stat_gather_history t_opt
   JOIN   oceanbase.__all_virtual_table t
   JOIN   oceanbase.__all_virtual_database d
   WHERE  t_opt.ret_code != 0
   AND    task_opt.task_id = t_opt.task_id
   AND    t_opt.table_id = t.table_id
   AND    t.database_id = d.database_id
   AND    t_opt.table_id > 200000;
   

2. 查询统计信息收集任务发现有大量的表超时 -4012，目前自动收集统计信息的任务是租户下是串行的，相同租户其他表超时了，就可能导致这个表没有机会收集统计信息。

   

3. 对于超时的大表需要手动收集处理一下，或者设置一下相关大表的策略。

   a. 设置合适的默认收集并行度避免影响业务，建议并行度控制 8 个以内。

   -- Oracle 或者 MySQL 业务租户相同。
   CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'degree', '8');
   

   b. 调整相关的自动收集任务在业务低峰期进行, 举例假如业务低峰是凌晨 2 点。

   -- MySQL 业务租，比如现在是 2024-03-07，周四早上 11 点，需要调整为从周五凌晨 2 点开始。
   CALL DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'NEXT_DATE', '2024-03-11 02:00:00');
   
   -- Oracle 业务租户需要在对应的 sys 用户下执行, 比如现在是 2024-03-07, 周四早上 11 点, 需要调整为从周五凌晨 2 点开始。
   SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
   SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF';
   SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZR TZD';
   CALL DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'NEXT_DATE', '2024-03-11 02:00:00');
   

   c. 如果在表的统计信息收集过程中，表收集很慢，可以选择关闭直方图的收集，因为现阶段收集直方图是最费时的操作，同时很多场景目前也还用不上直方图。

   CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 
                             'table_name', 
                             'method_opt', 
                             'for all columns size 1');
   

   d. 如果上面的调整还是收集慢可以在业务低峰期可以考虑适当增加并行度，加速统计信息的收集。

问题的风险及影响

业务 SQL 执行慢。

适用版本

OceanBase 数据库 V4.2.x 版本。

解决方法及规避方式

• 解决方法：

  手动收集统计信息。

  CALL DBMS_STATS.GATHER_TABLE_STATS ('database_name', 'table_name',degree=>8,no_invalidate=>true);
  

• 规避方式:

  临时规避可以写 HINT 调整索引，收集手动统计信息可以让优化器选对索引。