Skip to main content

统计信息收集最佳实践

说明：目前 DBA 进阶教程的内容暂时对应的是 OceanBase 4.x 社区版本，本小节的架构部分不涉及商业版中 Oracle 模式租户下的内容。社区版和商业版的能力区别详见：官网链接。

为何要收集统计信息​

优化器生成和选择最优的执行计划，往往是基于代价来评估的，因此一个执行计划的代价评估的是否准确，对于优化器的计划生成至关重要。

在代价计算的过程中，优化器会根据代价模型和每个算子估算出的行数评估以及各执行计划的代价。在这之中，统计信息扮演了重要的角色。统计信息是否准确直接影响了算子的行数估算，进一步会影响到计划的代价计算和计划的选择。

因此保持统计信息的准确性对于生成好的执行计划至关重要。

统计信息的默认收集策略​

统计信息会在每个工作日（周一至周五）22:00 开始收集，最长收集 4 小时；或在每个非工作日（每周六周日）6:00 开始收集，最长收集 20 小时。这些统计信息收集的时间段我们称之为统计信息维护窗口。

在每个统计信息维护窗口内，优化器会为每个统计信息过期的表/分区重新收集统计信息。默认情况下统计信息过期的判定是一个表/分区上没有收集过统计信息，或表/分区上增删改的数据量超过了上次收集统计信息时行数的 10%。收集统计信息时，默认的收集策略如下：

配置名称含义默认策略
degree并行度1 并行度，单线程扫描。
method_opt列级统计信息的收集策略收集所有列的统计信息，并收集在where条件中出现过且存在数据倾斜列的直方图
granularity收集颗粒收集分区级的统计信息，并根据结果推导全局级的统计信息。非分区表的情况下直接收集全局级的统计信息。
estimate_percent采样比例不采样， 通过全表扫描手机统计信息。
block_sample是否采样块采样不使用块采样，使用行采样。

统计信息收集策略配置​

采用默认统计信息收集策略对与大多数的表都是适用的，但是在一些特定的场景下不一定能满足业务的场景，需要用户根据业务特点进行适当的配置。常见的场景及配置策略如下：

业务高峰与统计信息维护窗口重叠​

OceanBase 统计信息维护窗口的默认设置参考了 Oracle 的设置。但是对于很多国内业务来说，工作日的 22:00 依然是业务高峰时期，此时收集统计信息可能会出现统计信息收集的 SQL 与业务 SQL 抢占资源，影响业务 SQL 的运行。针对这个场景，可以调整统计信息维护窗口的开始时间，使其与业务高峰错开。

-- 例如现在是 2024-03-07, 周四早上 11 点。
-- 需要调整为从周五起，以后都是凌晨 2 点开始收集统计信息，大家可以这样进行设置:

call dbms_scheduler.set_attribute(
    'FRIDAY_WINDOW', 'NEXT_DATE', '2024-03-08 02:00:00');

call dbms_scheduler.set_attribute(
    'SATURDAY_WINDOW', 'NEXT_DATE', '2024-03-09 02:00:00');

call dbms_scheduler.set_attribute(
    'SUNDAY_WINDOW', 'NEXT_DATE', '2024-03-10 02:00:00');

call dbms_scheduler.set_attribute(
    'MONDAY_WINDOW', 'NEXT_DATE', '2024-03-11 02:00:00');

call dbms_scheduler.set_attribute(
    'TUESDAY_WINDOW', 'NEXT_DATE', '2024-03-12 02:00:00');

call dbms_scheduler.set_attribute(
    'WEDNESDAY_WINDOW', 'NEXT_DATE', '2024-03-13 02:00:00');

call dbms_scheduler.set_attribute(
    'THURSDAY_WINDOW', 'NEXT_DATE', '2024-03-14 02:00:00');

注意：以上命令仅适用于 OceanBase MySQL 模式的租户。

存在超大的业务表导致统计信息收集不完​

统计信息收集的默认策略下，需要单并发全表扫描待收集统计信息的表 / 分区。如果待收集统计信息的表 / 分区的数据量特别大，或者占用的磁盘空间特别多，会导致统计信息收集耗时过长影响后续其它表的统计信息收集，甚至当前表可能会收集超时。

当业务环境下存在数据量过亿，或者磁盘空间占用超过 20G 的表时，建议参考以下方案配置统计信息收集策略：

  1. 跳过大对象。

例如 MySQL 租户模式下默认会收集 longtext 列的统计信息，如果 longtext 列中保存的都是大对象，可能会导致收集特别慢。

如下示例中，第四个参数配置收集哪些列的统计信息，需要把除大对象外的所有列都加上。

call dbms_stats.set_table_prefs(
  'databse_name',
  'table_name',
  'method_opt',
  'for columns col1,col2,col3,... size auto');
  1. 提高并行度或配置块采样。

提高并行度可以让统计信息收集时使用更多的线程同时收集，通过消耗更多的资源达到快速收集的效果。

使用块采样，可以通过采样的方式减少统计信息收集时要处理的数据量，也达到到快速收集的效果。

两者都可以达到提升统计信息收集效率的，区别于并行的方式通过牺牲资源保证了统计信息的准确性，采用的方式通过牺牲统计信息的准确性保证了系统资源的可用性。

用户可根据自己的需要选择其中一种方式进行配置。

-- 配置统计信息收集时的并行度
call dbms_stats.set_table_prefs(
  'databse_name',
  'table_name',
  'degree',
  '4');
-- 配置开启块采样
call dbms_stats.set_table_prefs(
  'databse_name',
  'table_name',
  'block_sample',
  'True');

-- 配置采样比例，可根据表的数量级进行配置，通常情况下，采集千万级的数据即可充分反应一个表的数据特征
call dbms_stats.set_table_prefs(
  'databse_name',
  'table_name',
  'estimate_percent',
  '0.1');
  1. 对于分区表，可以考虑不收集全局统计信息。如下示例中，第四个参数配置收集什么级别的统计信息，一级分区表可配置只收集一级分区的统计信息，二级分区表可配置只收集二级分区的统计信息。需要注意的是，如果采用这种策略，需要删除全局统计信息（一级分区表和二级分区表时）和一级分区统计信息（仅二级分区表时）。
-- 一级分区表
call dbms_stats.set_table_prefs(
  'databse_name',
  'table_name',
  'granularity',
  'PARTITION');

-- 二级分区表
call dbms_stats.set_table_prefs(
  'databse_name',
  'table_name',
  'granularity',
  'SUBPARTITION');

跑批场景导完数据直接查询缺失统计信息​

默认情况下自动收集统计信息的任务是定时触发的，在此之外统计信息是不会更新的。

在 4.2.4 及 4.2.5 以上版本版本的 OBServer 中，提供了异步统计信息收集的能力，可以解决这个问题。

如果业务存在空表或小数据量的表导入大量数据后立即查询的场景（通常存在于跑批场景中），优化器会因为缺失统计信息或统计信息严重过期生成不优的计划。

针对这种场景，建议业务在批量导入数据后手动进行一次统计信息收集后再进行业务查询。如果导入的数据非常大，可以调整一下手动收集的策略，详细可以参考本文中的 “存在超大的业务表导致统计信息收集不完” 部分。

按日期预建分区的数据表查询当天导入的数据​

对于按日期预建分区的数据表，部分预建的分区收集统计信息时是不存在任何数据的，优化器看到的这些分区的统计信息为 0 行。

一旦某个分区当天导入了新的数据，并且业务逻辑会查询这一天新导入的数据，优化器就极容易因为统计信息严重过期而生成不优的计划。

针对这种场景，建议在当天数据导入后手动收集一次对应分区的统计信息。

在 4.2.4 及 4.2.5 以上版本版本的 OBServer 中，提供了异步统计信息收集的能力，可以解决这个问题。

Loading...