Statistics

In databases, the optimizer tries to generate the optimal execution plan for each SQL query, and accurate row count estimation and real-time effective statistics are usually required. Statistics actually refer to optimizer statistics, which is a collection of data describing the information about tables and columns in the database. It is a crucial part of the cost model for selecting the optimal execution plan. The optimizer cost model depends on the statistics of objects such as tables, columns, and predicates involved in a query to select and optimize execution plans. Accurate statistics help the optimizer select the optimal execution plan.

OceanBase Database supports basic statistics and histograms at the table and column levels, and provides collection strategies such as scheduled automatic collection, manual collection, online collection, and asynchronous collection when statistics seriously expire. In most systems, you usually do not need to worry about statistics because the optimizer regularly performs tasks to collect statistics on tables that need to be updated. However, in AP scenarios, there may be some super-large tables or tables that require batch updates followed by real-time queries. In such cases, the default statistics collection strategy may fail to collect statistics in a timely manner, which affects the plan generation. The following sections describe how to collect statistics in some AP scenarios.

Overview

Categories of statistics

Statistics can be classified into the following categories:

• Table-level statistics (including index tables): This includes statistics such as the number of rows, the number of macroblocks, the number of microblocks, and the average row length. These statistics are used to estimate the scan cost of a table.

• Column-level statistics

  • Value distribution of a column: maximum value, minimum value, average column length, and the number of different values (NDV).

  • Data skew: The frequency distribution (histogram) describes the distribution of data.

  • Null value rate: helps the optimizer handle queries involving NULL values.

Statistics collection methods supported in OceanBase Database

• Automatic collection: The optimizer periodically checks whether statistics need to be updated for each table.

• Manual collection: You can run a SQL statement to trigger statistics collection. This method is suitable for specific query optimizations. When you collect statistics, you can specify the collection strategy, such as the parallelism, granularity, and number of histogram buckets.

• Online collection: In scenarios such as batch import, PDML, and CREATE TABLE ... AS, you can use the GATHER_OPTIMIZER_STATISTICS hint or set the system variable _optimizer_gather_stats_on_load (which is enabled by default) to enable online statistics collection. You can also use the APPEND hint of the direct load feature to implement online statistics collection.

Statistics update mechanism

Optimization strategies for statistics in AP scenarios

1. Customize collection strategies

   • Selective collection: Configure collection tasks for core query tables or key columns in AP scenarios.
   • Partition priority: Prioritize updating the most frequently used or changed partitions.

2. Configure concurrency

   • Reasonably set the collection concurrency for ultra-large tables when collecting statistics.

3. Dynamically adjust the update frequency

   • Flexibly configure the statistics update frequency based on the table data update mode to avoid unnecessary overheads.

Scenario examples

Adjust the statistics collection window

By default, the OceanBase optimizer performs daily automatic statistics collection by maintaining a window, to ensure iterative updates of statistics. The default start time for tasks from Monday to Sunday is 22:00, and the maximum collection duration is 4 hours. The following table shows the default settings.

You can configure the maintenance window based on your business needs. For example, if the maintenance window coincides with your business peak hours, you can adjust the start time of the maintenance window or specify not to collect statistics on specific dates. If your business environment contains a large number of tables or a large number of ultra-large tables, you can adjust the maximum collection duration of the maintenance window.

Here are some configuration examples.

-- Disable automatic statistics collection on Mondays
call dbms_scheduler.disable('MONDAY_WINDOW');

-- Enable automatic statistics collection on Mondays
call dbms_scheduler.enable('MONDAY_WINDOW');

-- Set the start time of the automatic statistics collection task on Mondays to 8 p.m.
call dbms_scheduler.set_attribute('MONDAY_WINDOW', 'NEXT_DATE', '2022-09-12 20:00:00');

-- Set the duration of the automatic statistics collection task on Wednesdays 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 strategies for super-sized tables

The optimizer's default statistics collection strategy may result in the statistics of a super-sized table not being collected within one maintenance window. Therefore, you need to set a reasonable collection strategy for super-sized tables. The statistics collection for super-sized tables is time-consuming mainly for the following reasons:

• Super-sized tables contain a large amount of data. Therefore, the statistics collection requires a full scan of the table, which consumes a large amount of time.
• Histogram collection involves complex computations, which results in additional costs.
• By default, the optimizer collects statistics and histograms on subpartitions, partitions, and the entire table of a large partitioned table. The cost of collecting statistics and histograms on a large partitioned table is 3 × (cost(full table scan) + cost(histogram)).

You can optimize the statistics collection for super-sized tables based on the preceding time-consuming factors and the actual situations of the tables and related queries. We recommend that you do the following:

• Set an appropriate default collection parallelism. Note that after you set the collection parallelism, you need to set the related automatic collection tasks to be executed during off-peak hours to avoid impact on business. We recommend that you set the collection parallelism to 8 or a smaller value. You can use the following statement to set the collection parallelism:

-- The same for Oracle and MySQL business tenants:
call dbms_stats.set_table_prefs('database_name', 'table_name', 'degree', '8');

• Set the default histogram collection method for columns. We recommend that you do not collect histograms for columns with evenly distributed data.

-- The same for Oracle and MySQL business tenants
-- 1. If the data is evenly distributed in all columns of the table, you can use the following statement to specify that no histograms be collected for all columns:
call dbms_stats.set_table_prefs('database_name', 'table_name', 'method_opt', 'for all columns size 1');

-- 2. If the data is unevenly distributed in only a few columns of the table and histograms need to be collected for these columns, you can use the following statement to specify that no histograms be collected for other columns (c1 and c2 require histograms, and c3, c4, and c5 do not require histograms):
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 partitioned or key partitioned tables, you can collect only global statistics or specify that partitions be derived for global statistics.

-- The same for Oracle and MySQL business tenants
-- 1. Specify that only global statistics be collected:
call dbms_stats.set_table_prefs('database_name', 'table_name', 'granularity', 'GLOBAL');

-- 2. Specify that partitions be derived for global statistics:
call dbms_stats.set_table_prefs('database_name', 'table_name', 'granularity', 'APPROX_GLOBAL AND PARTITION');

• Use large table sampling with caution. If you enable large table sampling, the number of histogram samples in earlier versions becomes excessively large, which may result in counterproductive effects. Enabling sampling is suitable only for collecting basic statistics and not histograms.

-- The same for Oracle and MySQL business tenants. For example, you can delete the granularity attribute.
-- 1. Specify that no histograms be collected for all columns:
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');

To delete a default collection strategy that has been set, you only need to specify the attribute to be cleared. Use the following statement:

-- The same for Oracle and MySQL business tenants, such as deleting granularity.
call dbms_stats.delete_table_prefs('database_name', 'table_name', 'granularity');

After you set a collection strategy for the statistics of a super-sized table, you can query whether the strategy is set successfully. Use the following statement:

-- The same for Oracle and MySQL business tenants, such as querying the specified collection parallelism degree.
select dbms_stats.get_prefs('degree', 'database_name','table_name') from dual;

In addition to the preceding methods, you can manually collect the statistics of a super-sized table and then lock the statistics. Note that after the statistics are locked, automatic collection will not update the statistics. This is suitable for scenarios where the data characteristics change slightly and the values are not sensitive. If you need to recollect the locked statistics, you must first unlock them.

-- The same for Oracle and MySQL business tenants, lock the statistics of a table
call dbms_stats.lock_table_stats('database_name', 'table_name');

-- The same for Oracle and MySQL business tenants, unlock the statistics of a table
call dbms_stats.unlock_table_stats('database_name', 'table_name');

References

For more information about statistics, see the following topics:

• Statistics are divided into table-level statistics and column-level statistics. For more information about statistics types, see Overview.

• OceanBase Database optimizer supports manual and automatic statistics collection. For more information about statistics collection, see Overview.

• For more information about statistics management, see Manage statistics.

• You can learn how to use statistics by following the steps in Example.