Statistics

Statistics update mechanism

• Threshold-triggered update: Asynchronous statistics update is triggered when table data changes exceed a certain percentage (by default, the data volume changes by more than 10 times).

• Partitioned table support: OceanBase supports partition-level statistics update and management.

Optimization strategies for statistics in AP scenarios

1. Customized collection strategies

   • Selective collection: Configure separate collection tasks for the core query tables or key columns in AP scenarios.
   • Partition priority: Prioritize updating partitions with the highest usage frequency or the greatest changes.

2. Configure parallelism

   • When collecting statistics for ultra-large tables, configure the collection parallelism appropriately.

3. Dynamically adjust the update frequency

   • Flexibly configure the statistics update frequency based on the table data's update pattern to avoid unnecessary overhead.

Scenario examples

Adjust the statistics collection window

By default, the OceanBase optimizer uses maintenance windows for daily automatic statistics collection to ensure statistics are updated iteratively. The default start time for tasks from Monday to Sunday is 22:00, with a maximum collection duration of 4 hours, as shown in the following table.

We need to configure the maintenance windows appropriately based on the actual business situation. For example, if a maintenance window coincides with business peak hours, you can adjust its start time or disable statistics collection on specific days. When there are many tables or a large number of ultra-large tables in the business environment, you can adjust the collection duration of the maintenance windows.

The following are some configuration examples.

-- Disable Automatic Monday Stat Collection
CALL DBMS_SCHEDULER.DISABLE('MONDAY_WINDOW');

-- Enable Monday Automatic Statistics Collection
CALL DBMS_SCHEDULER.ENABLE('MONDAY_WINDOW');

-- Set the start time for automatic statistics collection on Monday to 8:00 PM.
CALL DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'NEXT_DATE', '2022-09-12 20:00:00');

-- Set the duration for automatic weekly statistics collection to 6 hours.
-- 6 hours <=> 6 * 60 * 60 * 1000 * 1000 <=> 21600000000 us
CALL DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW', 'JOB_ACTION', 'DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(21600000000)');

Statistics collection strategy for ultra-large tables

In scenarios with ultra-large tables, the optimizer's default statistics collection strategy may not complete collecting all table statistics within a single maintenance window. Therefore, it is necessary to set an appropriate collection strategy for ultra-large tables. The main time-consuming aspects of collecting statistics for ultra-large tables are:

• The table contains a large amount of data, requiring a full-table scan for collection, which is time-consuming.
• Histogram collection involves complex calculations, adding extra time cost.
• For large partitioned tables, statistics and histograms are collected by default for subpartitions, partitions, and the entire table, resulting in a cost of 3 * (cost(full-table-scan) + cost(histogram)).

Based on these time-consuming points, optimizations can be made according to the table's actual situation and related query patterns. Recommendations are as follows:

• Set an appropriate default collection parallelism. Note that after setting the parallelism, related automatic collection tasks should be scheduled during off-peak business hours to avoid impacting operations. It is recommended to keep the parallelism within 8. You can set it using the following method.

  -- The same for Oracle or MySQL business tenants:
  CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'degree', '8');
  

• Set the default histogram collection method for columns. Consider disabling histogram collection for columns with evenly distributed data.

  -- Same business tenant in Oracle or MySQL
  -- 1. If the data distribution is uniform for all columns in the table, you can set all columns not to collect histograms as follows:
  CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'method_opt', 'for all columns size 1');
  
  -- 2. If only a very few columns in the table have uneven data distribution and histograms need to be collected for those columns, while histograms are not needed for others, you can set it as follows (collect histograms for c1 and c2, do not collect histograms for c3, c4, and c5):
  CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'method_opt', 'for columns c1 size 254, c2 size 254, c3 size 1, c4 size 1, c5 size 1');
  

• Set the default collection granularity for partitioned tables. For some partitioned tables, such as hash partitions or key partitions, consider collecting only global statistics, or you can also set a collection method that derives global statistics from partitions.

  -- Same business tenant in Oracle or MySQL
  -- 1. Set to collect global statistics only.
  CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'granularity', 'GLOBAL');
  
  -- 2. Set the partition to derive the global collection method
  CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'granularity', 'APPROX_GLOBAL AND PARTITION');
  

• Use caution when setting sampling for large table statistics collection. When sampling is set for large tables, the histogram sample size in earlier versions can also become large, which can counterproductive. Setting sampling is only suitable for scenarios where only basic statistics are collected and histograms are not collected.

  -- Oracle or MySQL business tenants are the same, such as the deleted granularity.
  -- 1. Set all columns not to collect histograms:
  CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'method_opt', 'for all columns size 1');
  
  -- 2. Set the sampling ratio to 10%.
  CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'estimate_percent', '10');
  

Additionally, if you need to clear/delete a set default collection strategy, you only need to specify the attribute to clear {attribute}, using the following method.

-- Oracle or MySQL business tenant is the same, such as deletion granularity
CALL DBMS_STATS.delete_table_prefs('database_name', 'table_name', 'granularity');

If you have set the relevant collection strategies and need to check whether they were successfully set, you can use the following method to query.

-- The same for Oracle or MySQL business tenants, such as obtaining a specified degree of parallelism
SELECT DBMS_STATS.GET_PREFS('degree', 'database_name','table_name') from dual;

Besides the above methods, you can also consider locking the relevant statistics after manually collecting statistics for large tables. Note that once a table's statistics are locked, automatic collection will not update them. This is suitable for scenarios where data characteristics do not change significantly and data values are not sensitive. If you need to recollect locked statistics, you must unlock them first.

-- The same as that of an Oracle or MySQL business tenant. Lock tables' statistics.
CALL DBMS_STATS.LOCK_TABLE_PREFS('database_name', 'table_name');

-- The same as that of Oracle or MySQL business tenants for unlocking table statistics.
CALL DBMS_STATS.UNLOCK_TABLE_PREFS('database_name', 'table_name');

HMS Catalog External Table Statistics Collection Strategy

OceanBase Database extends the DBMS_STATS package by adding interfaces such as GATHER_CATALOG_TABLE_STATS, supporting the collection of table-level and column-level statistics for external tables (in Hive or Iceberg format) within HMS Catalog. The optimizer can leverage these statistics to select better execution plans for external table queries (such as JOIN order, degree of parallelism, etc.), significantly improving external table query performance.

Feature Introduction

Statistical Information Types

The following statistical information is supported for collection:

Lock Statistics

Supports locking the statistics of a specified table or partition to prevent them from being overwritten by subsequent collection operations. This is typically used to stabilize execution plans in stable environments.

Supported Formats

• Hive Parquet/ORC/CSV tables
• Iceberg tables

Limitations

• Currently, only Hive format tables (Parquet/ORC/CSV) within HMS Catalog are supported. Iceberg format supports statistics collection but does not currently support partition enumeration.
• Statistical information is stored in internal system tables of OceanBase Database and is not written back to HMS.
• CDC/SCN snapshot reads are not supported (external tables themselves do not support this feature).
• The collection operation is executed synchronously; large tables may take a long time. It is recommended to perform it during off-peak business hours.

Procedure

Step 1: Collect Global Statistics

Collect global (table-level + column-level) statistics for external tables in HMS Catalog.

Collect statistics for the lineitem table:

obclient> CALL DBMS_STATS.GATHER_CATALOG_TABLE_STATS(
    'hive_catalog',                                              -- catalog name
    'test_oss',                                                  -- database name
    'lineitem',                                                  -- table name
    NULL,                                                        -- partition name. NULL indicates the entire table.
    100,                                                         -- sampling percentage. 100 indicates full collection.
    'BLOCK',                                                     -- sampling method. BLOCK indicates block sampling.
    NULL,                                                        -- method_opt. NULL uses the default.
    32                                                           -- degree of parallelism
    );

For more information about DBMS_STATS.GATHER_CATALOG_TABLE_STATS, see GATHER_CATALOG_TABLE_STATS.

Step 2: View the collection results

1. View table-level statistics.

   obclient> SELECT *
       FROM oceanbase.DBA_OB_EXTERNAL_TAB_STATISTICS
       WHERE catalog_name = 'hive_catalog'
       AND database_name = 'test_oss'
       AND table_name = 'lineitem';
   

   For more information about oceanbase.DBA_OB_EXTERNAL_TAB_STATISTICS, see oceanbase.DBA_OB_EXTERNAL_TAB_STATISTICS.

2. View column-level statistics (global).

   obclient> SELECT *
       FROM oceanbase.DBA_OB_EXTERNAL_TAB_COL_STATISTICS
       WHERE catalog_name = 'hive_catalog'
       AND database_name = 'test_oss'
       AND table_name = 'lineitem';
   

   For more information about oceanbase.DBA_OB_EXTERNAL_TAB_COL_STATISTICS, see oceanbase.DBA_OB_EXTERNAL_TAB_STATISTICS.

3. View partition column statistics.

   obclient> SELECT *
       FROM oceanbase.DBA_OB_EXTERNAL_PART_COL_STATISTICS
       WHERE catalog_name = 'hive_catalog'
       AND database_name = 'test_oss'
       AND table_name = 'lineitem';
   

   For more information about oceanbase.DBA_OB_EXTERNAL_PART_COL_STATISTICS, see oceanbase.DBA_OB_EXTERNAL_PART_COL_STATISTICS.

Step 3: Collect statistics for a specified partition

obclient> CALL DBMS_STATS.GATHER_CATALOG_TABLE_STATS(
    'hive_catalog',
    'test_oss',
    'lineitem',
    'l_shipdate=1998-01-01',    -- Specify the partition.
    100,
    'BLOCK',
    NULL,
    32
    );

For more information about DBMS_STATS.GATHER_CATALOG_TABLE_STATS, see GATHER_CATALOG_TABLE_STATS.

Step 4: Set collection parameters

Adjust the batch size for partition processing by using the SET_CATALOG_TABLE_PREFS procedure:

1. Set the batch size to 64.

   obclient> CALL DBMS_STATS.SET_CATALOG_TABLE_PREFS(
       'hive_catalog',
       'test_oss',
       'lineitem',
       'CATALOG_STATS_BATCH_SIZE',
       '64'
       );
   

   For more information about DBMS_STATS.SET_CATALOG_TABLE_PREFS, see SET_CATALOG_TABLE_PREFS.

2. View the current settings.

   obclient> SELECT DBMS_STATS.GET_CATALOG_PREFS(
       'CATALOG_STATS_BATCH_SIZE',
       'hive_catalog',
       'test_oss',
       'lineitem'
       );
   

   For more information about DBMS_STATS.GET_CATALOG_PREFS, see GET_CATALOG_PREFS.

3. Drop the setting (to restore the default value).

   obclient> CALL DBMS_STATS.DELETE_CATALOG_TABLE_PREFS(
       'hive_catalog',
       'test_oss',
       'lineitem',
       'CATALOG_STATS_BATCH_SIZE'
       );
   

   For more information about DBMS_STATS.DELETE_CATALOG_TABLE_PREFS, see DELETE_CATALOG_TABLE_PREFS.

Step 5: Lock and unlock statistics

1. Lock table statistics (to disable automatic refresh).

   obclient> CALL DBMS_STATS.LOCK_CATALOG_TABLE_STAT(
       'hive_catalog',
       'test_oss',
       'lineitem'
       );
   

   For more information about DBMS_STATS.LOCK_CATALOG_TABLE_STAT, see LOCK_CATALOG_TABLE_STAT.

2. Unlock.

   obclient> CALL DBMS_STATS.UNLOCK_CATALOG_TABLE_STAT(
       'hive_catalog',
       'test_oss',
       'lineitem'
       );
   

   For more information about DBMS_STATS.UNLOCK_CATALOG_TABLE_STAT, see UNLOCK_CATALOG_TABLE_STAT.

Step 6: Force a full collection

When you need to ignore freshness checks and forcibly collect statistics for all partitions, set force => TRUE:

obclient> CALL DBMS_STATS.GATHER_CATALOG_TABLE_STATS(
    'hive_catalog',
    'test_oss',
    'lineitem',
    NULL,
    100,
    'BLOCK',
    NULL,
    32,
    'DEFAULT',   -- granularity
    TRUE         -- force, to force a full collection
    );

For more information about DBMS_STATS.GATHER_CATALOG_TABLE_STATS, see GATHER_CATALOG_TABLE_STATS.

References

For detailed information and usage instructions on statistics, see the following topics:

• Statistics include Table Level Statistics and Column Level Statistics. For more information about types of statistics, see Overview of statistics.

• The OceanBase Database optimizer supports both manual and automatic statistics collection. For detailed information and operational instructions on statistics collection, see Overview of statistics collection methods.

• For detailed operational instructions on statistics management, see the Statistics management topic.

• Learn about the usage of statistics through a simple example. View the example.