Note
This view is introduced in V4.2.0.
Purpose
This view displays the plan information collected by the execution plan through the EXPLAIN PLAN tool.
Columns
Column |
Type |
Nullable? |
Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | The ID of the tenant to which the plan information belongs
NoteThis field is introduced in V4.3.5 BP1. |
| SQL_ID | varchar(32) | NO | The query ID |
| DB_ID | bigint(20) | NO | The Schema ID 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 record generation time |
| OPERATOR | varchar(255) | NO | The operator name (for example, TABLE SCAN, SORT) |
| OBJECT_NODE | varchar(40) | NO | If the current operator is related to a DBLINK, this field indicates the name of the DBLINK |
| OBJECT_ID | bigint(20) | NO | The ID of the scanned object (for example, the physical table ID scanned by the TABLE SCAN operator) |
| OBJECT_OWNER | varchar(128) | NO | The user to which the scanned object belongs |
| OBJECT_NAME | varchar(128) | NO | The name of the scanned object (for example, the physical table name 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 | Information about indexes (for example, physical rows, logical rows, number of table accesses, and types of statistics used) |
| 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 layer of the binary plan tree |
| POSITION | bigint(20) | NO | The number of the current logical operator as a child node of the parent operator |
| COST | bigint(20) | NO | The estimated cost of the optimizer |
| REAL_COST | bigint(20) | NO | The real cost of the plan during the first execution (execution feedback information) |
| CARDINALITY | bigint(20) | NO | The estimated number of output rows of the current operator by the optimizer |
| REAL_CARDINALITY | bigint(20) | NO | The real number of output rows of the current operator during the first execution (execution feedback information) |
| IO_COST | bigint(20) | NO | The actual I/O overhead of the current operator during the first execution (execution feedback information) |
| CPU_COST | bigint(20) | NO | The actual CPU overhead of the current operator during the first execution (execution feedback information) |
| BYTES | bigint(20) | NO | The estimated width of the data of the current operator by the optimizer |
| ROWSET | bigint(20) | NO | The vectorized size of the current operator |
| OTHER_TAG | varchar(4000) | NO | The hints used in the current query. This field is stored only 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 | Optimization information, such as the plan type, parameterized information of the Fast Parser, Plan Note, and constraints hit by the plan |
| ACCESS_PREDICATES | varchar(4000) | NO | The expression information that the current operator needs to access |
| FILTER_PREDICATES | varchar(4000) | NO | The filter 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 unique expression information of the current operator, such as the join condition, GROUP EXPRS, and SORT EXPRS |
| QBLOCK_NAME | varchar(128) | NO | The name of the query block in which the current operator is located |
| REMARKS | varchar(4000) | NO | The modification tracking information of qb_name |
| OTHER_XML | varchar(4000) | NO | OUTLINE DATA |
Sample query
In a user tenant, view the plan information collected by executing EXPLAIN PLAN in the current tenant on the current node, and 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