Collect statistics

This topic describes how to collect statistics. OceanBase Cloud Platform (OCP) supports two statistics collection modes: automatic collection and manual collection.

Prerequisites

To collect statistics, make sure that you have the following permissions:

• Resource Permissions : Cluster Maintenance or Tenant Maintenance permission
• Menu Permissions : Permission on the Statistics Management menu of Tenants

Automatic statistics collection

By default, the OceanBase Database optimizer automatically collects statistics every day based on the maintenance windows to ensure iterative updates of statistics. By default, the optimizer starts to collect statistics at 22:00 on Monday to Friday with the maximum collection duration of 4 hours and at 6:00 on Saturday and Sunday with the maximum collection duration of 20 hours. The following table describes the maintenance windows.

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

You can perform the following steps to adjust automatic statistics collection settings:

1. Log in to the OCP console.

2. In the left-side navigation pane, select Tenants . The Tenants page appears.

3. On the Tenants page, find the target tenant and click its name to go to the Overview page of the tenant.

4. In the left-side navigation pane of the page that appears, click Statistics Management .

5. On the Statistics Overview page that appears, click Automatic Statistics Collection in the upper-right corner.

Enable or disable automatic statistics collection

By default, all maintenance windows are enabled. You can click Enable or Disable in the Actions column of a maintenance window to enable or disable automatic statistics collection.

Edit automatic statistics collection settings

To edit automatic statistics collection settings, click Edit in the Actions column of a maintenance window and adjust the start time or maximum collection duration.

Modify automatic statistics collection parameters

OCP provides default automatic statistics settings regarding the collection method, degree of parallelism (DOP), and granularity. You can use the default settings or click the Edit icon next to a parameter to modify the setting.

The following table describes the parameters.

Manual statistics collection

The OceanBase Database optimizer may be unable to complete statistics collection on ultra-large tables or too many tables within one maintenance window based on the default statistics collection strategies. In this case, you can manually collect statistics. Manual statistics collection is supported at the schema and table levels.

Collect schema-level statistics

You can collect statistics on all tables under a tenant.

Procedure

1. Log in to the OCP console.

2. In the left-side navigation pane, select Tenants . The Tenants page appears.

3. On the Tenants page, find the target tenant and click its name to go to the Overview page of the tenant.

4. In the left-side navigation pane, click Statistics Management to go to the Statistics Overview page.

5. Click Collect Schema Statistics in the upper-right corner.

6. (Optional) In the panel that appears on the right, select the database for statistics collection and configure related parameters.

   For more information about statistics collection parameters, see Modify automatic statistics collection parameters.

7. Click OK .

Example

Here is an example of manually collecting schema-level statistics:

1. Go to the Statistics Overview page and click Collect Schema Statistics in the upper-right corner.

2. The Collect Schema Statistics panel appears.

3. Select a database for statistics collection. You can perform a fuzzy search for the target database. The drop-down list displays all databases under the current tenant. You can select only one database.

4. Modify statistics collection parameters. To collect partition-level statistics without collecting index statistics by using an DOP of 5 and the default settings of other parameters, modify related parameters as follows:

   1. Change the value of the granularity parameter to PARTITION.

   2. Change the value of the degree parameter to 5.

   3. Change the value of the cascade parameter to FALSE.

   4. Retain the default settings of other parameters.

5. Click OK .

Collect table-level statistics

You can manually collect statistics on a single table in the Statistics Table section. For a partitioned table, you can expand the partitions for more fine-grained statistics collection.

Procedure

1. Log in to the OCP console.

2. In the left-side navigation pane, select Tenants . The Tenants page appears.

3. On the Tenants page, find the target tenant and click its name to go to the Overview page of the tenant.

4. In the left-side navigation pane, click Statistics Management to go to the Statistics Overview page.

5. In the Statistics Table section, select the business table for statistics collection and click Collect in the Actions column.

6. (Optional) In the panel that appears on the right, configure statistics collection parameters.

   For more information about statistics collection parameters, see Modify automatic statistics collection parameters.

7. Click OK .

Related operations

• In the Statistics Table section, enter a table name keyword in the search box in the upper-right corner and click the Search icon. Business tables meeting the search condition are displayed.

  • You can specify a table in the schema.table or table format.
  • Fuzzy match is supported only for table names.

• For a table in the Normal state, you can collect statistics on the table or lock the table.

• For a table in the Locked state, you can only unlock the table.