You can analyze queries related to distributed plans by performing the following steps:
Query the
(G)V$OB_PLAN_CACHE_PLAN_STATand(G)V$OB_SQL_AUDITviews and check whether the plan is a distributed plan.Analyze the correctness of the execution plan.
Query the
(G)V$OB_SQL_AUDITview by usingtrace_idand check the time consumed for executing each subplan. Each execution of a subplan by using a remote procedure call (RPC) corresponds to an SQL Audit record. You can analyze the SQL Audit record to locate the problem.
In the following example, is_executor_rpc = 1 indicates an SQL Audit record for a subplan execution. This record stores information related to that execution. is_executor_rpc = 0 indicates an SQL Audit record for a thread that accepted the SQL request. This record contains information about the SQL execution process, including information about the SQL request and the obtained execution plan.
obclient> SELECT/*+ PARALLEL(15)*/sql_id, is_executor_rpc, elapsed_time
FROM oceanbase.GV$OB_SQL_AUDIT WHERE trace_id = 'YB420AB74FC6-00056349D323483A';
+----------------------------------+-----------------+--------------+
| sql_id | is_executor_rpc | elapsed_time |
+----------------------------------+-----------------+--------------+
| | 1 | 124 |
| | 1 | 191 |
| | 1 | 123447 |
| | 1 | 125 |
| 20172B18BC9EE3F806D4149895754CE0 | 0 | 125192 |
| | 1 | 148 |
| | 1 | 149 |
| | 1 | 140 |
+----------------------------------+-----------------+--------------+