Parallel execution tuning tips

This topic describes the basic tips for parallel execution tuning in OceanBase Database.

Manually collect statistics

If the statistics saved in the optimizer are outdated, a nonoptimal execution plan may be generated. OceanBase Database provides interfaces for manually collecting statistics in V3.2 and V4.1.

• Optimizer statistics in the documentation of OceanBase Database V3.2.x
• Optimizer statistics in the documentation of OceanBase Database V4.x

V3.2

This section describes the syntax for manually collecting statistics on a primary table in OceanBase Database V3.2. Statistics collection is not supported for index tables in this version.

Syntax in MySQL mode

-- Enable support for the ANALYZE TABLE statement.
ALTER SYSTEM SET enable_sql_extension = true;

-- Collect the statistics on the T1 table of the TEST user, and enable the AUTO strategy for determining the number of buckets for all columns.
ANALYZE TABLE T1 COMPUTE STATISTICS FOR ALL COLUMNS SIZE AUTO;

Syntax in Oracle mode

-- Collect the global statistics on the T1 table of the TEST user, and enable the AUTO strategy for determining the number of buckets for all columns.
CALL dbms_stats.gather_table_stats('TEST', 'T1', granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE AUTO');

-- Alternatively, use the analyze syntax.
-- Collect the statistics on the T1 table of the TEST user, and enable the AUTO strategy for determining the number of buckets for all columns.
ANALYZE TABLE T1 COMPUTE STATISTICS FOR ALL COLUMNS SIZE AUTO;

V4.1

This section describes the syntax for collecting statistics on a primary table or an index table in OceanBase Database V4.1.

Syntax in MySQL mode

-- Collect the global statistics on the T1 table of the TEST user, and enable the AUTO strategy for determining the number of buckets for all columns.
CALL dbms_stats.gather_table_stats('TEST', 'T1', granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE AUTO');

-- Collect the statistics on the IDX index in the T1 table of the TEST user, set the degree of parallelism (DOP) to 4, and specify the table name. The table name must be specified because the index name is not unique.
CALL dbms_stats.gather_index_stats('TEST', 'IDX', degree=>4, tabname=>'T1');

Syntax in Oracle mode

-- Collect the global statistics on the T1 table of the TEST user, and enable the AUTO strategy for determining the number of buckets for all columns.
CALL dbms_stats.gather_table_stats('TEST', 'T1', granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE AUTO');

-- Collect the statistics on the IDX index of the TEST user, and set the DOP to 4. You do not need to specify the table name because the index name is unique.
CALL dbms_stats.gather_index_stats('TEST', 'IDX', degree=>4);

Modify the partitioning method for a partition-wise join

For a large-table join in a proof of concept (POC) scenario, if allowed by the business system, you can use the same partitioning method for the large tables and bind the tables to the same table group to achieve optimal performance for partition-wise joins.

When you perform a partition-wise join, you must adjust the DOP to a value that matches the partition quantity to achieve optimal performance.

Adapt the DOP and partition quantity

Generally, preferable performance can be achieved if the DOP and the partition quantity are in an integral multiple relationship.

For more information, see the Performance optimization through load balancing section in the Overview topic.

Create appropriate indexes

You can create appropriate indexes to reduce the amount of data to be scanned, thereby improving the parallel execution performance. You need to determine the tables and columns on which indexes are to be created based on specific SQL statements.

Create replicated tables

In OceanBase Database V4.2 and later, you can create replicated tables to reduce data redistribution, thereby improving the parallel execution performance. For more information about replicated tables, see Replicated tables in OceanBase Database. The basic syntax for creating a replicated table is as follows:

CREATE TABLE dup_t1(c1 int) duplicate_scope = 'cluster';