Note
This view is available starting with V4.2.0.
Purpose
This view displays the plan information collected by executing the EXPLAIN PLAN statement on the current OBServer node for the current tenant.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | The ID of the tenant to which the plan information belongs.
NoteThis column is available starting with V4.3.5 BP1. |
| SQL_ID | varchar(32) | NO | The query ID. |
| DB_ID | bigint(20) | NO | The ID of the schema to which the query belongs. |
| PLAN_HASH | bigint(20) unsigned | NO | The hash value of the plan. |
| PLAN_ID | bigint(20) | The plan ID. | |
| GMT_CREATE | timestamp(6) | NO | The time when the record was generated. |
| OPERATOR | varchar(255) | NO | The name of the operator. For example: TABLE SCAN or SORT. |
| OBJECT_NODE | varchar(40) | NO | The name of the dblink if the current operator is related to a dblink. |
| OBJECT_ID | bigint(20) | NO | The ID of the scanned object. For example, the ID of the physical table scanned by the TABLE SCAN operator. |
| OBJECT_OWNER | varchar(128) | NO | The username of the user who owns the scanned object. |
| OBJECT_NAME | varchar(128) | NO | The name of the scanned object. For example, the name of the physical table scanned by the TABLE SCAN operator. |
| OBJECT_ALIAS | varchar(261) | NO | The alias of the scanned object. For example, the alias of the physical table scanned by the TABLE SCAN operator. |
| OBJECT_TYPE | varchar(20) | NO | The type of the scanned object. For example: SYNONYM, DBLINK, or BASIC TABLE. |
| OPTIMIZER | varchar(4000) | NO | The index-related information. For example: the number of physical rows, logical rows, and table accesses, the type of statistics used, and the statistics version number. |
| ID | bigint(20) | NO | The ID of the logical operator. |
| PARENT_ID | bigint(20) | NO | The ID of the parent operator of the logical operator. |
| DEPTH | bigint(20) | NO | The depth of the logical operator in the current plan. That is, the level in the binary plan tree. |
| POSITION | bigint(20) | NO | The index of the current logical operator among the child operators of the parent operator. |
| COST | bigint(20) | NO | The estimated cost calculated by the optimizer. |
| REAL_COST | bigint(20) | NO | The actual cost of the plan when it is executed for the first time. |
| CARDINALITY | bigint(20) | NO | The estimated number of rows output by the current operator. |
| REAL_CARDINALITY | bigint(20) | NO | The actual number of rows output by the current operator when the plan is executed for the first time. |
| IO_COST | bigint(20) | NO | The actual I/O overhead of the current operator when the plan is executed for the first time. |
| CPU_COST | bigint(20) | NO | The actual CPU overhead of the current operator when the plan is executed for the first time. |
| BYTES | bigint(20) | NO | The estimated width of the data output by the current operator. |
| ROWSET | bigint(20) | NO | The vectorized size of the current operator. |
| OTHER_TAG | varchar(4000) | NO | The hint used in the current query. This column is only recorded in the first row of the plan. |
| PARTITION_START | varchar(4000) | NO | The partition information scanned by the TABLE SCAN operator. |
| OTHER | varchar(4000) | NO | The optimization information. For example: the plan type, the parameterization information of the Fast Parser, the Plan Note, and the constraint information that the plan hits. |
| ACCESS_PREDICATES | varchar(4000) | NO | The expression information that needs to be accessed by the current operator. |
| FILTER_PREDICATES | varchar(4000) | NO | The filtering conditions of the current operator. |
| STARTUP_PREDICATES | varchar(4000) | NO | The startup conditions of the current operator. |
| PROJECTION | varchar(4000) | NO | The output expression information of the current operator. |
| SPECIAL_PREDICATES | varchar(4000) | NO | The specific expression information of the current operator. For example: Join Condition, GROUP EXPRS, and SORT EXPRS. |
| QBLOCK_NAME | varchar(128) | NO | The name of the query block to which the current operator belongs. |
| REMARKS | varchar(4000) | NO | The modification tracking information of qb_name. |
| OTHER_XML | varchar(4000) | NO | OUTLINE DATA |
Sample query
Query the plan information collected by executing the EXPLAIN PLAN statement on the current node for the current tenant in the user tenant. Display one record.
obclient [oceanbase]> SELECT * FROM oceanbase.V$OB_SQL_PLAN LIMIT 1\G
The query result is as follows:
*************************** 1. row ***************************
TENANT_ID: 1002
SQL_ID: 07E5B378A3CD3778A58E18DB9AD2A430
DB_ID: 201009
PLAN_HASH: 7420493073239164301
PLAN_ID: 3813
GMT_CREATE: 2025-03-25 17:35:21.524475
OPERATOR: LIMIT
OBJECT_NODE:
OBJECT_ID: 0
OBJECT_OWNER:
OBJECT_NAME:
OBJECT_ALIAS:
OBJECT_TYPE:
OPTIMIZER:
ID: 0
PARENT_ID: -1
DEPTH: 0
POSITION: 1
COST: 1
REAL_COST: 0
CARDINALITY: 1
REAL_CARDINALITY: 1
IO_COST: 0
CPU_COST: 71
BYTES: 0
ROWSET: 16
OTHER_TAG: /*+
*/
PARTITION_START:
OTHER: Plan Type:
LOCAL
Parameters:
:0 => 1
Note:
Degree of Parallelisim is 1 because of table property
ACCESS_PREDICATES:
FILTER_PREDICATES:
STARTUP_PREDICATES:
PROJECTION: output([get_sys_var('version_comment', 0)], [get_sys_var('version', 0)])
SPECIAL_PREDICATES: limit(1), offset(nil)
QBLOCK_NAME: SEL$1
REMARKS: stmt_id:0, SEL$1
OTHER_XML: /*+
BEGIN_OUTLINE_DATA
OPTIMIZER_FEATURES_ENABLE('4.3.5.1')
END_OUTLINE_DATA
*/
1 row in set