Note
This view is available starting with V4.2.0.
Purpose
This view displays the plan information collected by executing the EXPLAIN PLAN statement for all tenants on all OBServer nodes.
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. |
| SVR_IP | varchar(46) | NO | The IP address of the node where the replica resides. |
| SVR_PORT | bigint(20) | NO | The port of the node where the replica resides. |
| PLAN_ID | bigint(20) | The plan ID. | |
| 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. |
| 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, SORT). |
| OBJECT_NODE | varchar(40) | NO | If the current operator is related to a DBLINK, this column indicates the name of the 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 user to which the scanned object belongs. |
| 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, 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, and the version number of the statistics). |
| 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 of the binary plan tree. |
| POSITION | bigint(20) | NO | The position 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 during the first execution (execution feedback information). |
| 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 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. |
| ROWSET | bigint(20) | NO | The vectorized size of the current operator. |
| OTHER_TAG | varchar(4000) | NO | The hints used in the current query. This column only stores the information of the first row in 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, parameterized information of Fast Parser, Plan Note, and constraint information 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 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
In the sys tenant, query the plan information collected by executing the EXPLAIN PLAN statement for all tenants on all nodes, and display one record.
obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_SQL_PLAN LIMIT 1\G
The query result is as follows:
*************************** 1. row ***************************
TENANT_ID: 1
SVR_IP: 11.xxx.xxx.xxx
SVR_PORT: 28825
PLAN_ID: 28790
SQL_ID: 359E40D6A4AB75171925325700759325
DB_ID: 500006
PLAN_HASH: 6388442502389984409
GMT_CREATE: 2025-03-26 10:26:41.039836
OPERATOR: EXPRESSION
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: 0
BYTES: 0
ROWSET: 1
OTHER_TAG: /*+
*/
PARTITION_START:
OTHER: Plan Type:
LOCAL
Note:
Degree of Parallelisim is 1 because of table property
ACCESS_PREDICATES:
FILTER_PREDICATES:
STARTUP_PREDICATES:
PROJECTION: output([database()])
SPECIAL_PREDICATES: values({database()})
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