Note
This view was introduced in V4.2.0.
Purpose
This view displays the plan information collected by the execution plans obtained by executing the EXPLAIN PLAN tool.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| TENANT_ID | bigint(20) | NO | The ID of the tenant to which the plan belongs
NoteThis field is introduced in V4.3.5 BP1. |
| SVR_IP | VARCHAR2(46) | NO | The IP address of the node where the replica resides. |
| SVR_PORT | NUMBER(38) | NO | The port number of the node where the replica resides. |
| 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 schema ID 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 is generated. |
| OPERATOR | VARCHAR2(255) | NO | The name of the operator, for example, TABLE SCAN and SORT. |
| OBJECT_NODE | VARCHAR2(40) | NO | If the current operator is related to a DBLINK, this field indicates the name of the DBLINK. |
| OBJECT_ID | NUMBER(38) | NO | The ID of the scanned object, for example, the physical table ID 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 physical table name 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, and BASIC TABLE. |
| OPTIMIZER | VARCHAR2(4000) | NO | Information about the index, for example, the number of physical rows, logical rows, rows accessed by table access, and the type and version of 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 layer in the binary plan tree. |
| POSITION | NUMBER(38) | NO | The number of the child node of the parent operator of the current logical operator. |
| COST | NUMBER(38) | NO | The estimated cost by the optimizer. |
| REAL_COST | NUMBER(38) | NO | The real cost of the plan during the first execution (execution feedback information). |
| CARDINALITY | NUMBER(38) | NO | The estimated number of output rows of the current operator by the optimizer. |
| REAL_CARDINALITY | NUMBER(38) | NO | The real number of output rows of the current operator during the first execution (execution feedback information). |
| IO_COST | NUMBER(38) | NO | The actual I/O overhead of the current operator during the first execution (execution feedback information). |
| CPU_COST | NUMBER(38) | NO | The actual CPU overhead of the current operator during the first execution (execution feedback information). |
| BYTES | NUMBER(38) | NO | The estimated width of data processed by the current operator. |
| ROWSET | NUMBER(38) | NO | The vectorization size of the current operator. |
| OTHER_TAG | VARCHAR2(4000) | NO | The hints used in the current query. This field is recorded only in the first row of the plan. |
| PARTITION_START | VARCHAR2(4000) | NO | The partition information scanned by the TABLE SCAN operator. |
| OTHER | VARCHAR2(4000) | NO | Optimization information, such as 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 the current operator needs to access. |
| FILTER_PREDICATES | VARCHAR2(4000) | NO | The filter 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, such as join conditions, group expressions, and sort expressions. |
| QBLOCK_NAME | VARCHAR2(128) | NO | The name of the query block in which the current operator resides. |
| REMARKS | VARCHAR2(4000) | NO | Modification tracking information of qb_name. |
| OTHER_XML | VARCHAR2(4000) | NO | Outline data. |
Sample query
In a user tenant, view the plan information collected by executing EXPLAIN PLAN on all nodes for the current tenant, and display one record.
obclient [SYS]> SELECT * FROM SYS.GV$OB_SQL_PLAN WHERE ROWNUM <= 1\G
The query result is as follows:
*************************** 1. row ***************************
TENANT_ID: 1004
SVR_IP: 11.xxx.xxx.xxx
SVR_PORT: 28825
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