The optimizer of OceanBase Database can optimize SQL queries by using 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 by using 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/UDF or dblink.
- Parallel execution is not supported for base tables scanned by using 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 enabling 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 DOP = 8 and DOP = 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 DOP = 8. If parallel execution is not enabled for table t2, it will be scanned with DOP = 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 PARALLEL( 8 ) hint specifies to use DOP = 8 for both tables t1 and t2.
You can also use a global hint to specify the DOP acquisition strategy for a query. For example, in query Q4, the PARALLEL ( AUTO ) hint 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 DOP = 8 for table t1 and DOP = 4 for table t2. In query Q6, a table-level hint specifies to use DOP = 8 for table t1, and the auto DOP strategy is used to determine the 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, the auto DOP strategy is enabled, or the parallel_degree_policy variable is set to enable the auto DOP strategy, the fixed DOP becomes ineffective. 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 following internal system variables but set the DOP 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 the t1 table was created is 8. You can use the ALTER TABLE statement to change the DOP for the t1 table to 4, and use the ALTER INDEX statement to change the DOP for the idx1 index to 2. Then, parallel execution will be enabled for the table object and index based on the changed 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;