Automatic statistics collection

OceanBase Database in Oracle mode allows you to configure a scheduled task to automatically collect statistics on tables on a regular basis.

Note

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

As a distributed database, OceanBase Database cannot create a collection task when you create a tenant within the server. Therefore, you must explicitly use DBMS_JOB to create a scheduled task for a tenant that has been created or has been upgraded from an earlier version of the server. For example:

## Set a task to automatically collect statistics every other day from now on.
DECLARE
   jid BINARY_INTEGER;
BEGIN
   DBMS_JOB.SUBMIT(jid, 'dbms_stats.gather_database_stats_job_proc();', trunc(sysdate) , 'trunc(sysdate) + 1');
   COMMIT;
END;

The OceanBase Database optimizer automatically collects statistics based on the following strategies:

• Automatic statistics collection strategies for system tables and non-partitioned user tables:

  • If the table does not have global-level statistics, statistics are collected automatically.

  • If the table has global-level statistics and the statistics have expired, statistics are collected automatically.

  Otherwise, statistics are not collected automatically.

• Automatic statistics collection strategy for partitioned user tables:

  • If the table does not have any statistics, collect all statistics automatically.

  • If the table has partition-level statistics but no global-level statistics, collect statistics automatically in an incremental way.

  • If the table has global-level statistics and the statistics have expired, all statistics are collected automatically.

  • If the table has GLOBAL statistics, and the statistics of only some partitions have expired, the system automatically collects the statistics of partitions with expired statistics and deduces GLOBAL statistics incrementally.