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
Field |
Type |
Is NULL |
Description |
|---|---|---|---|
| TENANT_ID | NUMBER(38) | NO | Tenant ID |
| CLUSTER_ID | NUMBER(38) | NO | Cluster ID |
| SNAP_ID | NUMBER(38) | NO | Snapshot ID |
| SVR_IP | VARCHAR2(46) | NO | Node IP |
| SVR_PORT | NUMBER(38) | NO | Node Port |
| SQL_ID | VARCHAR2(32) | NO | SQL Statement ID |
| PLAN_HASH | NUMBER(38) | NO | Execution Plan HASH Value |
| PLAN_ID | NUMBER(38) | NO | Execution Plan ID |
| ID | NUMBER(38) | NO | Operator ID |
| DB_ID | NUMBER(38) | NO | Database ID |
| GMT_CREATE | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | Record Generated At |
| OPERATOR | VARCHAR2(255) | NO | Operator name |
| OPTIONS | VARCHAR2(255) | NO | Options |
| OBJECT_NODE | VARCHAR2(40) | NO | If the current operator is DBLINK-related, it indicates the name of DBlink. |
| OBJECT_ID | NUMBER(38) | NO | Table or index object number for operator operation |
| OBJECT_OWNER | VARCHAR2(128) | NO | Object Owner |
| OBJECT_NAME | VARCHAR2(128) | NO | Object Name |
| OBJECT_ALIAS | VARCHAR2(261) | NO | Object Alias in the Statement |
| OBJECT_TYPE | VARCHAR2(20) | NO | Object type |
| OPTIMIZER | VARCHAR2(4000) | NO | Index information (such as physical rows, logical rows, number of rows accessed, type of statistics used, and statistics version number) |
| PARENT_ID | NUMBER(38) | NO | Parent operator ID |
| DEPTH | NUMBER(38) | NO | Depth of the operator hierarchy. The depth of the root operator is 0. |
| POSITION | NUMBER(38) | NO | The ordinal position among all operators that have the same parent operator |
| IS_LAST_CHILD | NUMBER(38) | NO | The current logical operator is the last child node of the parent operator. |
| COST | NUMBER(38) | NO | Estimated cost by the optimizer |
| REAL_COST | NUMBER(38) | NO | Actual Cost on First Execution |
| CARDINALITY | NUMBER(38) | NO | Estimated Output Rows by the Optimizer |
| REAL_CARDINALITY | NUMBER(38) | NO | Actual Output Rows on First Execution |
| BYTES | NUMBER(38) | NO | Width of data estimated by the optimizer for the current operator |
| ROWSET | NUMBER(38) | NO | Vectorization Size of the Current Operator |
| OTHER_TAG | VARCHAR2(4000) | NO | The hints used in the current query are stored only in the first row of the plan record. |
| PARTITION_START | VARCHAR2(4000) | NO | Scanned Partition Information |
| OTHER | VARCHAR2(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 | NUMBER(38) | NO | The actual CPU cost of the current operator during the first execution of the plan (execution feedback information). |
| IO_COST | NUMBER(38) | NO | The actual I/O overhead of the current operator during the first execution of the plan (execution feedback information). |
| ACCESS_PREDICATES | VARCHAR2(4000) | NO | Information about the expression accessed by the current operator |
| FILTER_PREDICATES | VARCHAR2(4000) | NO | Filter condition of the current operator |
| FILTER_PREDICATES | VARCHAR2(4000) | NO | Filter condition of the current operator |
| PROJECTION | VARCHAR2(4000) | NO | Output expression information of the current operator |
| SPECIAL_PREDICATES | VARCHAR2(4000) | NO | Information on the specific expressions of the current operator, such as Join Condition, GROUP EXPRS, and SORT EXPRS. |
| QBLOCK_NAME | VARCHAR2(128) | NO | The name of the query block where the operator is located. |
| REMARKS | VARCHAR2(4000) | NO | Modification tracking information of qb_name |
| OTHER_XML | VARCHAR2(4000) | NO | OUTLINE DATA |
Sample query
Query the GV$OB_SQL_PLAN data collected by WR.
obclient [SYS]> SELECT * FROM SYS.DBA_WR_SQL_PLAN WHERE ROWNUM = 1\G
The query result is as follows:
*************************** 1. row ***************************
TENANT_ID: 1004
CLUSTER_ID: 10001
SNAP_ID: 1
SVR_IP: xx.xx.xx.xx
SVR_PORT: 2882
SQL_ID: 1AFDC73B67776FB7482042E92F082BEC
PLAN_HASH: 6388442502389984409
PLAN_ID: 340
ID: 0
DB_ID: 201006
GMT_CREATE: 18-OCT-24 05.23.29.254609 PM
OPERATOR: EXPRESSION
OPTIONS: NULL
OBJECT_NODE: NULL
OBJECT_ID: 0
OBJECT_OWNER: NULL
OBJECT_NAME: NULL
OBJECT_ALIAS: NULL
OBJECT_TYPE: NULL
OPTIMIZER: NULL
PARENT_ID: -1
DEPTH: 0
POSITION: 1
IS_LAST_CHILD: 0
COST: 1
REAL_COST: 0
CARDINALITY: 1
REAL_CARDINALITY: 1
BYTES: 0
ROWSET: 1
OTHER_TAG: /*+
*/
PARTITION_START: NULL
OTHER: Plan Type:
LOCAL
Parameters:
:0 => 1
Note:
Degree of Parallelisim is 1 because of table property
Expr Constraints:
cast(FLOOR(1), BIGINT(-1, 0)) >= 1 result is TRUE
CPU_COST: 3
IO_COST: 0
ACCESS_PREDICATES: NULL
FILTER_PREDICATES: NULL
FILTER_PREDICATES: NULL
PROJECTION: output([get_sys_var('version_comment', 0)], [get_sys_var('version', 0)])
SPECIAL_PREDICATES: values({get_sys_var('version_comment', 0), get_sys_var('version', 0)})
QBLOCK_NAME: SEL$1
REMARKS: stmt_id:0, SEL$1
stmt_id:1, parent:SEL$1 > SEL$658037CB
OTHER_XML: /*+
BEGIN_OUTLINE_DATA
OPTIMIZER_FEATURES_ENABLE('4.2.5.0')
END_OUTLINE_DATA
*/
1 row in set (0.088 sec)