Note
This view is available starting with V4.2.0.
Purpose
This view displays the plan information collected by executing the EXPLAIN PLAN statement on the current OBServer node for the current tenant.
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. |
| DB_ID | NUMBER(38) | NO | The ID of the plan. |
| SQL_ID | VARCHAR2(32) | NO | The ID of the query. |
| PLAN_HASH | NUMBER(38) | NO | The ID of the schema to which the query belongs. |
| PLAN_ID | NUMBER(38) | NO | The hash value of the plan. |
| GMT_CREATE | TIMESTAMP(6) WITH LOCAL TIME ZONE | NO | The time when the record was generated. |
| OPERATOR | VARCHAR2(255) | NO | The name of the operator, for example, TABLE SCAN or SORT. |
| OBJECT_NODE | VARCHAR2(40) | NO | The name of the dblink if the current operator is related to a dblink. |
| OBJECT_ID | NUMBER(38) | NO | The ID of the scanned object. For example, the ID of the physical table scanned by the TABLE SCAN operator. |
| OBJECT_OWNER | VARCHAR2(128) | NO | The user to which the scanned object belongs. |
| OBJECT_NAME | VARCHAR2(128) | NO | The name of the scanned object. For example, the name of the physical table scanned by the TABLE SCAN operator. |
| OBJECT_ALIAS | VARCHAR2(261) | NO | The alias of the scanned object. For example, the alias of the physical table scanned by the TABLE SCAN operator. |
| OBJECT_TYPE | VARCHAR2(20) | NO | The type of the scanned object. For example, SYNONYM, DBLINK, or BASIC TABLE. |
| OPTIMIZER | VARCHAR2(4000) | NO | The information about indexes, for example, the number of physical rows, logical rows, and table accesses, the type of statistics used, and the version number of the statistics. |
| ID | NUMBER(38) | NO | The ID of the logical operator. |
| PARENT_ID | NUMBER(38) | NO | The ID of the parent operator of the logical operator. |
| DEPTH | NUMBER(38) | NO | The depth of the logical operator in the current plan, that is, the level in the binary plan tree. |
| POSITION | NUMBER(38) | NO | The position of the current logical operator among the child operators of the parent operator. |
| COST | NUMBER(38) | NO | The estimated cost calculated by the optimizer. |
| REAL_COST | NUMBER(38) | NO | The actual cost of the plan when it was executed for the first time (execution feedback information). |
| CARDINALITY | NUMBER(38) | NO | The estimated number of rows output by the current operator calculated by the optimizer. |
| REAL_CARDINALITY | NUMBER(38) | NO | The actual number of rows output by the current operator when the plan was executed for the first time (execution feedback information). |
| IO_COST | NUMBER(38) | NO | The actual I/O overhead of the current operator when the plan was executed for the first time (execution feedback information). |
| CPU_COST | NUMBER(38) | NO | The actual CPU overhead of the current operator when the plan was executed for the first time (execution feedback information). |
| BYTES | NUMBER(38) | NO | The estimated width of the data of the current operator calculated by the optimizer. |
| ROWSET | NUMBER(38) | NO | The vectorized size of the current operator. |
| OTHER_TAG | VARCHAR2(4000) | NO | The hint used in the current query. This column stores only the information in the first row of the plan. |
| PARTITION_START | VARCHAR2(4000) | NO | The partition information of the TABLE SCAN operator. |
| OTHER | VARCHAR2(4000) | NO | The optimization information, for example, the plan type, parameterized information of the Fast Parser, Plan Note, and constraint information hit by the plan. |
| ACCESS_PREDICATES | VARCHAR2(4000) | NO | The expression information that needs to be accessed by the current operator. |
| FILTER_PREDICATES | VARCHAR2(4000) | NO | The filtering conditions of the current operator. |
| STARTUP_PREDICATES | VARCHAR2(4000) | NO | The startup conditions of the current operator. |
| PROJECTION | VARCHAR2(4000) | NO | The output expression information of the current operator. |
| SPECIAL_PREDICATES | VARCHAR2(4000) | NO | The unique expression information of the current operator, for example, the join condition, GROUP BY expressions, and SORT expressions. |
| QBLOCK_NAME | VARCHAR2(128) | NO | The name of the query block to which the current operator belongs. |
| REMARKS | VARCHAR2(4000) | NO | The modification tracking information of the qb_name. |
| OTHER_XML | VARCHAR2(4000) | NO | OUTLINE DATA |
Sample query
Query the plan information collected by executing the EXPLAIN PLAN statement on the current OBServer node for the current tenant in the user tenant. Display one record.
obclient [SYS]> SELECT * FROM SYS.V$OB_SQL_PLAN WHERE ROWNUM <= 1\G
The query result is as follows:
*************************** 1. row ***************************
TENANT_ID: 1004
DB_ID: 201006
SQL_ID: 87608EF09AC3942892896C42F7792955
PLAN_HASH: 3290318591324336132
PLAN_ID: 3598
GMT_CREATE: 25-MAR-25 10.00.05.020265 PM
OPERATOR: DISTRIBUTED INSERT
OBJECT_NODE: NULL
OBJECT_ID: 0
OBJECT_OWNER: NULL
OBJECT_NAME: NULL
OBJECT_ALIAS: NULL
OBJECT_TYPE: NULL
OPTIMIZER: NULL
ID: 0
PARENT_ID: -1
DEPTH: 0
POSITION: 1
COST: 13
REAL_COST: 0
CARDINALITY: 1
REAL_CARDINALITY: 0
IO_COST: 0
CPU_COST: 84
BYTES: 0
ROWSET: 1
OTHER_TAG: /*+
*/
PARTITION_START: NULL
OTHER: Plan Type:
LOCAL
Note:
Degree of Parallelisim is 1 because of table property
ACCESS_PREDICATES: NULL
FILTER_PREDICATES: NULL
STARTUP_PREDICATES: NULL
PROJECTION: NULL
SPECIAL_PREDICATES: columns([{DBMS_SCHEDULER_T1: ({DBMS_SCHEDULER_T1: (DBMS_SCHEDULER_T1.__pk_increment, DBMS_SCHEDULER_T1.COL)})}]),
column_values([T_HIDDEN_PK], [column_conv(DATE,PS:(19,0),NULL,__values.COL)])
QBLOCK_NAME: INS$1
REMARKS: stmt_id:0, INS$1
OTHER_XML: /*+
BEGIN_OUTLINE_DATA
USE_DISTRIBUTED_DML(@"INS$1")
OPTIMIZER_FEATURES_ENABLE('4.3.5.1')
END_OUTLINE_DATA
*/
1 row in set