Real-time execution plan display

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 EXPLAIN result 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 parameters involved.

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

In OceanBase Database, the plan cache of each server is independent. You can directly access the v$plan_cache_plan_stat view to query the plan cache on the server. When you specify the tenant_id 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.81.152.44
        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 rowin set (0.01 sec)

Step 2 Use plan_id to demonstrate the corresponding execution plan

After you retrieve the plan_id, you can use the tenant_id and plan_id 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 of the plan may be different from those of 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.81.152.44 | 15212 |       7 |  PHY_ROOT_TRANSMIT | NULL |    0 |    0 |
|      1001 | 100.81.152.44 | 15212 |       7 |   PHY_INSERT       | NULL |    0 |    0 |
|      1001 | 100.81.152.44 | 15212 |       7 |    PHY_EXPR_VALUES | NULL |    0 |    0 |
+-----------+---------------+-------+---------+--------------------+------+------+------+
3 rows in set (0.01 sec)

Notice

• To access the gv$plan_cache_plan_explain view, you must specify values for IP, port, tenant_id, and plan_id.

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