Real-time execution plan display

OceanBase Database can display the physical execution plans of SQL statements.

You can run the EXPLAIN command to demonstrate the execution plan generated by the current optimizer for an SQL query. However, changes in statistics and settings of user session variables may lead to differences between the return result of the EXPLAIN command and the corresponding SQL plan in the plan cache. To determine the execution plans that are used by the SQL query, you must analyze the physical execution plans in the plan cache.

You can demonstrate the execution plan of an SQL query in the plan cache by querying the (g)v$plan_cache_plan_explain view.

Example:

obclient> VIEW_DEFINITION='SELECT *
FROM oceanbase.gv$plan_cache_plan_explain
WHERE IP =host_ip() AND PORT = rpc_port()'

The following table describes the fields in the syntax.

Step 1. Query the plan_id value of the SQL query in the plan cache

In OceanBase Database, the plan cache of each server is independent. You can access the v$plan_cache_plan_stat view to query the plan cache on the server. When you specify the tenant_id parameter and the SQL string to be queried (fuzzy match is supported), you can find the corresponding plan_id of the SQL statement in the plan cache.

obclient> SELECT * FROM v$plan_cache_plan_stat  WHERE tenant_id= 1001
                                AND STATEMENT LIKE 'INSERT INTO T1 VALUES%'\G

***************************1. row ***************************
       tenant_id: 1001
          svr_ip:100.x.x.x
        svr_port:15212
         plan_id: 7
          sql_id:0
            type: 1
       statement: insert into t1 values(1)
       plan_hash:1
last_active_time:2016-05-28 19:08:57.416670
    avg_exe_usec:0
slowest_exe_time:1970-01-01 08:00:00.000000
slowest_exe_usec:0
      slow_count:0
       hit_count:0
        mem_used:8192
1 row in set

Step 2. Use the plan_id value to demonstrate the corresponding execution plan

After you retrieve the value of the plan_id parameter, you can use the values of the tenant_id and plan_id parameters to access the v$plan_cache_plan_explain view and demonstrate the execution plan.

Notice

The plan demonstrated here is a physical execution plan. Operator names in the plan may differ from those in the logical execution plan demonstrated by running the EXPLAIN command.

obclient> SELECT * FROM v$plan_cache_plan_explain
           WHERE tenant_id = 1001 AND plan_id = 7;

+-----------+---------------+-------+---------+--------------------+------+------+------+
| TENANT_ID | IP            | PORT  | PLAN_ID | OPERATOR           | NAME | ROWS | COST |
+-----------+---------------+-------+---------+--------------------+------+------+------+
|      1001 | 100.x.x.x     | 15212 |       7 |  PHY_ROOT_TRANSMIT | NULL |    0 |    0 |
|      1001 | 100.x.x.x     | 15212 |       7 |   PHY_INSERT       | NULL |    0 |    0 |
|      1001 | 100.x.x.x     | 15212 |       7 |    PHY_EXPR_VALUES | NULL |    0 |    0 |
+-----------+---------------+-------+---------+--------------------+------+------+------+
3 rows in set

Notice

• To access the gv$plan_cache_plan_explain view, you must specify values for the IP, port, tenant_id, and plan_id parameters.
• To access the v$plan_cache_plan_explain view, you must specify values for the tenant_id and plan_id parameters. Otherwise, the system returns an empty set.