Note
This view was introduced in OceanBase Database V4.2.5.
Purpose
The oceanbase.DBA_WR_SQL_PLAN view displays the data collected by Workload Repository (WR) from the GV$OB_SQL_PLAN view.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | The ID of the tenant. |
| CLUSTER_ID | bigint(20) | NO | The ID of the cluster. |
| SNAP_ID | bigint(20) | NO | The ID of the snapshot. |
| SVR_IP | varchar(46) | NO | The IP address of the OBServer node. |
| SVR_PORT | bigint(20) | NO | The port number of the OBServer node. |
| SQL_ID | varchar(32) | NO | The ID of the SQL statement. |
| PLAN_HASH | bigint(20) unsigned | NO | The hash value of the execution plan. |
| PLAN_ID | bigint(20) | NO | The ID of the execution plan. |
| ID | bigint(20) unsigned | NO | The ID of the operator. |
| DB_ID | bigint(20) | NO | The ID of the database. |
| GMT_CREATE | timestamp(6) | NO | The time when the record was generated. |
| OPERATOR | varchar(255) | NO | The name of the operator. |
| OPTIONS | varchar(255) | NO | The options. |
| OBJECT_NODE | varchar(40) | NO | The object node. The value is the name of the DBLink if the current operator is associated with a DBLink. |
| OBJECT_ID | bigint(20) | NO | The ID of the table or index object on which the operator takes effect. |
| OBJECT_OWNER | varchar(128) | NO | The owner of the object. |
| OBJECT_NAME | varchar(128) | NO | The name of the object. |
| OBJECT_ALIAS | varchar(261) | NO | The alias of the object in the statement. |
| OBJECT_TYPE | varchar(20) | NO | The type of the object. |
| OPTIMIZER | varchar(4000) | NO | The index-related information, such as the number of physical rows, number of logical rows, and number of rows returned after table access by index primary key, as well as the type and version of statistics. |
| PARENT_ID | bigint(20) | NO | The ID of the parent operator. |
| DEPTH | bigint(20) | NO | The depth of the operator. The depth of the root operator is 0. |
| POSITION | bigint(20) | NO | The position of the operator in all operators under the same parent operator. |
| IS_LAST_CHILD | bigint(20) | NO | Indicates whether the current logical operator is the last child node of the parent operator. |
| COST | bigint(20) | NO | The cost estimated by the optimizer. |
| REAL_COST | bigint(20) | NO | The actual cost of the first execution. |
| CARDINALITY | bigint(20) | NO | The number of output rows estimated by the optimizer. |
| REAL_CARDINALITY | bigint(20) | NO | The actual number of output rows in the first execution. |
| BYTES | bigint(20) | NO | The data width of the current operator estimated by the optimizer. |
| ROWSET | bigint(20) | NO | The vectorized size of the current operator. |
| OTHER_TAG | varchar(4000) | NO | The hints that have been used by the current query. This information is stored only in the first row of the plan. |
| PARTITION_START | varchar(4000) | NO | The partition scanned. |
| OTHER | varchar(4000) | NO | The optimization information, such as the plan type, parameterized information of the fast parser, plan note, and constraints hit by the plan. |
| CPU_COST | bigint(20) | NO | The actual CPU overhead of the current operator during the first execution of the plan based on the execution feedback. |
| IO_COST | bigint(20) | NO | The actual I/O overhead of the current operator during the first execution of the plan based on the execution feedback. |
| ACCESS_PREDICATES | varchar(4000) | NO | The access conditions of the current operator. |
| FILTER_PREDICATES | varchar(4000) | NO | The filter conditions of the current operator. |
| PROJECTION | varchar(4000) | NO | The output expression information of the current operator. |
| SPECIAL_PREDICATES | varchar(4000) | NO | The expressions specific to the current operator, such as Join Condition, GROUP EXPRS, and SORT EXPRS. |
| QBLOCK_NAME | varchar(128) | NO | The name of the query block in which the current operator resides. |
| REMARKS | varchar(4000) | NO | The modification tracking information about the query block. |
| OTHER_XML | varchar(4000) | NO | The outline data. |
Sample query
Query the data collected by WR from the GV$OB_SQL_PLAN view.
obclient [test]> SELECT * FROM oceanbase.DBA_WR_SQL_PLAN limit 1\G
The query result is as follows:
*************************** 1. row ***************************
TENANT_ID: 1
CLUSTER_ID: 10001
SNAP_ID: 19
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
SQL_ID: FFBE7CC97F106AE6B9BCE5AFAB48CEE4
PLAN_HASH: 6055243608786114737
PLAN_ID: 5362
ID: 2
DB_ID: 201001
GMT_CREATE: 2024-10-19 11:31:48.181241
OPERATOR: TABLE RANGE SCAN
OPTIONS:
OBJECT_NODE:
OBJECT_ID: 104
OBJECT_OWNER: oceanbase
OBJECT_NAME: __ALL_DATABASE
OBJECT_ALIAS: D
OBJECT_TYPE: BASIC TABLE
OPTIMIZER: D:
table_rows:10
physical_range_rows:10
logical_range_rows:10
index_back_rows:0
output_rows:10
table_dop:1
dop_method:Table DOP
avaiable_index_name:[idx_db_name, __all_database]
pruned_index_name:[idx_db_name]
stats info:[version=2024-10-18 22:00:00.326358, is_locked=0, is_expired=0]
dynamic sampling level:0
estimation method:[OPTIMIZER STATISTICS, STORAGE]
PARENT_ID: 0
DEPTH: 1
POSITION: 2
IS_LAST_CHILD: 1
COST: 5
REAL_COST: 4219
CARDINALITY: 10
REAL_CARDINALITY: 0
BYTES: 61
ROWSET: 1
OTHER_TAG:
PARTITION_START: partitions(p0)
OTHER:
CPU_COST: 0
IO_COST: 0
ACCESS_PREDICATES: access([D.database_id], [D.database_name])
FILTER_PREDICATES:
FILTER_PREDICATES:
PROJECTION: output([D.database_id], [D.database_name])
SPECIAL_PREDICATES: is_index_back=false, is_global_index=false,
range_key([D.tenant_id], [D.database_id]), range(0,MIN ; 0,MAX),
range_cond([D.tenant_id = 0])
QBLOCK_NAME: SEL$5C40C8D3
REMARKS:
OTHER_XML:
1 row in set (0.006 sec)