Asynchronous statistics collection promptly processes tables or partitions whose data has changed significantly. The system periodically checks data changes in tables or partitions and uses an independent threshold to determine whether to add them as candidates for asynchronous statistics collection.
Asynchronous statistics collection and automatic statistics collection are independent collection mechanisms. Automatic statistics collection uses maintenance windows to collect missing or stale statistics, whereas asynchronous statistics collection uses an independent background job to check data changes.
OceanBase Database supports asynchronous statistics collection starting with V4.2.4. Starting with V4.3.5 BP2, the system uses the data change ratio since the last statistics collection to determine whether a table or partition becomes a candidate for asynchronous statistics collection.
Determine candidates for asynchronous statistics collection
The data change ratio is calculated as follows:
Data change ratio = (Inserted rows + Updated rows + Deleted rows) / Number of rows recorded in the last statistics
The numbers of inserted, updated, and deleted rows are cumulative values since the last statistics collection. By default, when the data change ratio is greater than 2, which means that the cumulative number of affected rows exceeds twice the number of rows recorded in the last statistics, the table or partition becomes a candidate for asynchronous statistics collection.
If the number of rows recorded in the last statistics is 0, the table or partition becomes a candidate when the currently monitored cumulative number of inserted rows minus the cumulative number of deleted rows is greater than 0. If the result is 0, the table or partition does not become a candidate solely because the number of rows recorded in the last statistics is 0.
Note
Meeting the preceding condition means that the table or partition becomes a candidate for asynchronous statistics collection. It does not mean that statistics collection is completed immediately. The default threshold for asynchronous statistics collection is 2. Automatic statistics collection uses STALE_PERCENT to control the staleness threshold, whose default value is 10, or 10%.
Determination granularity for partitioned tables
The system independently determines candidates at the following granularities:
For a non-partitioned table, the system makes the determination at the table level.
For a partitioned table, the system makes the determination at the partition level.
For a subpartitioned table, the system makes the determination at the subpartition level.