手动收集统计信息 - OceanBase 数据库 V4.2.0

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

说明

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

通过 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
);

参数解释如下表所示。

参数	解释
ownname	Oracle 模式：用户名。如果设置为 `NULL`，会默认使用当前登录用户名。 MySQL 模式：表所在数据库名称。
tabname	表名。
indname	索引名。
partname	分区名。默认为 `NULL`。
estimate_percent	指定使用多少比例的数据计算其分布特征，范围为 [0.000001,100]。如果指定为 `NULL`，则使用所有数据。默认是 `AUTO_SAMPLE_SIZE`，即由优化器内部决定使用多少比例的数据。
block_sample	是否使用块采样代替行采样，默认为 `FALSE`。
method_opt	设置列级别的统计信息收集方式。详细信息，请参见 method_opt。
degree	统计信息收集时的并行度，默认为 `NULL`。
granularity	统计信息收集时的分区粒度。目前支持以下几种设置： `'GLOBAL'`：收集全局级别的统计信息。 `'PARTITION'`：收集分区级别的统计信息。 `'SUBPARTITION'`：收集子分区级别的统计信息。 `'ALL'` ：收集所有级别的统计信息（包括 `GLOBAL`、`PARTITION` 和 `SUBPARTITION` 级别）。 `'AUTO'` ：使用默认方式收集所有级别的统计信息（包括 `GLOBAL`、`PARTITION` 和 `SUBPARTITION` 级别）。不设置 `granularity` 时，`'AUTO'` 为默认值。 `'DEFAULT'`：收集 `GLOBAL` 和 `PARTITION` 级别的统计信息。 `'GLOBAL AND PARTITION'`：收集 `GLOBAL` 和 `PARTITION` 级别的统计信息。 `'APPROX_GLOBAL AND PARTITION'`：收集分区级别的统计信息，并根据分区信息推导出全局级别的统计信息。
cascade	是否同时收集表的索引统计信息，默认为 `TRUE`。
stattab	该参数暂未实现，不可用。
statid	该参数暂未实现，不可用。
statown	该参数暂未实现，不可用。
no_invalidate	该参数暂未实现，不可用。
stattype	该参数暂未实现，不可用。
force	是否强制收集信息，并忽略锁的状态，默认为 `FALSE`。该参数只能在 PL 的 `BEGIN .... END` 里面执行。

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])

参数解释如下表所示。

参数	解释
SIZE integer	指定收集列的直方图桶的个数，取值范围为 [1, 2048]。
REPEAT	仅仅只收集被收集过直方图的列的直方图。使用之前收集直方图设置的桶个数。
AUTO	OceanBase 数据库优化器根据列的使用情况，来决定是否收集列的直方图。直方图桶个数使用默认值 254。
SKEWONLY	仅仅只收集数据分布不均匀的列的直方图。直方图桶个数使用默认值 254。

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

PROCEDURE GATHER_DATABASE_STATS_JOB_PROC();

示例

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

收集用户 user 的表 tbl1 的全局级别的统计信息。

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

收集用户 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 上有所区别，如下表所示。

指定分区	GRANULARITY	全局索引	本地索引
否	ALL	GLOBAL 级别	ALL
否	AUTO	GLOBAL 级别	AUTO
否	DEFAULT	GLOBAL 级别	DEFAULT
否	GLOBAL AND PARTITION	GLOBAL 级别	GLOBAL AND PARTITION
否	APPROX_GLOBAL AND PARTITION	GLOBAL 级别	APPROX_GLOBAL AND PARTITION
否	GLOBAL	GLOBAL 级别	GLOBAL
否	PARTITION	GLOBAL 级别	PARTITION
否	SUBPARTITION	不收集	SUBPARTITION
是	ALL	GLOBAL 级别	ALL
是	AUTO	GLOBAL 级别	AUTO
是	DEFAULT	GLOBAL 级别	DEFAULT
是	GLOBAL AND PARTITION	GLOBAL 级别	GLOBAL AND PARTITION
是	APPROX_GLOBAL AND PARTITION	不收集	APPROX_GLOBAL AND PARTITION
是	GLOBAL	GLOBAL 级别	GLOBAL
是	PARTITION	不收集	PARTITION
是	SUBPARTITION	不收集	SUBPARTITION

通过 ANALYZE 语句收集统计信息

在 Oracle 模式和 MySQL 模式下，OceanBase 数据库支持使用 ANALYZE 语句进行统计信息的收集。

Oracle 模式下的 ANALYZE 语法

Oracle 模式下的 ANALYZE 语法如下：

analyze_stmt:
ANALYZE TABLE table_name [use_partition] analyze_statistics_clause

use_partition:
  PARTITION (parition_name [,partition_name...])
| SUBPARTITION(subpartition_name [,subpartition_name...])

analyze_statistics_clause:
  COMPUTE STATISTICS [analyze_for_clause]
| ESTIMATE STATISTICS [analyze_for_clause] [SAMPLE INTNUM {ROWS | PERCENTAGE}]

analyze_for_clause:
  FOR TABLE
| 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 包的 method_opt 语法相同，请参见 method_opt。

与 DBMS_STATS 包收集统计信息的方式相比，ANALYZE 语句并没有提供那么丰富的策略设置。为了支持 ANALYZE 语句只收集 GLOBAL 级别的统计信息，约定规则是只需要在 use_partition 语法中设置 partition_name 为表名即可。

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

收集用户 user 的表 tbl1 的统计信息。

ANALYZE TABLE tbl1 COMPUTE STATISTICS FOR ALL COLUMNS SIZE AUTO;

收集用户 user 的表 t_part1 的 GLOBAL 级别的统计信息，并且只收集数据分布不均匀的列的直方图。
```
ANALYZE TABLE t_part1 PARTITION (t_part1) COMPUTE STATISTICS FOR ALL COLUMNS SIZE SKEWONLY;
```
收集用户 user 的表 t_subpart1 的分区 p0sp0 和 p1sp2 的统计信息，所有的列的直方图都由优化器内部决定。
```
ANALYZE TABLE t_subpart1 SUBPARTITION(p0sp0,p1sp2) COMPUTE STATISTICS FOR ALL COLUMNS SIZE AUTO;
```

MySQL 模式下的 ANALYZE 语法

MySQL 模式下的 ANALYZE 语法如下：

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

当租户级配置项 enable_sql_extension 为 TRUE 的时候，可以使用 Oracle 模式的语法，即 OceanBase 数据库为 MySQL 模式提供的扩展语法。

analyze_stmt:
ANALYZE TABLE table_name [use_partition] analyze_statistics_clause

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

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

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

在 MySQL 模式下使用 Oracle 模式的语法收集用户 test 的表 tbl1 的统计信息。

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

手动统计信息收集的实践场景

非分区表的统计信息收集

当表的数据量和列的个数的乘积不高于一千万时，推荐使用如下命令收集非分区表的统计信息。如下示例中表 test.t1 有 10 个列，同时数据量在一百万行。

CREATE TABLE test.t1(c1 INT, c2 INT, c3 INT, c4 INT, c5 INT, c6 INT, c7 INT, c8 INT, c9 INT, c10 INT);
CREATE TABLE t2 (c1 INT PRIMARY KEY);
INSERT INTO t2 VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
INSERT /*+APPEND*/ INTO t1 SELECT * FROM (SELECT s0.c1 c1, s1.c1 c2, s2.c1 c3, s3.c1 c4, s4.c1 c5, s5.c1 c6, s0.c1 * 10 c7, s1.c1 * 10 c8, s2.c1 * 10 c9, s3.c1 * 10 c10 FROM t2 s0, t2 s1, t2 s2, t2 s3, t2 s4, t2 s5);

# 不收集直方图的场景
CALL DBMS_STATS.GATHER_TABLE_STATS('test', 't1', method_opt=>'for all columns size 1');

# 收集直方图的场景并使用默认策略
CALL DBMS_STATS.GATHER_TABLE_STATS('test', 't1');

当表的数据量和列的个数的乘积超过一千万时，推荐根据数据业务情况和系统资源设置一定并行度来加快统计信息的收集。如下示例中表 test.t1 中的数据量增大到一千万行，使用 8 个并行度。

# 不收集直方图的场景
CALL DBMS_STATS.GATHER_TABLE_STATS('test', 't1', degree=>8, method_opt=>'for all columns size 1');

# 收集直方图的场景并使用默认策略
CALL DBMS_STATS.GATHER_TABLE_STATS('test', 't1', degree=>8);

分区表的统计信息收集

相比较于非分区表，分区表的统计信息收集策略需要考虑分区统计信息收集。在系统资源允许的情况下，对于分区表的统计信息收集策略，推荐将收集非分区表的并行度再额外增加一倍。

如下示例中，相同场景的 test.t_part 表有 10 个列，同时数据量在一百万行。由于增加了分区统计信息的收集，因此并行度增加为 2。

CREATE TABLE t_part(c1 INT, c2 INT, c3 INT, c4 INT, c5 INT, c6 INT, c7 INT, c8 INT, c9 INT, c10 INT) PARTITION BY HASH(c1) PARTITIONS 128;
CREATE TABLE t2 (c1 INT PRIMARY KEY);
INSERT INTO t2 VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
INSERT /*+APPEND*/ INTO t_part SELECT * FROM (SELECT s0.c1 c1, s1.c1 c2, s2.c1 c3, s3.c1 c4, s4.c1 c5, s5.c1 c6, s0.c1 * 10 c7, s1.c1 * 10 c8, s2.c1 * 10 c9, s3.c1 * 10 c10 FROM t2 s0, t2 s1, t2 s2, t2 s3, t2 s4, t2 s5);

针对分区表，除了上述增加并行度以外，也可以考虑分区推导方式的收集，即收集分区的统计信息，进而通过分区统计信息推导全局统计信息，以加快收集的效率。例如同样的场景，不增加并行度，而是调整收集的分区方式，示例如下。

# 不收集直方图的场景
CALL DBMS_STATS.GATHER_TABLE_STATS('test', 't_part', granularity=>'APPROX_GLOBAL AND PARTITION', method_opt=>'for all columns size 1');

# 收集直方图的场景并使用默认策略
CALL DBMS_STATS.GATHER_TABLE_STATS('test', 't_part', granularity=>'APPROX_GLOBAL AND PARTITION');

更多关于分区表统计信息的收集与更新策略，以及详细示例参见分区表统计信息的收集与更新策略。

Schema（数据库）级别的统计信息收集

除了手动的对单表的统计信息收集以外，OceanBase 数据库 DBMS_STATS 系统包还提供了对整个用户下的所有表进行统计信息收集。

说明

该功能仅在 OceanBase 数据库 V4.x 版本的双模式和 OceanBase 数据库 V3.2.x 版本的 Oracle 模式下支持。

由于收集某个用户下的所有表统计信息比较耗时，因此，建议在业务低峰期使用该功能。

如果该用户下所有表的数据量都是一些小表（数据量不超过一百万行），可以直接使用如下示例中收集 test 用户的统计信息命令。

# 不收集直方图的场景
CALL DBMS_STATS.GATHER_SCHEMA_STATS('test', method_opt=>'for all columns size 1');

# 收集直方图的场景并使用默认策略
CALL DBMS_STATS.GATHER_SCHEMA_STATS('test');

当收集的用户下存在一些大表（行数在千万级别）时，可以在业务低峰期增大并行度来收集：

# 不收集直方图的场景
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');

如果用户下存在超大表（行数超过 1 亿）时，可以选择针对超大表开大并行单独收集，然后锁定超大表的统计信息再使用上述命令收集整个用户的，收集完成后在解锁超大表的统计信息，后续按照增量模式收集，示例如下：

CALL DBMS_STATS.GATHER_TABLE_STATS('test', 'big_table', degree=>128, method_opt=>'for all columns size 1');

CALL DBMS_STATS.LOCK_TABLE_STATS('test','big_table');

CALL DBMS_STATS.GATHER_SCHEMA_STATS('TEST', degree=>'16', method_opt=>'for all columns size 1');

CALL DBMS_STATS.UNLOCK_TABLE_STATS('test','big_table');