OceanBase统计信息收集和管理

最新推荐文章于 2025-12-05 12:37:18 发布

原创最新推荐文章于 2025-12-05 12:37:18 发布 · 1k 阅读

26 ·

CC 4.0 BY-SA版权

文章标签：

#oceanbase #数据库

OceanBase基础篇专栏收录该内容

18 篇文章

订阅专栏

OceanBase统计信息收集和管理

统计信息收集方式
优化器动态采样
统计信息锁定和解锁
统计信息相关视图
统计信息收集历史

统计信息收集方式

手动收集统计信息

OceanBase数据库优化器主要通过DBMS_STATS包和ANALYZE语句两种方式手动收集统计信息。推荐使用DBMS_STATS包收集统计信息。

OceanBase数据库优化器支持利用DBMS_STATS包收集表级、Schema级别的统计信息和索引统计信息，分别通过调用存储过程gather_table_stats、gather_schema_stats和gather_index_stats来完成。

PROCEDURE gather_table_stats (
  ownname            VARCHAR2,
  tabname            VARCHAR2,
  partname           VARCHAR2 DEFAULT NULL,
  estimate_percent   NUMBER DEFAULT AUTO_SAMPLE_SIZE,
  block_sample       BOOLEAN DEFAULT FALSE,
  method_opt         VARCHAR2 DEFAULT DEFAULT_METHOD_OPT,
  degree             NUMBER DEFAULT NULL,
  granularity        VARCHAR2 DEFAULT DEFAULT_GRANULARITY,
  cascade            BOOLEAN DEFAULT NULL,
  stattab            VARCHAR2 DEFAULT NULL,
  statid             VARCHAR2 DEFAULT NULL,
  statown            VARCHAR2 DEFAULT NULL,
  no_invalidate      BOOLEAN DEFAULT FALSE,
  stattype           VARCHAR2 DEFAULT 'DATA',
  force              BOOLEAN DEFAULT FALSE
);

PROCEDURE gather_schema_stats (
  ownname            VARCHAR2,
  estimate_percent   NUMBER DEFAULT AUTO_SAMPLE_SIZE,
  block_sample       BOOLEAN DEFAULT FALSE,
  method_opt         VARCHAR2 DEFAULT DEFAULT_METHOD_OPT,
  degree             NUMBER DEFAULT NULL,
  granularity        VARCHAR2 DEFAULT DEFAULT_GRANULARITY,
  cascade            BOOLEAN DEFAULT NULL,
  stattab            VARCHAR2 DEFAULT NULL,
  statid             VARCHAR2 DEFAULT NULL,
  statown            VARCHAR2 DEFAULT NULL,
  no_invalidate      BOOLEAN DEFAULT FALSE,
  stattype           VARCHAR2 DEFAULT 'DATA',
  force              BOOLEAN DEFAULT FALSE
);

PROCEDURE gather_index_stats (
  ownname            VARCHAR2,
  indname            VARCHAR2,
  partname           VARCHAR2 DEFAULT NULL,
  estimate_percent   NUMBER DEFAULT AUTO_SAMPLE_SIZE,
  stattab            VARCHAR2 DEFAULT NULL,
  statid             VARCHAR2 DEFAULT NULL,
  statown            VARCHAR2 DEFAULT NULL,
  degree             NUMBER DEFAULT NULL,
  granularity        VARCHAR2 DEFAULT DEFAULT_GRANULARITY,
  no_invalidate      BOOLEAN DEFAULT FALSE,
  force              BOOLEAN DEFAULT FALSE,
  tabname            VARCHAR2  DEFAULT NULL
);

其中：

degree：统计信息收集时的并行度，默认为NULL。
cascade：是否同时收集表的索引统计信息，默认为TRUE。
no_invalidate：该参数暂未实现，不可用。
granularity：统计信息收集时的分区粒度。
- GLOBAL：收集全局级别的统计信息。
- PARTITION：收集分区级别的统计信息。
- SUBPARTITION：收集子分区级别的统计信息。
- ALL：收集所有级别的统计信息（包括GLOBAL、PARTITION和SUBPARTITION级别）。
- AUTO：使用默认方式收集所有级别的统计信息（包括GLOBAL、PARTITION和SUBPARTITION级别）。
- DEFAULT：收集GLOBAL和PATITION级别的统计信息。
- GLOBAL AND PARTITION：收集GLOBAL和PATITION级别的统计信息。
- APPROX_GLOBAL AND PARTITION：收集分区级别的统计信息，并根据分区信息推导出全局级别的统计信息。
method_opt：设置列级别的统计信息收集方式。
- SIZE integer：指定收集列的直方图桶的个数，取值范围为[1, 2048]。
- REPEAT：仅仅只收集被收集过直方图的列的直方图。使用之前收集直方图设置的桶个数。
- AUTO：优化器根据列的使用情况，来决定是否收集列的直方图。直方图桶个数使用默认值254。
- SKEWONLY：仅仅只收集数据分布不均匀的列的直方图。直方图桶个数使用默认值254。

示例：

--收集表统计信息
CALL dbms_stats.gather_table_stats('user', 't1', granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE AUTO');

CALL dbms_stats.gather_table_stats('user', 't_part1', 
                                   degree=>'64', 
				   estimate_percent=> '50', 
				   granularity=>'ALL', 
				   method_opt=>'FOR ALL COLUMNS SIZE AUTO');
				   
--收集SCHEMA统计信息
CALL DBMS_STATS.GATHER_SCHEMA_STATS('test', degree=>'16', method_opt=>'for all columns size 1');

CALL DBMS_STATS.GATHER_SCHEMA_STATS('test', degree=>'16');

OceanBase数据库当前不支持对统计信息收集时间的预估。因此建议将ob_query_timeout设置得足够长，以便完成统计信息的收集。在收集schema级别的统计信息时，如果由于超时而中断，已经收集完毕的统计信息将被保留。同时，推荐用户在会话session级别调大ob_query_timeout变量，这样做仅影响当前会话，不会干扰其他操作。

自动收集统计信息

目前OceanBase数据库优化器通过基于DBMS_SCHEDULER系统包实现的MAINTENANCE WINDOW来实现每日的自动统计信息收集，从而保证统计信息的不断迭代更新。

OceanBase数据库定义MAINTENANCE WINDOW为周一到周日有7个自动统计信息收集任务，周一到周日的任务开始时间为22:00，最大收集时长为4小时。

📖查询MAINTENANCE WINDOW信息：

--Oracle模式：查询Job信息
select owner,job_name,job_action,repeat_interval,enabled,state,last_start_date,last_run_duration,next_run_date,max_run_duration,comments
from DBA_SCHEDULER_JOBS where job_name like '%_WINDOW' order by last_start_date;

--Oracle模式：查询Job信息的代理表
select tenant_id,job_name,last_date,this_date,next_date,interval#,enabled,state,failures,what,comments
from sys.ALL_VIRTUAL_TENANT_SCHEDULER_JOB_REAL_AGENT where job_name like '%_WINDOW' order by next_date;

--MySQL模式：查询Job信息
select owner,job_name,job_action,repeat_interval,enabled,state,last_start_date,last_run_duration,next_run_date,max_run_duration,comments
from oceanbase.DBA_SCHEDULER_JOBS where job_name like '%_WINDOW' order by last_start_date;

📖设置MAINTENANCE WINDOW属性：

--禁用某一天的统计信息自动收集
CALL DBMS_SCHEDULER.DISABLE('MONDAY_WINDOW');
CALL DBMS_SCHEDULER.DISABLE('TUESDAY_WINDOW');
CALL DBMS_SCHEDULER.DISABLE('WEDNESDAY_WINDOW');
CALL DBMS_SCHEDULER.DISABLE('THURSDAY_WINDOW');
CALL DBMS_SCHEDULER.DISABLE('FRIDAY_WINDOW');
CALL DBMS_SCHEDULER.DISABLE('SATURDAY_WINDOW');
CALL DBMS_SCHEDULER.DISABLE('SUNDAY_WINDOW');

--启用某一天的统计信息自动收集
CALL DBMS_SCHEDULER.ENABLE('MONDAY_WINDOW');
CALL DBMS_SCHEDULER.ENABLE('TUESDAY_WINDOW');
CALL DBMS_SCHEDULER.ENABLE('WEDNESDAY_WINDOW');
CALL DBMS_SCHEDULER.ENABLE('THURSDAY_WINDOW');
CALL DBMS_SCHEDULER.ENABLE('FRIDAY_WINDOW');
CALL DBMS_SCHEDULER.ENABLE('SATURDAY_WINDOW');
CALL DBMS_SCHEDULER.ENABLE('SUNDAY_WINDOW');

--设置统计信息任务下次开始的时间
CALL DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW', 'NEXT_DATE', to_timestamp('2025-10-24 20:00:00','YYYY-MM-DD HH24:MI:SS'));

OceanBase数据库中表的统计信息过期判断标准为，如果当前表增量的DML次数（上一次收集统计信息时DML次数到本次收集统计信息期间发生的增/删/改总次数）超过设置的阈值时就会过期。阈值的默认值是10%，阈值可以通过Prefs调整设置。

Oracle和MySQL两种模式下都支持通过相关视图和表查询DML STATS的信息。DML STATS用于记录一张表做增、删、改的次数，它决定了一张表是否需要再次收集统计信息。

Oracle模式：
- GV$DML_STATS、V$DML_STATS：查询虚表中记录的表的DML STATS。
- sys.ALL_VIRTUAL_MONITOR_MODIFIED_REAL_AGENT：查询代理表中记录表的DML STATS。
- ALL_TAB_MODIFICATIONS、DBA_TAB_MODIFICATIONS、USER_TAB_MODIFICATIONS：查询代理表中记录表的DML STATS。
MySQL模式：
- oceanbase.GV$DML_STATS、oceanbase.V$DML_STATS：查询虚表中记录的表的DML STATS。
- oceanbase.DBA_TAB_MODIFICATIONS：查询表中所有的DML STATS。

在线收集统计信息

在线统计信息收集指在执行INSERT插入时，数据库优化器就可以收集统计信息，不用手动调用系统包。在批量导入数据的过程中，可以实时收集统计信息，不需要额外的发起收集任务，减少了运维操作，提高统计信息收集性能。

OceanBase数据库使用GATHER_OPTIMIZER_STATISTICS Hint和系统变量_optimizer_gather_stats_on_load（默认开启）进行在线统计信息收集，同时也可以使用旁路导入功能的APPEND Hint实现在线统计信息收集。

🐘在线统计信息收集的主要触发场景如下：

CREATE TABLE ... AS SELECT：在系统变量_optimizer_gather_stats_on_load为True时，CTAS默认启动在线收集统计信息功能。如果需要禁用该功能，可以使用NO_GATHER_OPTIMIZER_STATISTICS Hint。

CREATE TABLE t2 AS SELECT /*+NO_GATHER_OPTIMIZER_STATISTICS*/* FROM t1;

INSERT INTO ... SELECT：在系统变量_optimizer_gather_stats_on_load为True时，使用GATHER_OPTIMIZER_STATISTICS Hint或者APPEND Hint启动在线收集统计信息功能。

INSERT /*+GATHER_OPTIMIZER_STATISTICS*/ INTO t2 SELECT * FROM t1;

并行DML（PDML）：通过ENABLE_PARALLEL_DML Hint或者通过ALTER SESSION ENABLE PARALLEL DML;设置系统并行模式的方式让插入操作以并行的方式进行。

INSERT /*+ENABLE_PARALLEL_DML PARALLEL(4)*/ INTO t2 SELECT * FROM t1;

优化器动态采样

OceanBase数据库从V4.2.0版本开始支持优化器动态采样功能，该功能在SQL运行时可以收集需要的统计信息，帮助优化器生成更好的执行计划，从而优化查询性能。

目前动态采样功能默认生效于当前用户的SQL，当前只支持基表的动态采样。在没有关闭动态采样功能时，以下场景会尝试在计划生成阶段使用动态采样：

没有统计信息的场景。
查询条件中存在复杂谓词的场景，例如c1 like '%test%'，无法用选择率计算公式进行行数估计。
用户指定使用动态采样的场景。

OceanBase数据库支持使用系统变量、查询Hint及系统配置项三种方式进行动态采样功能的控制，同时动态采样的采样集大小受限于并行度的控制。

通过系统变量optimizer_dynamic_sampling用于开启和关闭动态采样功能。

--开启 GLOBAL 级别的动态采样功能
SET GLOBAL optimizer_dynamic_sampling = 1;

--开启 SESSION 级别的动态采样功能 
SET SESSION optimizer_dynamic_sampling = 1;
SET optimizer_dynamic_sampling = 1;

--关闭 GLOBAL 级别的动态采样功能
SET GLOBAL optimizer_dynamic_sampling = 0;

--关闭 SESSION 级别的动态采样功能
SET SESSION optimizer_dynamic_sampling = 0;
SET optimizer_dynamic_sampling = 0;

通过DYNAMIC_SAMPLING Hint用于控制查询是否使用动态采样。

--对t1表开启动态采样：1表示开启，0表示关闭
SELECT /*+DYNAMIC_SAMPLING(t1 1)*/ c4 FROM t1 WHERE c1 > 1 AND c2 > 1 AND c3 > 1 GROUP BY c4;

通过系统配置项_optimizer_ads_time_limit来控制动态采样的时间上限。默认时间上限是10s，取值范围为[0, 300]。

--设置当前租户下的动态采样的时间上限为100s
ALTER SYSTEM SET _optimizer_ads_time_limit = 100;

统计信息锁定和解锁

统计信息的锁定功能主要用于锁死统计信息，防止统计信息更新，以保证统计信息的稳定性。

⭐️目前支持如下三种锁定统计信息的方式：

使用存储过程lock_table_stats锁定某个表的统计信息。
使用存储过程lock_partition_stats锁定某个分区的统计信息。
使用存储过程lock_schema_stats锁定某个Schema下所有表的统计信息。

PROCEDURE lock_table_stats (
    ownname          VARCHAR2,
    tabname          VARCHAR2,
    stattype         VARCHAR2 DEFAULT 'ALL'
  );

PROCEDURE lock_partition_stats (
    ownname          VARCHAR2,
    tabname          VARCHAR2,
    partname         VARCHAR2
  );

PROCEDURE lock_schema_stats(
    ownname          VARCHAR2,
    STATTYPE         VARCHAR2 DEFAULT 'ALL'
  );

⭐️目前支持如下三种解锁统计信息的方式：

使用存储过程unlock_table_stats解锁某个表的统计信息。
使用存储过程unlock_partition_stats解锁某个分区的统计信息。
使用存储过程unlock_schema_stats解锁某个 Schema 下所有表的统计信息。

示例：

--统计信息锁定
CALL dbms_stats.lock_table_stats('test', 't_part');
CALL dbms_stats.lock_partition_stats('test', 't_part', 'p0');

--统计信息解锁
CALL dbms_stats.unlock_table_stats('test', 't_part');
CALL dbms_stats.unlock_partition_stats('test', 't_part', 'p0');

统计信息相关视图

Oracle租户模式下，通过以下视图查询统计信息收集状态。

视图	描述
ALL_TAB_STATISTICS、DBA_TAB_STATISTICS、USER_TAB_STATISTICS	用于查询表级的统计信息。
ALL_TAB_COL_STATISTICS、DBA_TAB_COL_STATISTICS、USER_TAB_COL_STATISTICS	用于查询`GLOBAL`级别的列级统计信息。
ALL_PART_COL_STATISTICS、DBA_PART_COL_STATISTICS、USER_PART_COL_STATISTICS	用于查询`PARTITON`级别的列级统计信息
ALL_SUBPART_COL_STATISTICS、DBA_SUBPART_COL_STATISTICS、USER_SUBPART_COL_STATISTICS	用于查询`SUBPARTITON`级别的列级统计信息。
ALL_TAB_HISTOGRAMS、DBA_TAB_HISTOGRAMS、USER_TAB_HISTOGRAMS	用于查询`GLOBAL`级别的列级直方图统计信息。
ALL_PART_HISTOGRAMS、DBA_PART_HISTOGRAMS、USER_PART_HISTOGRAMS	用于查询`PARTITON`级别的列级直方图统计信息。
ALL_SUBPART_HISTOGRAMS、DBA_SUBPART_HISTOGRAMS、USER_SUBPART_HISTOGRAMS	用于查询`SUBPARTITON`级别的列级直方图统计信息。
ALL_IND_STATISTICS、DBA_IND_STATISTICS、USER_IND_STATISTICS	用于查询索引统计信息。
sys.ALL_VIRTUAL_TABLE_STAT_REAL_AGENT	用于查询表级的统计信息的代理表。
sys.ALL_VIRTUAL_COLUMN_STAT_REAL_AGENT	用于查询列级的基本统计信息的代理表。
sys.ALL_VIRTUAL_HISTOGRAM_STAT_REAL_AGENT	用于查询列级的直方图信息的代理表。

MySQL租户模式下，通过以下视图查询统计信息收集状态。

视图	描述
oceanbase.DBA_TAB_STATISTICS	用于查询表级的统计信息。
oceanbase.DBA_TAB_COL_STATISTICS	用于查询GLOBAL级别的列级统计信息。
oceanbase.DBA_PART_COL_STATISTICS	用于查询PARTITON级别的列级统计信息。
oceanbase.DBA_SUBPART_COL_STATISTICS	用于查询SUBPARTITON级别的列级统计信息。
oceanbase.DBA_TAB_HISTOGRAMS	用于查询GLOBAL级别的列级直方图统计信息。
oceanbase.DBA_PART_HISTOGRAMS	用于查询PARTITON级别的列级直方图统计信息。
oceanbase.DBA_SUBPART_HISTOGRAMS	用于查询SUBPARTITON级别的列级直方图统计信息。
oceanbase.DBA_IND_STATISTICS	用于查询索引统计信息。

查询表统计信息：

select owner,table_name,partition_name,partition_position,object_type,num_rows,avg_row_len,
last_analyzed,stattype_locked,stale_stats 
from oceanbase.DBA_TAB_STATISTICS where owner='scott' and table_name='t3';

其中：

last_analyzed：上次表分析的时间。
stattype_locked：统计信息锁定类型。NULL表示未锁定。
stale_stats：统计信息是否过期。

统计信息收集历史

查询租户级别的历史统计信息收集状态：

SELECT * FROM DBA_OB_TASK_OPT_STAT_GATHER_HISTORY WHERE TENANT_ID = 1006;

查询表级别的历史统计信息收集状态：

SELECT * FROM DBA_OB_TABLE_OPT_STAT_GATHER_HISTORY WHERE OWNER = 'scott';

References·
【1】https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001502819
【2】https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001503487