Parallel execution divides a large task into multiple smaller tasks and starts multiple threads or processes to process these small tasks in parallel. This allows more CPU and I/O resources to be utilized, thereby reducing the response time of operations.
Parallel execution is divided into parallel query, parallel DDL, and parallel DML.
There are two ways to start a parallel query:
Start a parallel query by specifying the degree of parallelism (DOP) using the
PARALLELhint.Parallel queries are automatically started for partitioned tables with more than one partition.
Enable parallel query for partitioned tables
For queries on partitioned tables, if the number of target partitions is greater than 1, the system automatically enables parallel query. The dop value is set to 1 by default.
As shown in the following example, create a partitioned table named ptable, scan all data in ptable, and view the generated execution plan using the EXPLAIN command.
obclient> CREATE TABLE PTABLE(c1 INT , c2 INT) PARTITION BY HASH(c1) PARTITIONS 16;
Query OK, 0 rows affected
obclient> EXPLAIN SELECT * FROM ptable;
Query Plan:
=======================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
-------------------------------------------------------
|0 |EXCHANGE IN DISTR | |1600000 |1246946|
|1 | EXCHANGE OUT DISTR |:EX10000|1600000 |1095490|
|2 | PX PARTITION ITERATOR| |1600000 |1095490|
|3 | TABLE SCAN |ptable |1600000 |1095490|
=======================================================
Outputs & filters:
-------------------------------------
0 - output([ptable.c1], [ptable.c2]), filter(nil)
1 - output([ptable.c1], [ptable.c2]), filter(nil), dop=1
2 - output([ptable.c1], [ptable.c2]), filter(nil)
3 - output([ptable.c1], [ptable.c2]), filter(nil),
access([ptable.c1], [ptable.c2]), partitions(p[0-15])
The execution plan shows that the default dop for parallel query on a partitioned table is 1. If the OceanBase AI Database cluster has three OBServer nodes, and the 16 partitions of table ptable are distributed across these three OBServer nodes, then each OBServer node will start one worker thread to scan its partitions. A total of three worker threads are required to complete the table scan.
For a partitioned table, add the PARALLEL hint to start parallel query and specify a dop value. View the generated execution plan using the EXPLAIN command.
obclient> EXPLAIN SELECT /*+ PARALLEL(8) */ * FROM ptable;
Query Plan:
=======================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
-------------------------------------------------------
|0 |EXCHANGE IN DISTR | |1600000 |1246946|
|1 | EXCHANGE OUT DISTR |:EX10000|1600000 |1095490|
|2 | PX PARTITION ITERATOR| |1600000 |1095490|
|3 | TABLE SCAN |ptable |1600000 |1095490|
=======================================================
Outputs & filters:
-------------------------------------
0 - output([ptable.c1], [ptable.c2]), filter(nil)
1 - output([ptable.c1], [ptable.c2]), filter(nil), dop=8
2 - output([ptable.c1], [ptable.c2]), filter(nil)
3 - output([ptable.c1], [ptable.c2]), filter(nil),
access([ptable.c1], [ptable.c2]), partitions(p[0-15])
The execution plan shows that the dop value for the parallel query is 8. If the number of OBServer nodes containing the queried partitions is less than or equal to the dop value, the worker threads (with a total number equal to the dop value) are allocated to the involved OBServer nodes according to a specific strategy. If the number of OBServer nodes containing the queried partitions is greater than the dop value, then each OBServer node starts at least one worker thread, and the total number of worker threads started will be greater than the dop value.
For example, when dop=8, if the 16 partitions are evenly distributed across four OBServer nodes, then each OBServer node starts two worker threads to scan its corresponding partitions (a total of eight worker threads). If the 16 partitions are distributed across 16 OBServer nodes (one partition per node), then each OBServer node starts one worker thread to scan its corresponding partition (a total of sixteen worker threads).
If the number of queried partitions for a partitioned table is less than or equal to 1, the system does not start a parallel query. As shown in the following example, add a filter condition c1=1 to the query for ptable.
obclient> EXPLAIN SELECT * FROM ptable WHERE c1 = 1;
Query Plan:
======================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
--------------------------------------
|0 |TABLE SCAN|ptable|990 |85222|
======================================
Outputs & filters:
-------------------------------------
0 - output([ptable.c1], [ptable.c2]), filter([ptable.c1 = 1]),
access([ptable.c1], [ptable.c2]), partitions(p1)
The plan shows that the number of target partitions is 1, and the system did not start a parallel query. If you want to enable parallel execution for queries targeting a single partition, you must use the PARALLEL hint to perform a parallel query within the partition. View the generated execution plan using the EXPLAIN command.
obclient> EXPLAIN SELECT /*+ PARALLEL(8) */ * FROM ptable WHERE c1 = 1;
Query Plan:
=================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
-------------------------------------------------
|0 |EXCHANGE IN DISTR | |990 |85316|
|1 | EXCHANGE OUT DISTR|:EX10000|990 |85222|
|2 | PX BLOCK ITERATOR| |990 |85222|
|3 | TABLE SCAN |ptable |990 |85222|
=================================================
Outputs & filters:
-------------------------------------
0 - output([ptable.c1], [ptable.c2]), filter(nil)
1 - output([ptable.c1], [ptable.c2]), filter(nil), dop=8
2 - output([ptable.c1], [ptable.c2]), filter(nil)
3 - output([ptable.c1], [ptable.c2]), filter([ptable.c1 = 1]),
access([ptable.c1], [ptable.c2]), partitions(p1)
Notice
- If you want to use hints for parallel query within a single partition when the number of queried partitions equals 1, the corresponding
dopvalue must be greater than or equal to 2. - If the
dopvalue is empty or less than 2, parallel query will not be started.
Enable parallel query for non-partitioned tables
A non-partitioned table is essentially a partitioned table with only one partition. Therefore, for queries on a non-partitioned table, you can only enable intra-partition parallel query execution by adding the PARALLEL hint; otherwise, parallel query execution will not be initiated.
As shown in the following example, create a non-partitioned table named stable, perform a full-table data scan on stable, and view the generated execution plan using the EXPLAIN command.
obclient> CREATE TABLE stable(c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> EXPLAIN SELECT * FROM stable;
Query Plan:
======================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
--------------------------------------
|0 |TABLE SCAN|stable|100000 |68478|
======================================
Outputs & filters:
-------------------------------------
0 - output([stable.c1], [stable.c2]), filter(nil),
access([stable.c1], [stable.c2]), partitions(p0)
The execution plan shows that, without using a hint for a non-partitioned table, parallel query execution is not initiated.
For a non-partitioned table, add the PARALLEL hint to enable intra-partition parallel query execution and specify a DOP value (greater than or equal to 2). Then, use the EXPLAIN command to view the generated execution plan.
obclient> EXPLAIN SELECT /*+ PARALLEL(4)*/ * FROM stable;
Query Plan:
=================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
-------------------------------------------------
|0 |EXCHANGE IN DISTR | |100000 |77944|
|1 | EXCHANGE OUT DISTR|:EX10000|100000 |68478|
|2 | PX BLOCK ITERATOR| |100000 |68478|
|3 | TABLE SCAN |stable |100000 |68478|
=================================================
Outputs & filters:
-------------------------------------
0 - output([stable.c1], [stable.c2]), filter(nil)
1 - output([stable.c1], [stable.c2]), filter(nil), dop=4
2 - output([stable.c1], [stable.c2]), filter(nil)
3 - output([stable.c1], [stable.c2]), filter(nil),
access([stable.c1], [stable.c2]), partitions(p0)
Enable multi-table parallel query
In queries, multi-table JOIN operations are the most common.
As shown in the following example, first create two partitioned tables named p1table and p2table:
obclient> CREATE TABLE p1table(c1 INT ,c2 INT) PARTITION BY HASH(c1) PARTITIONS 2;
Query OK, 0 rows affected
obclient> CREATE TABLE p2table(c1 INT ,c2 INT) PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected
Query the JOIN result of p1table and p2table. The JOIN condition is p1table.c1=p2table.c2. The execution plan is as follows:
obclient> EXPLAIN SELECT * FROM p1table p1 JOIN p2table p2 ON p1.c1=p2.c2;
Query Plan:
====================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
--------------------------------------------------------------------
|0 |EXCHANGE IN DISTR | |784080000|614282633|
|1 | EXCHANGE OUT DISTR |:EX10001|784080000|465840503|
|2 | HASH JOIN | |784080000|465840503|
|3 | EXCHANGE IN DISTR | |200000 |155887 |
|4 | EXCHANGE OUT DISTR (BROADCAST)|:EX10000|200000 |136955 |
|5 | PX PARTITION ITERATOR | |200000 |136955 |
|6 | TABLE SCAN |p1 |200000 |136955 |
|7 | PX PARTITION ITERATOR | |400000 |273873 |
|8 | TABLE SCAN |p2 |400000 |273873 |
====================================================================
Outputs & filters:
-------------------------------------
0 - output([p1.c1], [p1.c2], [p2.c1], [p2.c2]), filter(nil)
1 - output([p1.c1], [p1.c2], [p2.c1], [p2.c2]), filter(nil), dop=1
2 - output([p1.c1], [p1.c2], [p2.c1], [p2.c2]), filter(nil),
equal_conds([p1.c1 = p2.c2]), other_conds(nil)
3 - output([p1.c1], [p1.c2]), filter(nil)
4 - output([p1.c1], [p1.c2]), filter(nil), dop=1
5 - output([p1.c1], [p1.c2]), filter(nil)
6 - output([p1.c1], [p1.c2]), filter(nil),
access([p1.c1], [p1.c2]), partitions(p[0-1])
7 - output([p2.c1], [p2.c2]), filter(nil)
8 - output([p2.c1], [p2.c2]), filter(nil),
access([p2.c1], [p2.c2]), partitions(p[0-3])
By default, parallel query is used for queries on p1table and p2table (both tables have more than one partition to query), and the default DOP value is 1. Similarly, you can also change the degree of parallelism by using the PARALLEL hint.
As shown in the following example, change the JOIN condition to p1table.c1=p2table.c2 and p2table.c1=1. In this way, only a single partition of p2table is selected. The execution plan is as follows:
obclient> EXPLAIN SELECT * FROM p1table p1 JOIN p2table p2 ON p1.c1=p2.c2 AND p2.c1=1;
Query Plan:
=============================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
-------------------------------------------------------------
|0 |EXCHANGE IN DISTR | |1940598 |1807515|
|1 | EXCHANGE OUT DISTR |:EX10001|1940598 |1440121|
|2 | HASH JOIN | |1940598 |1440121|
|3 | EXCHANGE IN DISTR | |990 |85316 |
|4 | EXCHANGE OUT DISTR (PKEY)|:EX10000|990 |85222 |
|5 | TABLE SCAN |p2 |990 |85222 |
|6 | PX PARTITION ITERATOR | |200000 |136955 |
|7 | TABLE SCAN |p1 |200000 |136955 |
=============================================================
Outputs & filters:
-------------------------------------
0 - output([p1.c1], [p1.c2], [p2.c1], [p2.c2]), filter(nil)
1 - output([p1.c1], [p1.c2], [p2.c1], [p2.c2]), filter(nil), dop=1
2 - output([p1.c1], [p1.c2], [p2.c1], [p2.c2]), filter(nil),
equal_conds([p1.c1 = p2.c2]), other_conds(nil)
3 - output([p2.c1], [p2.c2]), filter(nil)
4 - (#keys=1, [p2.c2]), output([p2.c1], [p2.c2]), filter(nil), dop=1
5 - output([p2.c1], [p2.c2]), filter([p2.c1 = 1]),
access([p2.c1], [p2.c2]), partitions(p1)
6 - output([p1.c1], [p1.c2]), filter(nil)
7 - output([p1.c1], [p1.c2]), filter(nil),
access([p1.c1], [p1.c2]), partitions(p[0-1])
The plan shows that p2table only needs to scan one partition and does not use parallel query by default; p1table needs to scan two partitions and uses parallel query by default. Similarly, you can also change the degree of parallelism by adding the PARALLEL hint, turning the query for p2table on one partition into an intra-partition parallel query.
System views related to parallel execution
OceanBase AI Database provides the system view (G)V$OB_SQL_AUDIT to view the running status and some statistics of parallel execution.
(G)V$OB_SQL_AUDIT contains many fields. The fields related to parallel execution are: qc_id, dfo_id, sqc_id, and worker_id.
For more information, see GV$OB_SQL_AUDIT.