Parallel execution is a deep and complex topic that requires a certain period of study before you can accurately understand the mechanisms of concurrent operations and fully leverage its capabilities. To help beginners quickly grasp the essentials of parallel execution, we provide an introductory practice guide. Although the parameters provided in this article are not optimal choices, they can effectively avoid most adverse situations, enabling beginners to more easily apply parallel execution features.
Initialize the environment
Set the following parameters under the AP tenant:
SET GLOBAL parallel_servers_target = MIN_CPU * 20;
Here, MIN_CPU represents the lower limit of CPU specifications. For detailed information on setting the parallel query queue conditions for each server, see parallel_servers_target.
Collect statistics
After importing data, you can directly call the DBMS statistics package to collect statistics.
For more information about statistics, see Overview of statistics.
Set hints
The degree of parallelism (DOP) for each SQL statement should not exceed 1.5 times the number of physical CPUs.
Generally, if multiple SQL statements are not executed concurrently, the DOP for a single SQL statement can be set to the number of CPUs. For example, if the system has 32 physical CPUs, set the hint to /*+ PARALLEL(32) */.
Performance tuning
- Use
top -Hto view the tenant's CPU usage. - If the performance of a single SQL statement does not meet expectations, use
sql_plan_monitorto obtain a performance report.
-- open_dt indicates the time interval from the operator's opening to closing.
-- row_dt indicates the time interval from when the operator starts outputting the first row until it outputs OB_ITER_END.
-- If the value is NULL, it indicates that the first row has not been ejected or that OB_ITER_END has not been reached.
-- Add a hint for the slow SQL statement: /*+ monitor */
-- Execute the slow SQL statement with the added hint to obtain the Trace_id. Replace the following Yxxxxxxxxx and execute the following SQL statement.
-- User tenant:
-- Summary:
SELECT op_id, op, rows, rescan, threads, (close_time - open_time) open_dt, (last_row_eof_time-first_row_time) row_dt, open_time, close_time, first_row_time, last_row_eof_time FROM
(
select plan_line_id op_id, concat(lpad('', plan_depth, ' '), plan_operation) op, sum(output_rows) rows, sum(STARTS) rescan, min(first_refresh_time) open_time, max(last_refresh_time) close_time, min(first_change_time) first_row_time, max(last_change_time) last_row_eof_time, count(1) threads from oceanbase.gv$sql_plan_monitor where trace_id = 'Yxxxxxxxxx' group by plan_line_id, plan_operation order by plan_line_id
) a;
-- Details
SELECT op_id, thread, op, rows, rescan, (close_time - open_time) open_dt, (last_row_eof_time-first_row_time) row_dt, open_time, close_time, first_row_time, last_row_eof_time FROM
(
select plan_line_id op_id, PROCESS_NAME thread, concat(lpad('', plan_depth, ' '), plan_operation) op, output_rows rows, STARTS rescan, first_refresh_time open_time, last_refresh_time close_time, first_change_time first_row_time, last_change_time last_row_eof_time from oceanbase.gv$sql_plan_monitor where trace_id = 'Yxxxxxxxxx' order by plan_line_id, PROCESS_NAME
) a;
-- Summary
SELECT op_id, op, output_rows, rescan,threads ,(close_time - open_time) open_dt, (last_row_eof_time-first_row_time) row_dt, open_time, close_time, first_row_time, last_row_eof_time FROM
(
select plan_line_id op_id, concat(lpad(' ', max(plan_depth), ' '), plan_operation) op, sum(output_rows) output_rows, sum(STARTS) rescan, min(first_refresh_time) open_time, max(last_refresh_time) close_time, min(first_change_time) first_row_time, max(last_change_time) last_row_eof_time, count(1) threads from sys.gv$sql_plan_monitor where trace_id = 'Yxxxxxxxxx' group by plan_line_id, plan_operation,plan_depth order by plan_line_id
) a;
-- Details
SELECT op_id, thread, op, output_rows, rescan, (close_time - open_time) open_dt, (last_row_eof_time-first_row_time) row_dt, open_time, close_time, first_row_time, last_row_eof_time FROM
(
select plan_line_id op_id, PROCESS_NAME thread, concat(lpad(' ', plan_depth, ' '), plan_operation) op, output_rows, STARTS rescan, first_refresh_time open_time, last_refresh_time close_time, first_change_time first_row_time, last_change_time last_row_eof_time from sys.gv$sql_plan_monitor where trace_id = 'Yxxxxxxxxx' order by plan_line_id, process_name
) a;
FAQ
The performance of a Query does not meet expectations; the CPU is not fully utilized? Run
show variables like 'parallel_servers_target'to confirm that the target value is not less thanMIN_CPU* 20.PDML performance does not meet expectations?
Use
explain extendedto explain the PDML statement and confirm whether the plan uses PDML. If PDML is not used, theNotefield at the bottom of the plan will indicate the reason. This is usually because the modified table contains triggers, foreign keys,local unique index, or other reasons.If terms such as
DISTRIBUTED INSERT,DISTRIBUTED UPDATE, orDISTRIBUTED DELETEappear, it means PDML was not used.The business logic cannot be modified. How can I parallelize business SQL?
OBProxy allows users to modify connection configurations via the web interface to enable parallelism. For example, on a read-write split connection, you can set the DOP for all SQL statements to 2.