分布式数据库学习Note61：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
);

参数解释如下表所示。

method_opt

method_opt 主要采用下面的语法方式来设定：

method_opt:
  FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
| FOR COLUMNS [size clause] column [size_clause] [,column [size_clause]...]

size_clause:
  SIZE integer 
| SIZE REPEAT
| SIZE AUTO
| SIZE SKEWONLY

column:
   column_name
| (column_name [, column_name])

参数解释如下表所示。

此外，DBMS_STATS 包还提供了 GATHER_DATABASE_STATS_JOB_PROC 来收集整个数据库所有表的统计信息，所有收集策略都会采用默认配置的 Prefs，关于设置 Prefs 的详细信息，请参见 收集策略配置项（Prefs）管理功能。

PROCEDURE GATHER_DATABASE_STATS_JOB_PROC();

示例

DBMS_STATS 包收集统计信息的相关示例如下：

• 收集用户 user 的表 tbl1 的全局级别的统计信息，所有列的桶个数设定为 128。

  CALL dbms_stats.gather_table_stats('user', 'tbl1', granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE 128');
  

• 收集用户 user 的表 t_part1 的分区级别的统计信息，并行度为 64，只收集数据分布不均匀的列的直方图。

  CALL dbms_stats.gather_table_stats('user', 't_part1', degree=>'64', granularity=>'PARTITION', method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
  

• 收集用户 user 的表 t_subpart1 所有的统计信息，并行度为 128，只收集 50% 的数据，所有的列的直方图都由优化器内部决定。

  CALL dbms_stats.gather_table_stats('user', 't_subpart1', degree=>'128', estimate_percent=> '50', granularity=>'ALL', method_opt=>'FOR ALL COLUMNS SIZE AUTO');
  

表级和索引统计信息收集策略 GRANULARITY 的关联

默认情况下，在收集表统计信息的同时也会收集表上所有索引统计信息，由于索引表上的部分统计信息是可以通过基表的统计信息得到的，包括行数、平均行长（通过对应的几个列计算）等，因此可以加速统计信息的获取，也可以避免二次扫描全表数据。对于本地索引和全局索引两者在 GRANULARITY 上有所区别，如下表所示。

通过 ANALYZE 语句收集统计信息

ANALYZE 语法如下：

analyze_stmt:
ANALYZE TABLE table_name UPDATE HISTOGRAM ON column_name_list WITH INTNUM BUCKETS

当 Session 变量 enable_sql_extension 为 TRUE 的时候，可以使用 Oracle 模式的语法，即 OceanBase 数据库为 MySQL 模式提供的扩展语法。

analyze_stmt:
ANALYZE TABLE table_name [use_partition] analyze_statistics_clause

ANALYZE 语句统计信息收集示例如下：

• 收集表 tbl1 的统计信息，列的桶个数为 30 个。

  ANALYZE TABLE tbl1 UPDATE HISTOGRAM ON a, b, c, d WITH 30 BUCKETS;
  

• 在 MySQL 模式下使用 Oracle 模式的语法收集用户 test 的表 tbl1 的统计信息，所有列的桶个数设定为 128。

  ALTER SYSTEM SET ENABLE_SQL_EXTENSION = TRUE;
  ANALYZE TABLE tbl1 COMPUTE STATISTICS FOR ALL COLUMNS SIZE 128;