Note
This view is available starting with V4.0.0.
Purpose
The V$OB_PLAN_CACHE_PLAN_EXPLAIN view displays the physical execution plans cached on the current OBServer node.
Note
This view supports only the GET operation. When you query this view, you must specify the TENANT_ID and PLAN_ID fields.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | The tenant ID. |
| SVR_IP | varchar(46) | NO | The IP address of the server. |
| SVR_PORT | bigint(20) | NO | The port number of the server. |
| PLAN_ID | bigint(20) | NO | The ID of the plan. |
| PLAN_DEPTH | bigint(20) | NO | The depth of the operator when it is displayed. |
| PLAN_LINE_ID | bigint(20) | NO | The ID of the operator. |
| OPERATOR | varchar(128) | NO | The name of the operator. |
| NAME | varchar(256) | NO | The name of the table. |
| ROWS | bigint(20) | NO | The estimated number of rows. |
| COST | bigint(20) | NO | The estimated cost. |
| PROPERTY | varchar(4096) | NO | The information of the corresponding operator. |
Sample query
Query the V$OB_PLAN_CACHE_PLAN_STAT view to obtain the
PLAN_IDof the current OBServer node.obclient [oceanbase]> SELECT PLAN_ID FROM oceanbase.V$OB_PLAN_CACHE_PLAN_STAT limit 2;The query result is as follows:
+---------+ | PLAN_ID | +---------+ | 605 | | 607 | +---------+ 2 rows in set (0.010 sec)Query the
V$OB_PLAN_CACHE_PLAN_EXPLAINview to obtain the physical execution plan in the plan cache, for example, withPLAN_ID=605.obclient [oceanbase]> SELECT * FROM oceanbase.V$OB_PLAN_CACHE_PLAN_EXPLAIN WHERE PLAN_ID = 605\GThe query result is as follows:
*************************** 1. row *************************** TENANT_ID: 1002 SVR_IP: xx.xx.xx.xx SVR_PORT: 2882 PLAN_ID: 605 PLAN_DEPTH: 0 PLAN_LINE_ID: 0 OPERATOR: PHY_TABLE_SCAN NAME: __all_balance_task ROWS: 1 COST: 2 PROPERTY: table_rows:1, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, avaiable_index_name[__all_balance_task] 1 row in set (0.000 sec)