Differences in Manual Statistics Collection for OceanBase: DBMS_STATS vs ANALYZE Across Versions

1. Manual Statistics Collection Differences

If you need to display statistics for a table, the mainstream methods are the DBMS_STATS system package and the ANALYZE command.

1.1 Version Differences in Command Support

Version

MySQL Mode

Oracle Mode

3.X

Only supports the

ANALYZE

command

Supports both

DBMS_STATS

package and

ANALYZE

command

4.X

Supports both

DBMS_STATS

package and

ANALYZE

command

Supports both

DBMS_STATS

package and

ANALYZE

command

Oracle Mode

Recommended to use the DBMS_STATS package for manual statistics collection.

Reason: DBMS_STATS provides finer control (table‑level, schema‑level, granularity, parallelism, etc.), which meets more complex business scenarios.

MySQL Mode

In version 3.x, it is recommended to use the ANALYZE command for manual statistics collection.

Reason: The 3.x MySQL mode does not support the DBMS_STATS package. To use similar Oracle‑mode features, you must enable the enable_sql_extension system variable, which carries risks and is not recommended for regular use.

1.2 Daily Merge Operation Differences

Version

Statistics Collection Method

Advantages & Disadvantages

3.X

Statistics are automatically collected during daily merge.

Advantage: automation simplifies the process. Disadvantage: incremental merge leads to less precise statistics and cannot collect histogram information, which is problematic for data‑skew scenarios.

4.X

Statistics collection is decoupled from daily merge; merge no longer collects statistics.

Advantage: architectural improvement creates conditions for more accurate statistics. Disadvantage: still expects better accuracy for complex data scenarios.

2. DBMS_STATS Method

2.1 Table‑Level Statistics Collection

2.1.1 Non‑Partitioned Tables

-- When the table size and column count are small (less than 10 million rows)
call dbms_stats.gather_table_stats('test', 't1');

-- When the table size and column count are large (greater than 10 million rows)
call dbms_stats.gather_table_stats(
  'test',
  't1',
  degree=>8
);

2.1.2 Partitioned Tables

For partitioned tables you can increase the granularity and choose specific partitions for more precise statistics.

-- Increase granularity and select partitions
call dbms_stats.gather_table_stats(
  'test',
  't_part',
  degree=>2,
  granularity=>'APPROX_GLOBAL AND PARTITION'
);

2.2 Schema‑Level Statistics Collection

You can also collect statistics for all objects under a schema, which is useful for batch processing.

-- Collect stats for all objects in TEST schema
call dbms_stats.gather_schema_stats('TEST');

-- Collect with parallelism of 16
call dbms_stats.gather_schema_stats('TEST', degree=>'16');

-- Collect stats for big_table with parallelism 128 and column size 1
call dbms_stats.gather_table_stats(
  'test',
  'big_table',
  degree=>128,
  method_opt=>'for all columns size 1'
);

-- Lock stats for big_table
call dbms_stats.lock_table_stats('test', 'big_table');

-- Unlock stats for big_table
call dbms_stats.unlock_table_stats('test', 'big_table');

3. ANALYZE Method

For OceanBase 3.x MySQL tenant mode, the recommended way is to use the ANALYZE command.

3.1 MySQL Mode ANALYZE Syntax

-- MySQL mode ANALYZE statement usage
ANALYZE TABLE table_name UPDATE HISTOGRAM ON column_name_list WITH INTNUM BUCKETS;

-- Example
ANALYZE TABLE sbtest1 UPDATE HISTOGRAM ON id,k,c,pad WITH 30 BUCKETS;

If you want to collect statistics for all columns without listing them, use the FOR ALL COLUMNS clause, which is an Oracle‑mode syntax. Therefore you must first enable the enable_sql_extension system variable.

ALTER SYSTEM SET enable_sql_extension = TRUE;
ANALYZE TABLE products COMPUTE STATISTICS FOR ALL COLUMNS SIZE 30;

4. Statistics Verification

MySQL Mode

select distinct DATABASE_NAME, TABLE_NAME
from oceanbase.DBA_OB_TABLE_STAT_STALE_INFO
where DATABASE_NAME not in ('oceanbase','mysql','__recyclebin')
  and (IS_STALE = 'YES' or LAST_ANALYZED_TIME is null);

Oracle Mode

select distinct OWNER, TABLE_NAME
from sys.DBA_OB_TABLE_STAT_STALE_INFO
where OWNER != 'oceanbase'
  and OWNER != '__recyclebin'
  and (IS_STALE = 'YES' or LAST_ANALYZED_TIME is null);

For more information, see the 2025 OceanBase Developer Conference announcement and related technical articles linked below.

2025 OceanBase Developer Conference – May 17, Guangzhou

Other related posts:

Bandwidth Exhausted by OBServer Backup – Cluster ‘Leaderless’ Crisis

OBLogProxy Failure Cases in Binlog Mode

Logic for Calculating OceanBase Available CPU Cores

Resources:

GitHub: https://github.com/actiontech/sqle

Documentation: https://actiontech.github.io/sqle-docs/

Official Site: https://opensource.actionsky.com/sqle/

WeChat Group: Add the assistant to join ActionOpenSource

Commercial Support: https://www.actionsky.com/sqle