The optimizer of OceanBase Database can optimize SQL queries through parallel execution. In the optimizer, the degree of parallelism (DOP) indicates the amount of parallel resources. In actual business scenarios, you need to determine the DOP based on your query and business needs.
OceanBase Database supports enabling parallel execution for queries through auto DOP, hints, system variables, and schema DOP. However, parallel execution is not supported in the following scenarios:
- Parallel execution is not supported for queries involving PL UDFs or dblinks.
- Parallel execution is not supported for base tables scanned through DAS, and the DOP for such tables is fixed at 1 in the plan.
If parallel execution is supported for a query, the DOPs of the various parallel execution methods are ranked as follows:
- DOP specified in a table-level hint
- DOP specified in a global hint
- DOP specified in system variables
- Schema DOP
Configure DOP using a table-level hint
You can use a table-level hint to specify the DOP for a table. The basic syntax is as follows:
/*+PARALLEL ( @qb_name table_name dop_value)*/
For example, query Q1 specifies to execute tables t1 and t2 in parallel with DOPs of 8 and 4, respectively. Here is an example:
Q1: SELECT /*+PARALLEL(t1 8) PARALLEL(t2 4)*/ * FROM t1, t2 WHERE t1.c1 = t2.c1;
For tables not specified with a hint, the system determines the DOP based on lower-priority methods. For example, query Q2 specifies to execute table t1 in parallel with a DOP of 8. If parallel execution is not enabled for table t2, it will be scanned with a DOP of 1, namely, without parallel execution enabled.
Q2: SELECT /*+PARALLEL(t1 8)*/ * FROM t1, t2 WHERE t1.c1 = t2.c1;
Configure DOP using a global hint
You can use a global hint to specify the default DOP for a query. The basic syntax is as follows:
/*+PARALLEL ( dop_value )*/
/*+PARALLEL ( AUTO )*/
/*+PARALLEL ( MANUAL )*/
For example, in query Q3, the global hint PARALLEL( 8 ) specifies to use a DOP of 8 for tables t1 and t2.
A global hint can also specify the DOP acquisition strategy for a query. For example, in query Q4, the global hint PARALLEL ( AUTO ) specifies to use the auto DOP strategy for all tables in the query.
The global hint has a lower priority than a table-level hint. When both global and table-level hints are present, the global hint takes effect only for tables not specified with a table-level hint. For example, in query Q5, a table-level hint specifies to use a DOP of 8 for table t1 and a DOP of 4 for table t2. In query Q6, a table-level hint specifies to use a DOP of 8 for table t1, and the auto DOP strategy is used to determine a DOP for table t2.
Q3: SELECT /*+PARALLEL(8)*/ * FROM t1, t2 WHERE t1.c1 = t2.c1;
Q4: SELECT /*+PARALLEL(AUTO)*/ * FROM t1, t2 WHERE t1.c1 = t2.c1;
Q5: SELECT /*+PARALLEL(t1 8) PARALLEL(4)*/ * FROM t1, t2 WHERE t1.c1 = t2.c1;
Q6: SELECT /*+PARALLEL(t1 8) PARALLEL ( AUTO )*/ * FROM t1, t2 WHERE t1.c1 = t2.c1;
Configure DOP using a system variable
There are two types of system variables related to DOP: one for configuring the DOP acquisition strategy, and the other for configuring a fixed DOP.
You can set the parallel_degree_policy variable at the GLOBAl or SESSION level to enable the auto DOP strategy. In the auto DOP strategy, the system determines the DOP for a table based on a specific algorithm. For more information, see Enable the auto DOP strategy.
If you specify a DOP with a global PARALLEL hint or enable the auto DOP strategy, or set the parallel_degree_policy variable to enable the auto DOP strategy, the fixed DOP setting becomes invalid. In other scenarios, you can set the following internal system variables to configure the DOP for parallel queries or DML operations. Here is an example:
SET _enable_parallel_query = 1;
SET _enable_parallel_dml = 1;
SET _force_parallel_query_dop = 8;
SET _force_parallel_dml_dop = 8;
Schema DOP
When you use the fixed DOP strategy and enable parallel execution by using the following internal system variables but set the DOP to 1, the query uses the DOP specified when the table was created for parallel execution.
SET _enable_parallel_query = 1;
SET _enable_parallel_dml = 1;
SET _force_parallel_query_dop = 1;
SET _force_parallel_dml_dop = 1;
In the following example, the DOP specified when creating the t1 table is 8. If you use the ALTER TABLE statement to change the DOP of the t1 table to 4 and the ALTER INDEX statement to change the DOP of the idx1 index to 2, parallel execution is enabled for subsequent queries with the adjusted DOPs.
CREATE TABLE t1(c1 INT, c2 INT, c3 INT) PARALLEL 8;
CREATE INDEX idx1 ON t1(c1);
ALTER TABLE t1 PARALLEL 4;
ALTER INDEX idx1 PARALLEL 2;