This topic describes some basic tips for tuning parallel execution in OceanBase AI Database.
Manually collect statistics
Outdated statistics stored in the optimizer may result in suboptimal execution plans. For more details on manually collecting statistics, see Overview of statistics.
The syntax for manually collecting statistics on primary and index tables is as follows:
-- Collect global-level statistics for table T1 of user TEST, with the bucket count for all columns set to the auto strategy:
CALL dbms_stats.gather_table_stats('TEST', 'T1', granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE AUTO');
-- Gather index statistics for index IDX of table T1 in user TEST with a degree of parallelism of 4 (IDX is not unique, so the table name must be specified):
CALL dbms_stats.gather_index_stats('TEST', 'IDX', degree=>4, tabname=>'T1');
Modify partitioning by using partition-wise join
In a proof-of-concept scenario, if a large table needs to be JOINed, and business requirements permit, you can use the same partitioning method for both tables and bind them to the same table group. This enables optimal partition wise join performance.
When using partition wise join, adjust the degree of parallelism (DOP) to match the number of partitions for optimal performance.
Match DOP to the number of partitions
Generally, a good integer ratio between the DOP and the number of partitions yields better performance.
For more information, see the "Optimize performance through load balancing" section in Introduction to parallel execution.
Create appropriate indexes
Creating appropriate indexes reduces data scanning and improves parallel execution performance. There is no one-size-fits-all approach to determining which tables or columns to index; it requires analysis based on specific SQL queries.
Create replicated tables
Creating replicated tables reduces data redistribution and improves parallel execution performance. For more information about replicated tables, see Introduction to replicated tables. A basic syntax example is as follows:
CREATE TABLE dup_t1(c1 int) duplicate_scope = 'cluster';