The degree of parallelism (DOP) refers to the number of worker threads used by a single data flow operation (DFO) during execution.
Parallel execution is designed to efficiently utilize multi-core resources. The OceanBase parallel execution framework provides various ways to specify the DOP, allowing you to set it manually or let the database automatically choose it.
Suggestions on DOP values
A simple principle is that the purpose of setting PARALLEL is to fully utilize CPU resources, not simply to pursue the maximum value.
Examples
Assume the tenant has 20 CPUs:
- For simple single-table operations (such as single-table scan filtering, single-table insert, delete, and update), the theoretical value of
PARALLELcan be set to 20. - For multi-table
JOINqueries and PDML with global indexes, the theoretical value ofPARALLELcan be set to 10. - For some more complex plans that are right-deep trees (i.e., parts of the operation tree on the right side are deeper), the theoretical value of
PARALLELcan be set to around 7.
Example explanation
Assuming the current tenant has 20 CPUs:
For single-table operations, there is only one DFO, and all 20 CPUs can be allocated to this DFO.
For example, if this DFO spends most of its time performing I/O operations, you can set the DOP slightly higher, such as 25, to fully utilize the CPU.
After tuning, for simple single-table operations (such as single-table scan filtering, single-table insert, delete, and update), the actual
PARALLELvalue is 30.For multi-table
JOIN, two DFOs are started simultaneously to form a data pipeline (one DFO is the producer, and the other is the consumer). Therefore, 10 CPUs can be allocated to each DFO.In reality, it cannot be guaranteed that each DFO will use up all the CPUs allocated to it. Therefore, you can appropriately adjust the DOP, for example, to 15, to make more efficient use of the CPU. However, the DOP should not be increased indefinitely. Setting it to 50 does not bring substantial benefits; instead, it increases thread scheduling overhead and framework scheduling overhead.
After tuning, for multi-table
JOINqueries and PDML with global indexes, the actualPARALLELvalue is 15.For right-deep tree plans, let's use a simple SQL query as an example to illustrate the situation:
SELECT /*+ parallel(10) */ column1, column2 FROM table1 WHERE condition1;In this query, we use the
parallel(10)hint, hoping the system will execute the plan with a DOP of 10. Assume the execution plan is as follows:====================================================================== | ID | OPERATOR | NAME | EST.ROWS | EST.TIME(us)| ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | PX COORDINATOR | | | | | 2 | PX SEND QC (RANDOM) | :EX10000| | | | 3 | PX BLOCK ITERATOR | | N rows | | | 4 | TABLE ACCESS (FULL) | table1 | N rows | | ======================================================================In this execution plan, we can see that the right-deep tree part is on the right side of the operation tree, for example, the parts with operation symbols
PX SEND QCandPX BLOCK ITERATOR. At this stage, multiple DFOs may be started, with each DFO responsible for a part of the execution plan.Suppose the system decides to start three DFOs simultaneously at a certain local level of this right-deep tree plan, with each DFO allocated 7 CPUs. The purpose of this is to ensure each DFO can fully utilize computing resources and improve overall query performance.
After tuning, by setting the DOP to 10–15, we provide the system with a larger parallel execution space to accommodate the DOP requirements that different parts of the plan might need. Such adjustments can improve overall performance when processing large datasets or queries requiring high-degree parallel execution.
Manually specify the DOP
You can manually specify the DOP for a table to ensure parallel execution is always used when scanning that table. The syntax is as follows.
Specify the DOP by using table attributes
The following statements separately specify the scan DOP for the primary table and an index.
ALTER TABLE table_name PARALLEL 4;
ALTER TABLE table_name ALTER INDEX index_name PARALLEL 2;
If an SQL statement involves only one table, when the primary table is queried, all DFOs except the one where the primary table resides will use a DOP of 4. When an index table is queried, all DFOs except the one where the index table resides will use a DOP of 2.
If an SQL statement involves multiple tables, the maximum value of PARALLEL is used as the DOP for the entire plan.
Specify the DOP by using the PARALLEL hint
You can use the global PARALLEL hint to specify the DOP for the entire SQL statement, or use the table-level PARALLEL hint to specify the DOP for a specific table. If the PARALLEL hint is given for multiple tables in an SQL statement, the DOP for each DFO containing those tables is determined by the respective table's PARALLEL value. If a DFO contains multiple tables, the maximum value of their PARALLEL settings is used as the DOP for that DFO.
Global hint:
obclient> CREATE TABLE t1 (c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> EXPLAIN SELECT /*+ parallel(3) */ SUM(c1) FROM t1;
+---------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------+
| ============================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------- |
| |0 |SCALAR GROUP BY | |1 |3 | |
| |1 |└─PX COORDINATOR | |3 |3 | |
| |2 | └─EXCHANGE OUT DISTR |:EX10000|3 |2 | |
| |3 | └─MERGE GROUP BY | |3 |2 | |
| |4 | └─PX BLOCK ITERATOR| |1 |2 | |
| |5 | └─TABLE FULL SCAN|t1 |1 |2 | |
| ============================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T_FUN_SUM(T_FUN_SUM(t1.c1))]), filter(nil), rowset=16 |
| group(nil), agg_func([T_FUN_SUM(T_FUN_SUM(t1.c1))]) |
| 1 - output([T_FUN_SUM(t1.c1)]), filter(nil), rowset=16 |
| 2 - output([T_FUN_SUM(t1.c1)]), filter(nil), rowset=16 |
| dop=3 |
| 3 - output([T_FUN_SUM(t1.c1)]), filter(nil), rowset=16 |
| group(nil), agg_func([T_FUN_SUM(t1.c1)]) |
| 4 - output([t1.c1]), filter(nil), rowset=16 |
| 5 - output([t1.c1]), filter(nil), rowset=16 |
| access([t1.c1]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
+---------------------------------------------------------------------+
24 rows in set
Table-level hint:
obclient> CREATE TABLE t1 (c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> CREATE TABLE t2 (c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> EXPLAIN SELECT /*+ parallel(t1 3)+ parallel(t2 2)*/ * FROM t1, t2 WHERE t1.c1 = t2.c1;
+---------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------+
| ======================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------------ |
| |0 |PX COORDINATOR | |1 |6 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10001|1 |5 | |
| |2 | └─SHARED HASH JOIN | |1 |4 | |
| |3 | ├─EXCHANGE IN DISTR | |1 |2 | |
| |4 | │ └─EXCHANGE OUT DISTR (BC2HOST)|:EX10000|1 |2 | |
| |5 | │ └─PX BLOCK ITERATOR | |1 |2 | |
| |6 | │ └─TABLE FULL SCAN |t1 |1 |2 | |
| |7 | └─PX BLOCK ITERATOR | |1 |2 | |
| |8 | └─TABLE FULL SCAN |t2 |1 |2 | |
| ======================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t2.c1, t2.c2)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t2.c1, t2.c2)]), filter(nil), rowset=16 |
| dop=2 |
| 2 - output([t1.c1], [t2.c1], [t1.c2], [t2.c2]), filter(nil), rowset=16 |
| equal_conds([t1.c1 = t2.c1]), other_conds(nil) |
| 3 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 |
| 4 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 |
| dop=3 |
| 5 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 |
| 6 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 |
| access([t1.c1], [t1.c2]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
| 7 - output([t2.c1], [t2.c2]), filter(nil), rowset=16 |
| 8 - output([t2.c1], [t2.c2]), filter(nil), rowset=16 |
| access([t2.c1], [t2.c2]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t2.__pk_increment]), range(MIN ; MAX)always true |
+---------------------------------------------------------------------------------------+
33 rows in set
Specify the DOP at the session level
Once the DOP is specified for the current session, all query statements in that session will be executed with the specified DOP. Note that even single-row queries will use this DOP, which may result in performance degradation.
set _force_parallel_query_dop = 3;
For DML statements, using the preceding command only enables parallel execution for the query part of the DML statement; the write part remains serial. To enable parallel execution for both the query and write parts, use the following command:
set _force_parallel_dml_dop = 3;
When _force_parallel_query_dop is set to a value greater than 1, queries in the current session are executed with a DOP greater than 1. When _force_parallel_query_dop is set to 1, parallel execution is disabled for SQL statements in the current session.
Note that these settings apply globally to all queries in the session, including DML statements. Therefore, use them cautiously and understand their impact on the overall session execution.
Automatic DOP
For more information about automatic DOP, see Auto DOP.
DOP priority
TABLE HINT > Global hint > Session-level DOP > Table DOP
Compare the priorities of global hints and table hints. As you can see, when a table hint is used, the global hint does not take effect.
obclient> CREATE TABLE t1 (c1 int primary key, c2 int);
obclient> EXPLAIN SELECT /*+ parallel(3) parallel(t1 5) */ * FROM t1;
+-------------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------------+
| ======================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |1 | |
| |1 | EXCHANGE OUT DISTR|:EX10000|1 |21 | |
| |2 | PX BLOCK ITERATOR| |1 |1 | |
| |3 | TABLE SCAN |T1 |1 |1 | |
| ======================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2)]), filter(nil), rowset=16 |
| dop=5 |
| 2 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 |
| 3 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 |
| access([t1.c1], [t1.c2]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.c1]), range(MIN ; MAX)always true |
+-------------------------------------------------------------------------+
18 rows in set
obclient> EXPLAIN SELECT /*+ parallel(t1 3) */ * FROM t1;
+-------------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------------+
| ======================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |2 | |
| |1 | EXCHANGE OUT DISTR|:EX10000|1 |1 | |
| |2 | PX BLOCK ITERATOR| |1 |1 | |
| |3 | TABLE SCAN |T1 |1 |1 | |
| ======================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2)]), filter(nil), rowset=16 |
| dop=3 |
| 2 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 |
| 3 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 |
| access([t1.c1], [t1.c2]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.c1]), range(MIN ; MAX)always true |
+-------------------------------------------------------------------------+
18 rows in set
Compare the priorities of session-level and table-level hints. As you can see, when a table-level hint is used, the DOP specified at the session level does not take effect.
obclient> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;
Query OK, 0 rows affected (0.001 sec)
obclient> EXPLAIN SELECT /*+ parallel(t1 3) */ * FROM t1;
+-------------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------------+
| ========================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |3 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000|1 |2 | |
| |2 | └─PX BLOCK ITERATOR| |1 |2 | |
| |3 | └─TABLE FULL SCAN|T1 |1 |2 | |
| ========================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(T1.C1, T1.C2)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(T1.C1, T1.C2)]), filter(nil), rowset=16 |
| dop=3 |
| 2 - output([T1.C1], [T1.C2]), filter(nil), rowset=16 |
| 3 - output([T1.C1], [T1.C2]), filter(nil), rowset=16 |
| access([T1.C1], [T1.C2]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T1.C1]), range(MIN ; MAX)always true |
+-------------------------------------------------------------------------+
18 rows in set
obclient> EXPLAIN SELECT * FROM t1;
+-------------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------------+
| ========================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |2 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000|1 |2 | |
| |2 | └─PX BLOCK ITERATOR| |1 |1 | |
| |3 | └─TABLE FULL SCAN|T1 |1 |1 | |
| ========================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(T1.C1, T1.C2)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(T1.C1, T1.C2)]), filter(nil), rowset=16 |
| dop=4 |
| 2 - output([T1.C1], [T1.C2]), filter(nil), rowset=16 |
| 3 - output([T1.C1], [T1.C2]), filter(nil), rowset=16 |
| access([T1.C1], [T1.C2]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T1.C1]), range(MIN ; MAX)always true |
+-------------------------------------------------------------------------+
18 rows in set
Compare the priorities of the SESSION and TABLE PARALLEL hints. As you can see, the TABLE PARALLEL hint has a lower priority than the DOP specified by the SESSION hint.
obclient> ALTER TABLE t1 PARALLEL 5;
Query OK, 0 rows affected
obclient> EXPLAIN SELECT * FROM t1;
+-------------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------------+
| ========================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |2 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000|1 |2 | |
| |2 | └─PX BLOCK ITERATOR| |1 |1 | |
| |3 | └─TABLE FULL SCAN|T1 |1 |1 | |
| ========================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(T1.C1, T1.C2)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(T1.C1, T1.C2)]), filter(nil), rowset=16 |
| dop=4 |
| 2 - output([T1.C1], [T1.C2]), filter(nil), rowset=16 |
| 3 - output([T1.C1], [T1.C2]), filter(nil), rowset=16 |
| access([T1.C1], [T1.C2]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T1.C1]), range(MIN ; MAX)always true |
+-------------------------------------------------------------------------+
18 rows in set
obclient> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;
Query OK, 0 rows affected
obclient> EXPLAIN SELECT * FROM t1;
+-------------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------------+
| ========================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |2 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000|1 |2 | |
| |2 | └─PX BLOCK ITERATOR| |1 |1 | |
| |3 | └─TABLE FULL SCAN|T1 |1 |1 | |
| ========================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(T1.C1, T1.C2)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(T1.C1, T1.C2)]), filter(nil), rowset=16 |
| dop=4 |
| 2 - output([T1.C1], [T1.C2]), filter(nil), rowset=16 |
| 3 - output([T1.C1], [T1.C2]), filter(nil), rowset=16 |
| access([T1.C1], [T1.C2]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T1.C1]), range(MIN ; MAX)always true |
+-------------------------------------------------------------------------+
18 rows in set