Note
This view was introduced in OceanBase Database V4.0.0.
Purpose
The V$OB_PLAN_CACHE_PLAN_EXPLAIN view displays the physical execution plans that are cached in the plan cache on the current OBServer node.
Note
This view supports only GET operations. To query information in this view, you must specify the PLAN_ID column.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| TENANT_ID | NUMBER(38) | NO | The ID of the tenant. |
| SVR_IP | VARCHAR2(46) | NO | The IP address of the OBServer node. |
| SVR_PORT | NUMBER(38) | NO | The port number of the OBServer node. |
| PLAN_ID | NUMBER(38) | NO | The ID of the plan. |
| PLAN_DEPTH | NUMBER(38) | NO | The display depth of the operator. |
| PLAN_LINE_ID | NUMBER(38) | NO | The ID of the operator. |
| OPERATOR | VARCHAR2(128) | NO | The name of the operator. |
| NAME | VARCHAR2(256) | NO | The name of the table. |
| ROWS | NUMBER(38) | NO | The estimated number of rows in the result. |
| COST | NUMBER(38) | NO | The estimated cost. |
| PROPERTY | VARCHAR2(4096) | NO | The information about the corresponding operator. |
Sample query
Query the
V$OB_PLAN_CACHE_PLAN_STATview for thePLAN_IDvalue of the current OBServer node.obclient [SYS]> SELECT PLAN_ID FROM SYS.V$OB_PLAN_CACHE_PLAN_STAT WHERE ROWNUM < 5;The query result is as follows:
+---------+ | PLAN_ID | +---------+ | 800 | | 802 | | 803 | | 804 | +---------+ 4 rows in set (0.011 sec)Query the
V$OB_PLAN_CACHE_PLAN_EXPLAINview for the physical execution plan with thePLAN_IDvalue of800in the plan cache.obclient [SYS]> SELECT * FROM SYS.V$OB_PLAN_CACHE_PLAN_EXPLAIN WHERE PLAN_ID = 800\GThe query result is as follows:
*************************** 1. row *************************** TENANT_ID: 1004 SVR_IP: xx.xx.xx.xx SVR_PORT: 2882 PLAN_ID: 800 PLAN_DEPTH: 0 PLAN_LINE_ID: 0 OPERATOR: PHY_TABLE_SCAN NAME: __all_transfer_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_transfer_task] 1 row in set (0.023 sec)