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 requirements and business needs, with rich experience as the basis.
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 that involve PL UDFs or dblink.
- For base tables scanned through DAS, the system sets the DOP to 1 and does not support specifying the DOP.
If parallel execution is supported for a query, the DOP of the query can be set by using the following methods in descending order of priority:
- DOP specified in the table-level hint
- DOP specified in the global hint
- DOP specified in the system variable
- 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)*/
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 that do not have a DOP specified in the hint, the system determines the DOPs 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 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 )*/
For example, in query Q3, the PARALLEL( 8 ) hint specifies to use a DOP of 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 the table-level hint. When both global and table-level hints are present, the global hint takes effect only for tables that do not have a table-level hint. For example, in query Q5, the t1 table uses a DOP of 8 due to the table-level hint, and the t2 table uses a DOP of 4 due to the global hint. In query Q6, the t1 table uses a DOP of 8 due to the table-level hint, and the t2 table uses a DOP automatically determined 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 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 by using a global PARALLEL hint, an auto DOP strategy, or the parallel_degree_policy variable, the fixed DOP you specify will be ineffective. In other cases, you can set the following internal system variables to configure the DOP for parallel execution of a query or DML statement. 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, 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 for parallel execution of 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. 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 will be enabled for the table object and the index with the DOPs of 4 and 2, respectively, after the changes.
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;