Views related to the plan cache

To facilitate the query and management of cached execution plans, OceanBase Database provides the following views:

GV$OB_PLAN_CACHE_PLAN_STAT

The GV$OB_PLAN_CACHE_PLAN_STAT view records the specific information and execution statistics of each execution plan, and each cached plan has one record in this view. The following table describes the key columns in the view.

From this view, you can identify and optimize SQL statements that consume the most CPU resources on the OBServer node, and thereby improve the performance of the database.

GV$OB_PLAN_CACHE_PLAN_EXPLAIN

The GV$OB_PLAN_CACHE_PLAN_EXPLAIN view records the shape of each execution plan in the plan cache. You can specify ip, port, tenant_id, and plan_id to query a specific tree-shaped plan. The following table describes the key columns in the view.

The following example shows how to query a specific execution plan. The result is similar to the output of the EXPLAIN statement in OceanBase Database. Note that the plan returned from the GV$OB_PLAN_CACHE_PLAN_EXPLAIN view is the one generated for the first execution of the SQL statement, which may be inconsistent with the output of EXPLAIN. In this case, we need to check whether there is a bad case of the plan cache and determine whether to bind an execution plan to the SQL statement.

select plan_line_id, operator, name, rows, cost
from oceanbase.gv$ob_plan_cache_plan_explain
where ip = 'xx.xx.xx.xx'
  and port = 30042
  and tenant_id = 1001
  and plan_id = 248;
+--------------+------------------------+--------------+---------+----------+
| plan_line_id | operator               | name         |  rows   |  cost    |
+--------------+------------------------+--------------+---------+----------+
|            0 | PHY_SORT               | NULL         |  6      |  16522   |
|            1 |  PHY_HASH_GROUP_BY     | NULL         |  6      |  16506   |
|            2 |   PHY_TABLE_SCAN       | lineitem     |  11473  |   6597   |
+--------------+------------------------+--------------+---------+----------+