手动收集统计信息 | OceanBase 文档中心

手动收集统计信息

更新时间：2024-12-09 16:00:50

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

通过 DBMS_STATS 包收集统计信息

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

说明

MySQL 模式下暂不支持此方式，只支持 ANALYZE 语句收集方式。

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

参数解释如下表所示。

参数	解释
ownname	用户名。如果用户名设置为 `NULL`，会默认使用当前登录用户名。
tabname	表名。
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` 和 `PATITION` 级别的统计信息。 `'GLOBAL AND PARTITION'`：收集 `GLOBAL` 和 `PATITION` 级别的统计信息。 `'APPROX_GLOBAL AND PARTITION'`：收集分区级别的统计信息，并根据分区信息推导出全局级别的统计信息。
cascade	该参数暂未实现，不可用。
stattab	该参数暂未实现，不可用。
statid	该参数暂未实现，不可用。
statown	该参数暂未实现，不可用。
no_invalidate	该参数暂未实现，不可用。
stattype	该参数暂未实现，不可用。
force	是否强制收集信息，并忽略锁的状态，默认为 `FALSE`。

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 包收集统计信息的相关示例如下：

收集用户 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');

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

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

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