Set a DOP for parallel execution|V4.3.1|OceanBase Database| docs|Distributed Database

The degree of parallelism (DOP) refers to the number of worker threads used for the execution of a single data flow operation (DFO).

Parallel execution is designed to make full use of multi-core resources. The parallel execution (PX) framework of OceanBase Database allows you to manually specify a DOP or use a DOP automatically selected by the database.

Principle for specifying a DOP

When you specify a DOP by using the PARALLEL attribute, keep in mind that the DOP is specified to make full use of CPU resources rather than merely for the sake of a large DOP value.

Assumption

Assume that the current tenant has 20 CPU cores.

For simple single-table operations such as scan, filtering, addition, deletion, and modification, the recommended value for PARALLEL is 20.
For multi-table join queries and PDML operations involving global indexes, the recommended value for PARALLEL is 10.
For more complex execution plans in the form of a right-deep tree where the right side of the operation tree in the execution plan is deeper than the left side, the recommended value for PARALLEL is around 7.

Examples

Assume that the current tenant has 20 CPU cores.

For single-table operations with only one DFO, all the 20 CPU cores can be allocated to this DFO.

For example, if the DFO performs I/O operations most of the time, you can set the DOP to a large value, such as 25, to make full use of the CPU resources.

After tuning, the actual PARALLEL value for simple single-table operations, such as scan, filtering, addition, deletion, and modification, is 30.
For a multi-table join query, two DFOs are started at the same time to form a data pipeline, where one DFO is the producer and the other is the consumer. Each DFO can be allocated with 10 CPU cores.

It cannot be guaranteed that each DFO can make full use of the CPU cores allocated to it. Therefore, you can adjust the DOP to make full use of the CPU cores. For example, you can set the DOP to 15 in this example. We recommend that you do not simply increase the DOP. Setting the DOP to 50 will not bring actual benefits but may increase the thread scheduling overhead and framework scheduling overhead.

After tuning, the actual PARALLEL value for multi-table join queries and PDML operations involving global indexes is 15.
The following example uses a simple SQL query to describe how to set a DOP for an execution plan in the form of a right-deep tree.
```
SELECT /*+ parallel(10) */ column1, column2
FROM table1
WHERE condition1;
```
In this query, a parallel(10) hint is used so that the system uses an execution plan with a DOP of 10. Assume that 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, the deeper part is on the right side, such as the PX SEND QC and PX BLOCK ITERATOR operators. In this phase, multiple DFOs can be started. Each DFO is responsible for a part of the execution plan.

Assume that the system decides to concurrently start three DFOs in a part of this right-deep tree plan and that each DFO is allocated with seven CPU cores. This ensures that each DFO can make full use of the computing resources to improve the overall query performance.

After tuning, the DOP is set to a value ranging from 10 to 15. This leaves more space for parallel execution in the system to adapt to different DOPs required for different parts of the plan. This can improve the overall performance in processing large datasets or executing queries with a high DOP.

Manually specify a DOP

You can manually specify a DOP for a table so that the table is always scanned based on the specified DOP. The syntax for specifying a DOP varies in Oracle mode and MySQL mode.

Manually specify a DOP in MySQL mode

Specify a DOP by using a table attribute

The following statements respectively specify a DOP for a primary table and an index table.

ALTER TABLE table_name PARALLEL 4;
ALTER TABLE table_name ALTER INDEX index_name PARALLEL 2;

If an SQL statement involves only one table and the primary table is queried in the statement, not only the DFOs of the primary table but also other DFOs are executed based on a DOP of 4. If the index table is queried in the SQL statement, not only the DFOs of the index table but also other DFOs are executed based on a DOP of 2.

If an SQL statement involves multiple tables, the maximum PARALLEL value is used as the DOP for the whole execution plan of the statement.

Specify a DOP by using a PARALLEL hint

You can use a global PARALLEL hint to specify a DOP for a whole SQL statement, or use a table-level PARALLEL hint to specify a DOP for a specific table. If PARALLEL hints are specified for multiple tables in an SQL statement, the DOP of the DFOs of each table is subject to the value of the corresponding table-level PARALLEL hint. If a DFO involves multiple tables, the maximum PARALLEL value is used as the DOP for the DFO.

Here is an example of using a global hint to specify a DOP.

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

Here is an example of using table-level hints to specify DOPs:

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;
obclient [t]> 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 a DOP for a session

If you specify a DOP for the current session, all query statements in the current session are executed based on the specified DOP. Note that the specified DOP is used even for single-row query statements. This can compromise the query performance.

set _force_parallel_query_dop = 3;

For a DML statement, the preceding statement only enables parallel execution for the query part of the statement. The write part is still executed in serial. To enable parallel execution for the write part, execute the following statement:

set _force_parallel_dml_dop = 3;

If you specify a value greater than 1 for the _force_parallel_query_dop parameter, all queries in the current session are executed based on the specified DOP. If you specify 1 for the _force_parallel_query_dop parameter, parallel execution is not enabled for queries in the current session.

Note that these settings take effect for all queries in the current session, including DML statements. Proceed with caution when you use these settings and make sure that you are aware of the impact on the whole session.

Manually specify a DOP in Oracle mode

Specify a DOP by using a table attribute

The following statements respectively specify a DOP for a primary table and an index table.

ALTER TABLE table_name PARALLEL 4;
ALTER INDEX index_name PARALLEL 2;

Assume that an SQL statement involves only one table. If the primary table is queried in the statement, not only the DFOs of the primary table but also other DFOs are executed based on a DOP of 4. If the index table is queried in the SQL statement, not only the DFOs of the index table but also other DFOs are executed based on a DOP of 2.

If an SQL statement involves multiple tables, the maximum PARALLEL value is used as the DOP for the whole execution plan of the statement.

Specify a DOP by using a PARALLEL hint

You can use a global PARALLEL hint to specify a DOP for a whole SQL statement, or use a table-level PARALLEL hint to specify a DOP for a specific table.

If PARALLEL hints are specified for multiple tables in an SQL statement, the DOP of the DFOs of each table is subject to the value of the corresponding table-level PARALLEL hint. If a DFO involves multiple tables, the maximum PARALLEL value is used as the DOP for the DFO.

Here is an example of using a global hint to specify a DOP.

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       |2           |            |
| |1 | PX COORDINATOR      |        |3       |2           |            |
| |2 |  EXCHANGE OUT DISTR |:EX10000|3       |2           |            |
| |3 |   MERGE GROUP BY    |        |3       |1           |            |
| |4 |    PX BLOCK ITERATOR|        |1       |1           |            |
| |5 |     TABLE SCAN      |T1      |1       |1           |            |
| =========================================================            |
| Outputs & filters:                                                   |
| -------------------------------------                                |
|   0 - output([T_FUN_SUM(T_FUN_SUM(T1.C1))]), filter(nil), rowset=256 |
|       group(nil), agg_func([T_FUN_SUM(T_FUN_SUM(T1.C1))])            |
|   1 - output([T_FUN_SUM(T1.C1)]), filter(nil), rowset=256            |
|   2 - output([T_FUN_SUM(T1.C1)]), filter(nil), rowset=256            |
|       dop=3                                                          |
|   3 - output([T_FUN_SUM(T1.C1)]), filter(nil), rowset=256            |
|       group(nil), agg_func([T_FUN_SUM(T1.C1)])                       |
|   4 - output([T1.C1]), filter(nil), rowset=256                       |
|   5 - output([T1.C1]), filter(nil), rowset=256                       |
|       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

Here is an example of using table-level hints to specify DOPs:

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> SELECT /*+ parallel(t1 3) */ * FROM t1, t2 WHERE t1.c2 = t2.c1;
Empty set

obclient> EXPLAIN SELECT /*+ parallel(t1 3) */ * FROM t1, t2 WHERE t1.c2 = t2.c1;
+----------------------------------------------------------------------------------------+
| Query Plan                                                                             |
+----------------------------------------------------------------------------------------+
| =================================================================                      |
| |ID|OPERATOR                     |NAME    |EST.ROWS|EST.TIME(us)|                      |
| -----------------------------------------------------------------                      |
| |0 |PX COORDINATOR               |        |1       |2           |                      |
| |1 | EXCHANGE OUT DISTR          |:EX10002|1       |6           |                      |
| |2 |  HASH JOIN                  |        |1       |5           |                      |
| |3 |   EXCHANGE IN DISTR         |        |1       |2           |                      |
| |4 |    EXCHANGE OUT DISTR (HASH)|:EX10000|1       |2           |                      |
| |5 |     PX BLOCK ITERATOR       |        |1       |1           |                      |
| |6 |      TABLE SCAN             |T1      |1       |1           |                      |
| |7 |   EXCHANGE IN DISTR         |        |1       |4           |                      |
| |8 |    EXCHANGE OUT DISTR (HASH)|:EX10001|1       |4           |                      |
| |9 |     PX PARTITION ITERATOR   |        |1       |2           |                      |
| |10|      TABLE SCAN             |T2      |1       |2           |                      |
| =================================================================                      |
| Outputs & filters:                                                                     |
| -------------------------------------                                                  |
|   0 - output([INTERNAL_FUNCTION(T1.C1, T1.C2, T2.C1, T2.C2)]), filter(nil), rowset=256 |
|   1 - output([INTERNAL_FUNCTION(T1.C1, T1.C2, T2.C1, T2.C2)]), filter(nil), rowset=256 |
|       dop=3                                                                            |
|   2 - output([T1.C2], [T2.C1], [T1.C1], [T2.C2]), filter(nil), rowset=256              |
|       equal_conds([T1.C2 = T2.C1]), other_conds(nil)                                   |
|   3 - output([T1.C2], [T1.C1]), filter(nil), rowset=256                                |
|   4 - output([T1.C2], [T1.C1]), filter(nil), rowset=256                                |
|       (#keys=1, [T1.C2]), dop=3                                                        |
|   5 - output([T1.C2], [T1.C1]), filter(nil), rowset=256                                |
|   6 - output([T1.C2], [T1.C1]), filter(nil), rowset=256                                |
|       access([T1.C2], [T1.C1]), 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=256                                |
|   8 - output([T2.C1], [T2.C2]), filter(nil), rowset=256                                |
|       (#keys=1, [T2.C1]), dop=1                                                        |
|   9 - output([T2.C1], [T2.C2]), filter(nil), rowset=256                                |
|       force partition granule                                                          |
|  10 - output([T2.C1], [T2.C2]), filter(nil), rowset=256                                |
|       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                      |
+----------------------------------------------------------------------------------------+
39 rows in set

For a DML statement, the preceding hint only enables parallel execution for the query part of the statement. The write part is still executed in serial. To enable parallel execution for the write part, you must add the hint ENABLE_PARALLEL_DML. Here is an example.

INSERT /*+ parallel(3) enable_parallel_dml */ INTO t3 SELECT * FROM t1;

Notice

You must specify a global PARALLEL hint for PDML. A table-level PARALLEL hint cannot enable parallel execution for the write part. For example, the following SQL statement does not enable parallel execution for DML statements: INSERT /*+ parallel(t3 3) enable_parallel_dml */ INTO t3 SELECT * FROM t1;

Specify a DOP for a session

ALTER SESSION FORCE PARALLEL QUERY PARALLEL 3;

ALTER SESSION FORCE PARALLEL DML PARALLEL 3;

You can use the following statement to disable parallel execution for a session:

ALTER SESSION DISABLE PARALLEL QUERY;

Auto DOP

For more information about auto DOP, see Auto DOP.

DOP priorities

The priorities of DOPs specified in different ways are sorted in descending order as follows: DOP specified by a table-level PARALLEL hint > DOP specified by a global hint > DOP specified for a session > DOP specified for a table.

The following example shows that when a table-level PARALLEL hint is specified, a 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

The following example shows that when a table-level PARALLEL hint is specified, the DOP specified for a session 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

The following example shows that a DOP specified for a specific table has a lower priority than a DOP specified for a session.

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

OceanBase

Customer Stories

Documentation

Set a DOP for parallel execution

Principle for specifying a DOP

Assumption

Examples

Manually specify a DOP

Manually specify a DOP in MySQL mode

Specify a DOP by using a table attribute

Specify a DOP by using a PARALLEL hint

Specify a DOP for a session

Manually specify a DOP in Oracle mode

Specify a DOP by using a table attribute

Specify a DOP by using a PARALLEL hint

Notice

Specify a DOP for a session

Auto DOP

DOP priorities