All Products
Search
Document Center

Analyze queries related to distributed plans

Last Updated: Jun 18, 2021

You can analyze queries related to distributed plans by the following steps:

  1. Verify that the execution plan is a distributed plan by checking the execution plan type records in the (g)v$plan_cache_plan_stat and (g)v$sql_audit views.

  2. Analyze the correctness of the execution plan.

  3. Query gv$sql_audit by using trace_id, and check the response time of all executed subplans. As the Remote Procedure Call (RPC) execution of each subplan 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 the sql_audit record is for a subplan execution, and it mostly stores information related to that execution. is_executor_rpc = 0 indicates the sql_audit record is 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 |
+----------------------------------+-----------------+--------------+