In OceanBase Database, parallel execution (PX) performance is determined by the values of PX parameters, including the EXCHANGE parameter and those related to the degree of parallelism (DOP).
DOP parameters
DOP parameters determine the number of parallel workers that execute an SQL statement. For more information, see the following table.
Parameter |
Description |
Value range |
Default value |
Recommendation |
|---|---|---|---|---|
| parallel_servers_target | Before an SQL statement is queued, the system checks whether the sum of the DOP required by this statement and the total number of existing parallel workers exceed the limit specified by this parameter. If yes, the system queues the statement. Otherwise, the system proceeds with the statement execution. | [0, 1800] | 10. The value is determined based on the actual number of CPUs. |
This parameter controls whether to proceed with the execution of a statement or queue it by limiting the number of available workers in PX scenarios. |
| _force_parallel_query_dop | Specifies the default DOP of an SQL query in a session. If no PARALLEL hint is specified, the DOP of an SQL query is subject to this parameter. | [1, +∞] | 1 |
You can set this parameter as needed. For example, if you want to execute some SQL queries in parallel but do not want to add a PARALLEL hint to each of them, we recommend that you set this parameter. |
| _force_parallel_dml_dop | Specifies the default DOP of a DML statement in a session. If no PARALLEL hint is specified, the DOP of a DML statement is subject to this parameter. | [1,+∞] | 1 |
You can set this parameter as needed. For example, if you want to execute some DML statements in parallel but do not want to add a PARALLEL hint to each of them, we recommend that you set this parameter. |
View the values of DOP parameters
You can use the SHOW VARIABLES command to view the values of DOP parameters in OceanBase Database. Note that some parameters are hidden variables, which we recommend that you do not modify.
Query session variables
_force_parallel_query_dop and _force_parallel_dml_dop are hidden session variables. You can query their values as follows:
-- Query the variables from the oceanbase.DBA_OB_SYS_VARIABLES table
SELECT * FROM oceanbase.DBA_OB_SYS_VARIABLES WHERE name LIKE '%_force_parallel%';
-- Query the variables as system variables
SELECT @@_force_parallel_query_dop;
By using the preceding methods, you can query the values of hidden parameters. However, we recommend that you do not modify these parameters.
Query the parameters in the sys tenant
If you have logged on to the sys tenant, you can also query DOP parameters by executing the following command:
SELECT tenant_id, name, value
FROM oceanbase.CDB_OB_SYS_VARIABLES
WHERE name LIKE '%_force_parallel%';
EXCHANGE (shuffle) parameters
EXCHANGE (shuffle) parameters specify the memory for data shuffle between data flow objects (DFOs). OceanBase Database encapsulates its data transfer service into a module called Data Transfer Layer (DTL).
Parameter |
Description |
Value range |
Default value |
Recommendation |
|---|---|---|---|---|
| dtl_buffer_size | Specifies the size of the buffer for each data transmission operation between EXCHANGE operators, namely the Transmit and Receive operators. Data is sent only when the data size reaches the upper limit specified by this parameter. This reduces the cost of transmitting each row. | [0, 1800] | 10. The value is determined based on the actual number of CPUs. |
This parameter controls the data transmission between EXCHANGE operators in PX scenarios. We recommend that you do not modify this parameter unless you want to reduce the frequency of data transmission. |
You can use SHOW PARAMETERS to view the values of these parameters, as shown in the following sample statement:
obclient> SHOW PARAMETERS LIKE '%dtl%';
+-------+----------+----------------+----------+-----------------+-----------+-------+---------------+----------+---------+---------+-------------------+---------------+-----------+
| zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level | default_value | isdefault |
+-------+----------+----------------+----------+-----------------+-----------+-------+---------------+----------+---------+---------+-------------------+---------------+-----------+
| zone1 | observer | 172.xx.xxx.xxx | 2882 | dtl_buffer_size | CAPACITY | 64K | to be removed | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | 64K | 1 |
+-------+----------+----------------+----------+-----------------+-----------+-------+---------------+----------+---------+---------+-------------------+---------------+-----------+
1 row in set
Other PX parameters
Parameter |
Description |
Value range |
Default value |
Recommendation |
|---|---|---|---|---|
| _enable_px_batch_rescan | Specifies whether to enable BATCH RESCAN for better performance when you use the nested loop join (NLJ) algorithm to generate a distributed PX RESCAN plan. | True or False |
True |
By default, BATCH RESCAN is enabled. It provides better performance but consumes more memory resources. |
| _bloom_filter_enabled | Specifies whether to enable Bloom filters when you use the hash join algorithm. | True or False |
True |
Bloom filters are automatically enabled when the DOP exceeds 1. If the join condition of a hash join has poor filtering performance, Bloom filters may bring additional overhead. In this case, you can disable Bloom filters. |