To diagnose parallel execution issues, you can start from two main aspects. First, assess the overall system, such as confirming whether the network, disk I/O, or CPU is fully utilized. Then, focus on the specific SQL to locate the problematic statement and understand its internal state.
System diagnostics
When performance issues occur in a busy system, a preliminary diagnostic at the system level is necessary. Generally, there are two approaches:
- OCP (OceanBase Cloud Platform), which supports visual observation of system performance.
- Command-line system tools such as tsar, which support querying historical monitoring data for networks, disks, CPUs, etc.
tsar is a system monitoring and performance analysis tool that provides detailed information about CPU, disk, network, and other aspects. The following are some common usages of the tsar command:
Check CPU usage:
tsar --cpu
Check disk I/O usage:
tsar --io
Check network transfer rate:
tsar --traffic
In addition to the examples above, tsar supports other options and parameters. For example, using the -d 2 parameter allows you to view data from two days ago to the present, -d YYYY-MM-DD allows you to view data for the specified date YYYY-MM-DD, and -i 1 indicates data collection and display every minute.
View CPU usage for the past 2 days:
tsar -n 2 -i 1 --cpu
If disk or network saturation occurs, prioritize resolving the issue from the perspectives of insufficient hardware capacity or excessive concurrent pressure.
SQL diagnostics
When encountering parallel execution issues, you can check layer by layer from the SQL level, parallel execution thread level, to operator level.
Confirm the SQL is still executing
Confirm the SQL is executing normally: Pay attention to the TIME field. If the TIME field increases with each query on the GV$OB_PROCESSLIST view and the STATE is ACTIVE, it indicates the query is still in progress.
Confirm whether the SQL is retrying repeatedly: If the SQL does not return a result due to repeated retries, the RETRY_CNT and RETRY_INFO fields contain relevant information. RETRY_CNT indicates how many times it has retried, and RETRY_INFO indicates the reason for the last retry. When no retries occur, RETRY_CNT is 0. The TOTAL_TIME field indicates the cumulative execution time, including each retry.
If you find the SQL is retrying repeatedly, determine whether intervention is needed based on the error code provided in RETRY_INFO. Common errors include -4138 (OB_SNAPSHOT_DISCARDED). In this case, refer to Classification and optimization of parallel execution and increase the undo_retention value to resolve the issue.
SELECT
TENANT,INFO,TRACE_ID,STATE,TIME,TOTAL_TIME,RETRY_CNT,RETRY_INFO
FROM
oceanbase.GV$OB_PROCESSLIST;
Confirm whether SQL is still performing parallel queries
In an OBServer cluster, you can view all active parallel execution threads through the GV$OB_PX_WORKER_STAT view.
obclient> SELECT * FROM oceanbase.GV$OB_PX_WORKER_STAT;
SESSION_ID: 3221520411
TENANT_ID: 1002
SVR_IP: 192.xx.xx.xx
SVR_PORT: 19510
TRACE_ID: Y4C360B9E1F4D-0005F9A76E9E66B2-0-0
QC_ID: 1
SQC_ID: 0
WORKER_ID: 0
DFO_ID: 0
START_TIME: 2023-04-23 17:29:17.372461
Combined with the TRACE_ID obtained from GV$OB_PROCESSLIST, this view provides information such as which DFOs the SQL is currently executing and how long it has been running.
If nothing is found in this view, but the corresponding SQL is still visible in GV$OB_PROCESSLIST, possible reasons include:
- All DFOs have completed execution. The result set is large, and the data is currently being streamed to the client.
- All DFOs except for the topmost one have completed execution.
Confirm the execution status of each operator
You can use oceanbase.GV$SQL_PLAN_MONITOR to view the execution status of each operator in each parallel worker thread. GV$SQL_PLAN_MONITOR contains two parts of data:
- Operators that have completed execution. Completion means the operator has called the close interface and no longer processes any data in the current thread.
- Operators that are in progress. Being in progress means the operator has not called the close interface and is still processing data. To read data from these operators, specify
request_id < 0in the WHERE clause when querying theGV$SQL_PLAN_MONITORview. When using therequest_id < 0condition to query this view, it is also referred to as accessing the "Realtime SQL PLAN MONITOR". This access interface may change in the future.
GV$SQL_PLAN_MONITOR contains several important fields:
TRACE_ID: It uniquely identifies an SQL statement.PLAN_LINE_ID: The number of the operator in the execution plan, corresponding to the number seen when using theEXPLAINstatement.PLAN_OPERATION: The operator name, such asTABLE SCANorHASH JOIN.OUTPUT_ROWS: The number of rows output by the current operator.FIRST_CHANGE_TIME: The time when the operator outputs the first row of data.LAST_CHANGE_TIME: The time when the operator outputs the last row of data.FIRST_REFRESH_TIME: The time when the operator starts monitoring.LAST_REFRESH_TIME: The time when the operator stops monitoring.
Based on these fields, you can roughly outline the main actions of an operator in processing data. Here are some examples:
- View an executed SQL statement to see how many threads each operator uses for execution:
SELECT PLAN_LINE_ID, PLAN_OPERATION, COUNT(*) THREADS
FROM GV$SQL_PLAN_MONITOR
WHERE TRACE_ID = 'YA1E824573385-00053C8A6AB28111-0-0'
GROUP BY PLAN_LINE_ID, PLAN_OPERATION
ORDER BY PLAN_LINE_ID;
+--------------+------------------------+---------+
| PLAN_LINE_ID | PLAN_OPERATION | THREADS |
+--------------+------------------------+---------+
| 0 | PHY_PX_FIFO_COORD | 1 |
| 1 | PHY_PX_REDUCE_TRANSMIT | 2 |
| 2 | PHY_GRANULE_ITERATOR | 2 |
| 3 | PHY_TABLE_SCAN | 2 |
+--------------+------------------------+---------+
4 rows in set
- View a running SQL statement to see which operators are currently being executed, how many threads are used, and how many rows have been output:
SELECT PLAN_LINE_ID, CONCAT(LPAD('', PLAN_DEPTH, ' '), PLAN_OPERATION) OPERATOR, COUNT(*) THREADS, SUM(OUTPUT_ROWS) ROWS
FROM GV$SQL_PLAN_MONITOR
WHERE TRACE_ID = 'YA1E824573385-00053C8A6AB28111-0-0' AND REQUEST_ID < 0
GROUP BY PLAN_LINE_ID, PLAN_OPERATION, PLAN_DEPTH
ORDER BY PLAN_LINE_ID;
- View an executed SQL statement to see how many rows each operator processed and how many rows were output:
SELECT PLAN_LINE_ID, CONCAT(LPAD('', PLAN_DEPTH, ' '), PLAN_OPERATION) OPERATOR, SUM(OUTPUT_ROWS) ROWS
FROM GV$SQL_PLAN_MONITOR
WHERE TRACE_ID = 'Y4C360B9E1F4D-0005F9A76E9E6193-0-0'
GROUP BY PLAN_LINE_ID, PLAN_OPERATION, PLAN_DEPTH
ORDER BY PLAN_LINE_ID;
+--------------+-----------------------------------+------+
| PLAN_LINE_ID | OPERATOR | ROWS |
+--------------+-----------------------------------+------+
| 0 | PHY_PX_MERGE_SORT_COORD | 2 |
| 1 | PHY_PX_REDUCE_TRANSMIT | 2 |
| 2 | PHY_SORT | 2 |
| 3 | PHY_HASH_GROUP_BY | 2 |
| 4 | PHY_PX_FIFO_RECEIVE | 2 |
| 5 | PHY_PX_DIST_TRANSMIT | 2 |
| 6 | PHY_HASH_GROUP_BY | 2 |
| 7 | PHY_HASH_JOIN | 2002 |
| 8 | PHY_HASH_JOIN | 2002 |
| 9 | PHY_JOIN_FILTER | 8192 |
| 10 | PHY_PX_FIFO_RECEIVE | 8192 |
| 11 | PHY_PX_REPART_TRANSMIT | 8192 |
| 12 | PHY_GRANULE_ITERATOR | 8192 |
| 13 | PHY_TABLE_SCAN | 8192 |
| 14 | PHY_GRANULE_ITERATOR | 8192 |
| 15 | PHY_TABLE_SCAN | 8192 |
| 16 | PHY_GRANULE_ITERATOR | 8192 |
| 17 | PHY_TABLE_SCAN | 8192 |
+--------------+-----------------------------------+------+
18 rows in set
For aesthetic presentation, the PLAN_DEPTH field is used above to indicate indentation. PLAN_DEPTH indicates the depth of the operator in the operator tree.
Notice
- Information about operators of unscheduled DFO does not appear in
GV$SQL_PLAN_MONITOR. - In a PL statement, if it contains multiple SQL statements, they have the same
TRACE_ID. - If the parameter
enable_sql_auditis set to FALSE, data is not recorded inGV$SQL_PLAN_MONITOR.
Diagnose slow PDML
PDML statements that execute for a long time are subject to the limit of the undo_retention variable. When the execution time exceeds undo_retention, an automatic SQL-level retry may occur, which appears to the user as the PDML statement never ending.
You can diagnose PDML issues by checking the following views:
Confirm whether PDML is currently in progress.
obclient> SELECT * FROM oceanbase.__all_virtual_px_worker_stat;Confirm the total number of PDML executions (the
threadsfield). If this number is much larger than the degree of parallelism, retries have occurred.obclient> 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 = 'YB42C0A8051D-000600EFF65FBCFB-0-0' group by plan_line_id, plan_operation order by plan_line_id -> )a;Further confirm retries by checking the processlist virtual table, where the
retry_cntfield should be greater than 0.-- time: The duration since the start of this retry. -- total_time: The amount of time elapsed from when the user initiated the SQL statement until now. -- retry_cnt: The number of retries that have already been performed. If no retries have occurred, retry_cnt = 0. -- retry_info: What error code triggered the last retry? select time, total_time, trace_id, retry_cnt, retry_info from __all_virtual_processlist;Modify the
undo_retentionparameter to a value greater than the maximum execution time for a single PDML, in seconds.obclient> SHOW VARIABLES LIKE "%undo%" +---------------+-------+ | Variable_name | Value | +---------------+-------+ | undo_retention| 1800 | +---------------+-------+ 1 row in set