Manual statistics collection

The OceanBase Database optimizer collects statistics by using the DBMS_STATS package or the ANALYZE statement.

Collect statistics by using the DBMS_STATS package

In Oracle mode, the OceanBase Database optimizer can use the DBMS_STATS package to collect table-level and schema-level statistics by calling stored procedures gather_table_stats and gather_schema_stats, respectively.

Note

OceanBase Database in MySQL mode does not support this statistics collection method. Only the ANALYZE statement is supported.

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

The following table describes the parameters.

method_opt

method_opt can be set in the following syntax:

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

The following table describes the parameters.

Examples

The following examples use the DBMS_STATS package to collect statistics:

• Collect global-level statistics for table tbl1 under user user.

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

• Collect partition-level statistics for table t_part1 under user user, with a DOP of 64. Only histograms of columns with uneven data distribution are collected.

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

• Collect all statistics for table t_subpart1 under user user, with a DOP of 128. Only 50% of data is collected, with histograms of columns determined by the optimizer.

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

Collect statistics by using the ANALYZE statement

OceanBase Database in Oracle mode and MySQL mode allows you to use the ANALYZE statement to collect statistics.

Syntax of the ANALYZE statement in Oracle mode

The syntax of the ANALYZE statement in Oracle mode is as follows:

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

The column histogram collection method in the preceding syntax is the same as the syntax of method_opt in the DBMS_STATS package. For more information, see method_opt.

The ANALYZE statement does not provide as many strategy settings as the DBMS_STATS package does. To implement statistics collection at the GLOBAL level only in an ANALYZE statement, you can set partition_name in the use_partition syntax to the table name.

The following examples collect statistics using the ANALYZE statement in Oracle mode:

• Collect statistics on table tbl1 under user user.

  ANALYZE TABLE tbl1 COMPUTE STATISTICS FOR ALL COLUMNS SIZE AUTO;
  

• Collect global-level statistics for table t_part1 under user user. Only histograms of columns with uneven data distribution are collected.

  ANALYZE TABLE t_part1 PARTITION (t_part1) COMPUTE STATISTICS FOR ALL COLUMNS SIZE SKEWONLY;
  

• Collect statistics on partitions p0sp0 and p1sp2 in table t_subpart1 under user user, with histograms of columns determined by the optimizer.

  ANALYZE TABLE t_subpart1 SUBPARTITION(p0sp0,p1sp2) COMPUTE STATISTICS FOR ALL COLUMNS SIZE AUTO;
  

Syntax of the ANALYZE statement in MySQL mode

The syntax of the ANALYZE statement in MySQL mode is as follows:

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

When the session variable enable_sql_extension is TRUE, you can use the syntax in Oracle mode, which is the extended syntax for OceanBase Database in MySQL mode.

analyze_stmt:
ANALYZE TABLE table_name [use_partition] analyze_statistics_clause

The following examples collect statistics using the ANALYZE statement in MySQL mode:

• Collect statistics on table tbl1, with the number of buckets being 30 for columns.

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

• Collect statistics on table tbl1 under user test by using the syntax in Oracle mode.

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