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 db links.
- 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 methods are ranked in descending order as follows:
- DOP specified in a table-level parallel hint
- DOP specified in a global parallel hint
- DOP specified in system variables
- Schema DOP
Configure DOP by 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)*/
In the preceding example, Q1 specifies to execute the queries on 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 hints, the system determines the DOP based on lower-priority methods. In the preceding example, Q2 specifies to execute the query on table t1 in parallel with a DOP of 8. If parallel execution is not enabled for table t2, it will be scanned in serial with a DOP of 1.
Q2: SELECT /*+PARALLEL(t1 8)*/ * FROM t1, t2 WHERE t1.c1 = t2.c1;
Configure DOP by 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 )*/
In the preceding example, PARALLEL( 8 ) in Q3 specifies to use a DOP of 8 for tables t1 and t2.
You can also use a global hint to specify the DOP acquisition strategy. For example, PARALLEL ( AUTO ) in Q4 specifies to use the auto DOP strategy for all tables in the query. In the auto DOP strategy, the system calculates the DOP for a table based on a specific algorithm.
The global hint has a lower priority than the table-level hint. When both global and table-level hints are present, the global hint takes effect only for tables not specified with table-level hints. For example, in Q5, a table-level hint is specified for table t1, so a DOP of 8 is used for this table; a global hint is specified for table t2, so a DOP of 4 is used for this table. In Q6, a table-level hint is specified for table t1, so a DOP of 8 is used for this table; a DOP is automatically determined for table t2 based on the auto DOP strategy.
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 by using a system variable
There are two types of system variables related to DOP: one for specifying the DOP acquisition strategy, and the other for specifying a fixed DOP.
You can set the parallel_degree_policy variable at the GLOBAl or SESSION level to enable the auto DOP strategy. Under the auto DOP strategy, the system calculates the DOP for a table based on a specific algorithm. For more information, see Enable the auto DOP strategy.
If you specify a DOP by using a global PARALLEL hint, the auto DOP strategy is enabled, or you set the parallel_degree_policy variable to enable the auto DOP strategy, the setting of the fixed DOP variable will be ignored. In other cases, 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 a fixed DOP strategy, if you enable parallel execution by using the parallel_degree_ control variable but set its value to 1, the query will use the DOP specified when the table was created to enable parallel execution for the table object.
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. You can use the ALTER TABLE statement to change the DOP of the t1 table to 4, and use the ALTER INDEX statement to change the DOP of the idx1 index to 2. Then, parallel execution will be enabled for the table object and index 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;