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 mainly determine the number of Workers for each query during concurrent execution. For more information, see the following table.
| Parameter name | Description | Value range | Default value | Recommendation |
|---|---|---|---|---|
| parallel_servers_target | Before queuing, controls whether the sum of the DOP required by the query and the total number of existing Workers exceeds this value. If it exceeds, the query needs to be queued; otherwise, the query continues to execute. | [0, 1800] | 10 (currently calculated based on the number of CPUs; the actual value shall prevail) | This parameter mainly controls whether to proceed or wait in queue when there are not enough Workers to handle the query during parallel execution in PX scenarios. |
| _force_parallel_query_dop | Specifies the default DOP of query SQL in a session. When no PARALLEL hint is specified, the DOP of query SQL is controlled by this parameter. | [1, +∞] | 1 | Set as needed. For example, when you want to run a batch of parallel query SQL in the same session without manually adding a Hint to each SQL, we recommend using this parameter. |
| _force_parallel_dml_dop | Specifies the default DOP of DML SQL in a session. When no PARALLEL hint is specified, the DOP of DML SQL is controlled by this parameter. | [1,+∞] | 1 | Set as needed. For example, when you want to run a batch of parallel DML SQL without manually adding a Hint to each SQL, we recommend using 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 using the DBA_OB_SYS_VARIABLES table
SELECT * FROM oceanbase.DBA_OB_SYS_VARIABLES WHERE name LIKE '%_force_parallel%';
-- Query using 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
The EXCHANGE (shuffle) parameters are mainly used to control memory management and data redistribution strategies during data transfer between distributed flow operators (DFOs). OceanBase Database encapsulates this data transfer process within the Data Transfer Layer (DTL) module.
| Parameter name | Description | Value range | Default value | Recommendation |
|---|---|---|---|---|
| dtl_buffer_size | Controls the buffer size for each data transmission between EXCHANGE operators (between Transmit and Receive). Data is sent only when it reaches the upper limit of this value, reducing the cost of transmitting each row. | [4k, 2M] | 64K | In PX scenarios, data transmission between EXCHANGE operators depends on this parameter. Generally, you do not need to adjust it. Modify it only if you want to reduce the number of data transmissions. We generally do not recommend modifying this value. |
You can use SHOW PARAMETERS to view the values of these parameters, as shown in the following sample statement:
obclient> SHOW PARAMETERS LIKE '%dtl%';
Expected result:
+-------+----------+----------------+----------+-----------------+-----------+-------+---------------+----------+---------+---------+-------------------+---------------+-----------+
| 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 name | Description | Value range | Default value | Recommendation |
|---|---|---|---|---|
| _enable_px_batch_rescan | Controls whether to use BATCH RESCAN when NLJ generates a distributed PX RESCAN plan for better performance. | True or False | True | Enabled by default for better performance, but consumes more memory. |
| _bloom_filter_enabled | Controls whether to enable BLOOM FILTER in HASH JOIN scenarios. | True or False | True | Enabled by default when DOP is greater than 1. If the join condition of HASH JOIN has poor filtering performance, enabling BLOOM FILTER may add overhead. In such scenarios, you can consider disabling this feature. |