You can analyze queries related to distributed plans by performing the following steps:
Verify that the execution plan is a distributed plan by checking the execution plan type records in the
(g)v$plan_cache_plan_statand(g)v$sql_auditviews.Analyze the correctness of the execution plan.
Query
gv$sql_auditby usingtrace_idand check the response time of all executed subplans. Because the Remote Procedure Call (RPC) execution of each subplan corresponds to asql_auditrecord, you can analyze thesql_auditrecord to locate the problem.
In the following example, is_executor_rpc = 1 indicates the sql_audit record for a subplan execution. This record stores information related to that execution. is_executor_rpc = 0 indicates the 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 and the obtained execution plan.
obclient> SELECT/*+ PARALLEL(15)*/sql_id, is_executor_rpc, elapsed_time
FROM oceanbase.gv$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 |
+----------------------------------+-----------------+--------------+