Note
This view is available starting with V4.2.5.
Overview
The DBA_WR_SQL_PLAN view displays WR-collected GV$OB_SQL_PLAN data.
Columns
Column |
Type |
Is NULL |
Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | Tenant ID |
| CLUSTER_ID | bigint(20) | NO | Cluster ID |
| SNAP_ID | bigint(20) | NO | Snapshot ID |
| SVR_IP | varchar(46) | NO | Node IP |
| SVR_PORT | bigint(20) | NO | Node Port |
| SQL_ID | varchar(32) | NO | SQL Statement ID |
| PLAN_HASH | bigint(20) unsigned | NO | Execution Plan HASH Value |
| PLAN_ID | bigint(20) | NO | Execution Plan ID |
| ID | bigint(20) unsigned | NO | Operator ID |
| DB_ID | bigint(20) | NO | Database ID |
| GMT_CREATE | timestamp(6) | NO | Record Generated At |
| OPERATOR | varchar(255) | NO | Operator name |
| OPTIONS | varchar(255) | NO | Options |
| OBJECT_NODE | varchar(40) | NO | If the current operator is DBLINK-related, it indicates the name of DBlink. |
| OBJECT_ID | bigint(20) | NO | Table or index object number for operator operation |
| OBJECT_OWNER | varchar(128) | NO | Object Owner |
| OBJECT_NAME | varchar(128) | NO | Object Name |
| OBJECT_ALIAS | varchar(261) | NO | Object alias in the statement |
| OBJECT_TYPE | varchar(20) | NO | Object type |
| OPTIMIZER | varchar(4000) | NO | Index information (for example, physical rows, logical rows, number of rows accessed, type of statistics used, and version number of statistics) |
| PARENT_ID | bigint(20) | NO | Parent operator ID |
| DEPTH | bigint(20) | NO | Depth of the operator hierarchy. The depth of the root operator is 0. |
| POSITION | bigint(20) | NO | The order position among all operators with the same parent operator |
| IS_LAST_CHILD | bigint(20) | NO | The current logical operator is the last child node of the parent operator. |
| COST | bigint(20) | NO | Estimated Cost by the Optimizer |
| REAL_COST | bigint(20) | NO | Actual Cost on First Execution |
| CARDINALITY | bigint(20) | NO | Estimated Output Rows by the Optimizer |
| REAL_CARDINALITY | bigint(20) | NO | Actual Output Rows on First Execution |
| BYTES | bigint(20) | NO | Width of data estimated by the optimizer for the current operator |
| ROWSET | bigint(20) | NO | Vectorization Size of the Current Operator |
| OTHER_TAG | varchar(4000) | NO | The hints used in the current query are stored only in the first row of the plan record. |
| PARTITION_START | varchar(4000) | NO | Scanned Partition Information |
| OTHER | varchar(4000) | NO | Optimization information, such as the plan type, parameterized information of Fast Parser, plan notes, and constraint information hit by the plan. |
| CPU_COST | bigint(20) | NO | The actual CPU cost of the current operator during the first execution of the plan (execution feedback information). |
| IO_COST | bigint(20) | NO | The actual I/O overhead of the current operator during the first execution of the plan (execution feedback information). |
| ACCESS_PREDICATES | varchar(4000) | NO | Information about the expression that the operator needs to access |
| FILTER_PREDICATES | varchar(4000) | NO | Filter condition of the current operator |
| FILTER_PREDICATES | varchar(4000) | NO | Filter condition of the current operator |
| PROJECTION | varchar(4000) | NO | Output expression information of the current operator |
| SPECIAL_PREDICATES | varchar(4000) | NO | Information on the specific expressions of the current operator, such as Join Condition, GROUP EXPRS, and SORT EXPRS. |
| QBLOCK_NAME | varchar(128) | NO | The name of the query block where the operator is located. |
| REMARKS | varchar(4000) | NO | Modification tracking information of qb_name |
| OTHER_XML | varchar(4000) | NO | OUTLINE DATA |
Sample query
Query the GV$OB_SQL_PLAN data collected by WR.
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)