Parallel execution enabling methods and their priority

The OceanBase Database optimizer supports parallel execution to improve the performance of SQL queries. In the optimizer, you can use a degree of parallelism (DOP) to denote the amount of resources for parallel execution. In actual business scenarios, you need to decide whether to enable parallel execution and set an appropriate DOP based on your experience by considering the query execution situation and business requirements.

OceanBase Database allows you to enable parallel execution for queries by using multiple methods, including Auto DOP, hints, system variables, and schema DOP. However, parallel execution is not supported for a query in the following scenarios:

• The query uses PL user-defined functions (UDFs) or database links (DBLinks).
• The query uses distribute access service (DAS) to scan base tables. In this scenario, the DOP cannot be specified and is displayed as 1 in the execution plan.

If parallel execution is supported for a query, the methods for enabling parallel execution take the following precedence (from high to low):

• Configure the DOP in the table-level PARALLEL hint
• Configure the DOP in the global PARALLEL hint
• Configure the DOP in a system variable
• Configure the schema DOP

Configure the DOP in the table-level PARALLEL hint

You can directly specify the DOP for a table object in a PARALLEL hint. The basic SQL syntax is as follows:

/*+PARALLEL(@qb_name table_name dop_value)*/

For example, in query Q1, table scan is executed at a DOP of 8 on table t1 and a DOP of 4 on table t2. Here is an example:

Q1: SELECT /*+PARALLEL(t1 8) PARALLEL(t2 4)*/ * FROM t1, t2 WHERE t1.c1 = t2.c1;

If you do not specify a DOP by using the hint in a query, the DOP specified in a method with a lower priority is used. For example, in query Q2, the hint specifies a DOP, which is 8, only for table t1. In this case, if no other methods are used to enable parallel execution for table t2, the DOP of t2 is considered 1, which means that table t2 will be scanned in a non-parallel way.

Q2: SELECT /*+PARALLEL(t1 8)*/ * FROM t1, t2 WHERE t1.c1 = t2.c1;

Configure the DOP in the global PARALLEL hint

You can use the global PARALLEL hint to specify the default DOP for queries. The basic SQL syntax is as follows:

/*+PARALLEL(dop_value)*/
/*+PARALLEL(AUTO)*/
/*+PARALLEL(MANUAL)*/

For example, PARALLEL(8) in query Q3 sets DOP to 8 for both t1 and t2 tables.

You also use the global PARALLEL hint to specify the DOP obtaining strategy for queries. For example, PARALLEL(AUTO) in query Q4 specifies that all tables use the Auto DOP strategy.

Global PARALLEL hints have a lower priority than table-level PARALLEL hints. If both global and table-level PARALLEL hints are used, the global hint takes effect only for tables that do not have table-level hints. For example, in query Q5, table t1 uses the DOP 8 specified by its table-level hint, and table t2 uses the DOP 4 specified by the global hint. In query Q6, table t1 uses the DOP 8 specified by its table-level hint, and table t2 uses the DOP automatically obtained 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 the DOP in a system variable

OceanBase Database supports two system variables related to DOP, which are respectively used for configuring the DOP obtaining strategy and the fixed DOP.

You can configure the parallel_degree_policy variable at the global or session level to specify the DOP obtaining strategy. When the Auto DOP strategy is used, the DOP of a table object is automatically calculated based on a certain algorithm. For more information, see Enable Auto DOP.

If you use a global PARALLEL hint to specify a DOP or enable the Auto DOP strategy, or you use the parallel_degree_policy variable to enable the Auto DOP strategy, the fixed DOP becomes invalid. In other scenarios, you can use the following internal system variables to configure the DOP for queries or DML operations. Here are some examples:

SET _enable_parallel_query = 1;
SET _enable_parallel_dml = 1;
SET _force_parallel_query_dop = 8;
SET _force_parallel_dml_dop = 8;

Configure the schema DOP

When you use the following internal system variables to configure a fixed DOP but set the DOP to 1, parallel execution is enabled at the DOP specified during table creation.

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, DOP is set to 8 when the t1 table is created. If you use ALTER TABLE to change the DOP of the t1 table to 4 and use ALTER INDEX to set the DOP of the idx1 index to 2, the new DOP values will be used to enable parallel execution for the table and index in subsequent queries.

CREATE TABLE t1(c1 INT, c2 INT, c3 INT) PARALLEL 8;
CREATE INDEX idx1 ON t1(c1);
ALTER TABLE t1 PARALLEL 4;
ALTER INDEX idx PARALLEL 2;

References

Auto DOP