The DISPLAY_CURSOR function displays the details of an executed query plan.
Limitations
This feature is supported in BP2 of V4.3.5 and later.
Syntax
DBMS_XPLAN.DISPLAY_CURSOR(plan_id INTEGER DEFAULT 0, -- default value: last plan
format VARCHAR2 DEFAULT 'TYPICAL',
svr_ip VARCHAR2 DEFAULT null, -- default value: server connected by client
svr_port INTEGER DEFAULT 0, -- default value: server connected by client
tenant_id INTEGER DEFAULT 0, -- default value: current tenant
sql_handle VARCHAR2 DEFAULT NULL,
plan_name VARCHAR2 DEFAULT NULL
)
RETURN DBMS_XPLAN_TYPE_TABLE;
Parameters
| Parameter | Description |
|---|---|
| plan_id | The ID of the execution plan. If this parameter is not specified, the last executed plan is displayed. |
| format | The plan format. Valid values:
|
| svr_ip | The IP address of the OBServer node where the plan is located. The default value is the IP address of the OBServer node to which the session is connected. |
| svr_port | The port number of the OBServer node where the plan is located. The default value is the port number of the OBServer node to which the session is connected. |
| tenant_id | The ID of the tenant to which the plan belongs. The default value is the tenant that the session connects to. |
| sql_handle | The handle (handle) used to identify an SQL statement. When you query an execution plan for a specific SQL statement, you can precisely specify the target statement among similar SQL statements by using the sql_handle parameter. |
| plan_name | The name of the execution plan. |
Examples
This feature is used in conjunction with the Function Table feature for Oracle tenants.
Create a table.
obclient [test]> CREATE TABLE t1(c1 INT);Execute a query.
obclient [SYS]> SELECT * FROM t1;Use the
DBMS_XPLANpackage to query historical plans.Do not specify parameters for queries.
obclient [SYS]> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());The query result is as follows:
+--------------------------------------------------------------------------------------------------+ | COLUMN_VALUE | +--------------------------------------------------------------------------------------------------+ | ================================================================================================ | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)| | | ------------------------------------------------------------------------------------------------ | | |0 |TABLE FULL SCAN|T1 |1 |4 |0 |0 |0 |48 | | | ================================================================================================ | | Outputs & filters: | | ------------------------------------- | | 0 - output([T1.C1]), filter(nil), rowset=16 | | access([T1.C1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([T1.__pk_increment]), range(MIN ; MAX)always true | +--------------------------------------------------------------------------------------------------+ 11 rows in set (0.135 sec)Specify parameters for queries.
obclient [SYS]> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(2574, 'typical', 'xx.xx.xx.xx', 2882, 1004, '836B328E0F78A3AD873B24C07BF02D71', '16232266100518399368'));The query result is as follows:
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | COLUMN_VALUE | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ============================================================================================================================= | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)| | | ----------------------------------------------------------------------------------------------------------------------------- | | |0 |LIMIT | |1 |90376 |1 |1055 |0 |2 | | | |0 |LIMIT | |1 |90376 |1 |1055 |0 |2 | | | |0 |LIMIT | |1 |90376 |1 |1055 |0 |2 | | | |0 |LIMIT | |1 |90376 |1 |1055 |0 |2 | | | |0 |LIMIT | |1 |90376 |1 |1055 |0 |2 | | | |0 |LIMIT | |1 |90376 |1 |1055 |0 |2 | | | |0 |LIMIT | |1 |90376 |1 |1055 |0 |2 | | | |1 |└─HASH OUTER JOIN | |1 |90376 |1 |1055 |0 |170 | | | |1 | HASH OUTER JOIN | |1 |90376 |1 |1055 |0 |170 | | | |1 | HASH OUTER JOIN | |1 |90376 |1 |1055 |0 |170 | | | |1 | HASH OUTER JOIN | |1 |90376 |1 |1055 |0 |170 | | | |1 | HASH OUTER JOIN | |1 |90376 |1 |1055 |0 |170 | | | |1 | HASH OUTER JOIN | |1 |90376 |1 |1055 |0 |170 | | | |1 | HASH OUTER JOIN | |1 |90376 |1 |1055 |0 |170 | | | |2 | ├─SUBPLAN SCAN |VIEW2 |1 |89234 |1 |1055 |0 |2 | | | |2 | ├─SUBPLAN SCAN |VIEW2 |1 |89234 |1 |1055 |0 |2 | | | |2 | ├─SUBPLAN SCAN |VIEW2 |1 |89234 |1 |1055 |0 |2 | | | |2 | ├─SUBPLAN SCAN |VIEW2 |1 |89234 |1 |1055 |0 |2 | | | |2 | ├─SUBPLAN SCAN |VIEW2 |1 |89234 |1 |1055 |0 |2 | | | |2 | ├─SUBPLAN SCAN |VIEW2 |1 |89234 |1 |1055 |0 |2 | | | |2 | ├─SUBPLAN SCAN |VIEW2 |1 |89234 |1 |1055 |0 |2 | | | |3 | │ └─LIMIT | |1 |89234 |1 |1055 |0 |1 | | | |3 | │ LIMIT | |1 |89234 |1 |1055 |0 |1 | | | |3 | │ LIMIT | |1 |89234 |1 |1055 |0 |1 | | | |3 | │ LIMIT | |1 |89234 |1 |1055 |0 |1 | | | |3 | │ LIMIT | |1 |89234 |1 |1055 |0 |1 | | | |3 | │ LIMIT | |1 |89234 |1 |1055 |0 |1 | | | |3 | │ LIMIT | |1 |89234 |1 |1055 |0 |1 | | | |4 | │ └─TABLE FULL SCAN|ALL_VIRTUAL_ASH |11001 |89205 |1 |1055 |0 |375 | | | |4 | │ TABLE FULL SCAN|ALL_VIRTUAL_ASH |11001 |89205 |1 |1055 |0 |375 | | | |4 | │ TABLE FULL SCAN|ALL_VIRTUAL_ASH |11001 |89205 |1 |1055 |0 |375 | | | |4 | │ TABLE FULL SCAN|ALL_VIRTUAL_ASH |11001 |89205 |1 |1055 |0 |375 | | | |4 | │ TABLE FULL SCAN|ALL_VIRTUAL_ASH |11001 |89205 |1 |1055 |0 |375 | | | |4 | │ TABLE FULL SCAN|ALL_VIRTUAL_ASH |11001 |89205 |1 |1055 |0 |375 | | | |4 | │ TABLE FULL SCAN|ALL_VIRTUAL_ASH |11001 |89205 |1 |1055 |0 |375 | | | |5 | └─SUBPLAN SCAN |V$EVENT_NAME |334 |1108 |1 |1055 |0 |2 | | | |5 | SUBPLAN SCAN |V$EVENT_NAME |334 |1108 |1 |1055 |0 |2 | | | |5 | SUBPLAN SCAN |V$EVENT_NAME |334 |1108 |1 |1055 |0 |2 | | | |5 | SUBPLAN SCAN |V$EVENT_NAME |334 |1108 |1 |1055 |0 |2 | | | |5 | SUBPLAN SCAN |V$EVENT_NAME |334 |1108 |1 |1055 |0 |2 | | | |5 | SUBPLAN SCAN |V$EVENT_NAME |334 |1108 |1 |1055 |0 |2 | | | |5 | SUBPLAN SCAN |V$EVENT_NAME |334 |1108 |1 |1055 |0 |2 | | | |6 | └─TABLE FULL SCAN |TENANT_VIRTUAL_EVENT_NAME|334 |1107 |1 |1055 |0 |37 | | | |6 | TABLE FULL SCAN |TENANT_VIRTUAL_EVENT_NAME|334 |1107 |1 |1055 |0 |37 | | | |6 | TABLE FULL SCAN |TENANT_VIRTUAL_EVENT_NAME|334 |1107 |1 |1055 |0 |37 | | | |6 | TABLE FULL SCAN |TENANT_VIRTUAL_EVENT_NAME|334 |1107 |1 |1055 |0 |37 | | | |6 | TABLE FULL SCAN |TENANT_VIRTUAL_EVENT_NAME|334 |1107 |1 |1055 |0 |37 | | | |6 | TABLE FULL SCAN |TENANT_VIRTUAL_EVENT_NAME|334 |1107 |1 |1055 |0 |37 | | | |6 | TABLE FULL SCAN |TENANT_VIRTUAL_EVENT_NAME|334 |1107 |1 |1055 |0 |37 | | | ============================================================================================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([VIEW2.cast(ALL_VIRTUAL_ASH.SVR_IP, VARCHAR2(46 BYTE))], [VIEW2.cast(ALL_VIRTUAL_ASH.SVR_PORT, NUMBER(-1, -85))], [cast(VIEW2.ALL_VIRTUAL_ASH.SAMPLE_ID, | | NUMBER(-1, -85))], [VIEW2.ALL_VIRTUAL_ASH.SAMPLE_TIME], [cast(VIEW2.ALL_VIRTUAL_ASH.TENANT_ID, NUMBER(-1, -85))], [cast(VIEW2.ALL_VIRTUAL_ASH.USER_ID, | | NUMBER(-1, -85))], [cast(VIEW2.ALL_VIRTUAL_ASH.SESSION_ID, NUMBER(-1, -85))], [cast(ora_decode(VIEW2.ALL_VIRTUAL_ASH.SESSION_TYPE, 0, cast('FOREGROUND', | | VARCHAR2(10 BYTE)), cast('BACKGROUND', VARCHAR2(10 BYTE))), VARCHAR2(10 BYTE))], [cast(ora_decode(VIEW2.ALL_VIRTUAL_ASH.EVENT_NO, 0, cast('ON CPU', VARCHAR2(7 | | BYTE)), cast('WAITING', VARCHAR2(7 BYTE))), VARCHAR2(7 BYTE))], [cast(VIEW2.ALL_VIRTUAL_ASH.SQL_ID, VARCHAR2(32 BYTE))], [cast(VIEW2.ALL_VIRTUAL_ASH.PLAN_ID, | | NUMBER(-1, -85))], [cast(VIEW2.ALL_VIRTUAL_ASH.TRACE_ID, VARCHAR2(64 BYTE))], [cast(V$EVENT_NAME.NAME, VARCHAR2(64 BYTE))], [cast(VIEW2.ALL_VIRTUAL_ASH.EVENT_NO, | | NUMBER(-1, -85))], [cast(VIEW2.ALL_VIRTUAL_ASH.EVENT_ID, NUMBER(-1, -85))], [cast(V$EVENT_NAME.PARAMETER1, VARCHAR2(64 BYTE))], [cast(VIEW2.ALL_VIRTUAL_ASH.P1, | | NUMBER(-1, -85))], [cast(V$EVENT_NAME.PARAMETER2, VARCHAR2(64 BYTE))], [cast(VIEW2.ALL_VIRTUAL_ASH.P2, NUMBER(-1, -85))], [cast(V$EVENT_NAME.PARAMETER3, | | VARCHAR2(64 BYTE))], [cast(VIEW2.ALL_VIRTUAL_ASH.P3, NUMBER(-1, -85))], [cast(V$EVENT_NAME.WAIT_CLASS, VARCHAR2(64 BYTE))], [cast(V$EVENT_NAME.WAIT_CLASS_ID, | | NUMBER(-1, -85))], [cast(VIEW2.ALL_VIRTUAL_ASH.TIME_WAITED, NUMBER(-1, -85))], [cast(VIEW2.ALL_VIRTUAL_ASH.SQL_PLAN_LINE_ID, NUMBER(-1, -85))], [cast(VIEW2.ALL_VIRTUAL_ASH.GROUP_ID, | | NUMBER(-1, -85))], [cast(VIEW2.ALL_VIRTUAL_ASH.PLAN_HASH, NUMBER(-1, -85))], [cast(VIEW2.ALL_VIRTUAL_ASH.THREAD_ID, NUMBER(-1, -85))], [cast(VIEW2.ALL_VIRTUAL_ASH.STMT_TYPE, | | NUMBER(-1, -85))], [cast(VIEW2.ALL_VIRTUAL_ASH.TIME_MODEL, NUMBER(-1, -85))], [cast(ora_decode(VIEW2.ALL_VIRTUAL_ASH.IN_PARSE, 1, cast('Y', VARCHAR2(1 | | BYTE)), cast('N', VARCHAR2(1 BYTE))), VARCHAR2(1 BYTE))], [cast(ora_decode(VIEW2.ALL_VIRTUAL_ASH.IN_PL_PARSE, 1, cast('Y', VARCHAR2(1 BYTE)), cast('N', | | VARCHAR2(1 BYTE))), VARCHAR2(1 BYTE))], [cast(ora_decode(VIEW2.ALL_VIRTUAL_ASH.IN_PLAN_CACHE, 1, cast('Y', VARCHAR2(1 BYTE)), cast('N', VARCHAR2(1 BYTE))), | | VARCHAR2(1 BYTE))], [cast(ora_decode(VIEW2.ALL_VIRTUAL_ASH.IN_SQL_OPTIMIZE, 1, cast('Y', VARCHAR2(1 BYTE)), cast('N', VARCHAR2(1 BYTE))), VARCHAR2(1 BYTE))], | | [cast(ora_decode(VIEW2.ALL_VIRTUAL_ASH.IN_SQL_EXECUTION, 1, cast('Y', VARCHAR2(1 BYTE)), cast('N', VARCHAR2(1 BYTE))), VARCHAR2(1 BYTE))], [cast(ora_decode(VIEW2.ALL_VIRTUAL_ASH.IN_PX_EXECUTION, | | 1, cast('Y', VARCHAR2(1 BYTE)), cast('N', VARCHAR2(1 BYTE))), VARCHAR2(1 BYTE))], [cast(ora_decode(VIEW2.ALL_VIRTUAL_ASH.IN_SEQUENCE_LOAD, 1, cast('Y', | | VARCHAR2(1 BYTE)), cast('N', VARCHAR2(1 BYTE))), VARCHAR2(1 BYTE))], [cast(ora_decode(VIEW2.ALL_VIRTUAL_ASH.IN_COMMITTING, 1, cast('Y', VARCHAR2(1 BYTE)), | | cast('N', VARCHAR2(1 BYTE))), VARCHAR2(1 BYTE))], [cast(ora_decode(VIEW2.ALL_VIRTUAL_ASH.IN_STORAGE_READ, 1, cast('Y', VARCHAR2(1 BYTE)), cast('N', VARCHAR2(1 | | BYTE))), VARCHAR2(1 BYTE))], [cast(ora_decode(VIEW2.ALL_VIRTUAL_ASH.IN_STORAGE_WRITE, 1, cast('Y', VARCHAR2(1 BYTE)), cast('N', VARCHAR2(1 BYTE))), VARCHAR2(1 | | BYTE))], [cast(ora_decode(VIEW2.ALL_VIRTUAL_ASH.IN_REMOTE_DAS_EXECUTION, 1, cast('Y', VARCHAR2(1 BYTE)), cast('N', VARCHAR2(1 BYTE))), VARCHAR2(1 BYTE))], | | [cast(ora_decode(VIEW2.ALL_VIRTUAL_ASH.IN_FILTER_ROWS, 1, cast('Y', VARCHAR2(1 BYTE)), cast('N', VARCHAR2(1 BYTE))), VARCHAR2(1 BYTE))], [cast(CASE WHEN | | BITAND(VIEW2.ALL_VIRTUAL_ASH.TIME_MODEL, 16384) > 0 THEN 'Y' ELSE 'N' END, VARCHAR2(1 BYTE))], [cast(CASE WHEN BITAND(VIEW2.ALL_VIRTUAL_ASH.TIME_MODEL, | | 32768) > 0 THEN 'Y' ELSE 'N' END, VARCHAR2(1 BYTE))], [cast(CASE WHEN BITAND(VIEW2.ALL_VIRTUAL_ASH.TIME_MODEL, 65536) > 0 THEN 'Y' ELSE 'N' END, VARCHAR2(1 | | BYTE))], [cast(VIEW2.ALL_VIRTUAL_ASH.PROGRAM, VARCHAR2(64 BYTE))], [cast(VIEW2.ALL_VIRTUAL_ASH.MODULE, VARCHAR2(64 BYTE))], [cast(VIEW2.ALL_VIRTUAL_ASH.ACTION, | | VARCHAR2(64 BYTE), filter(nil) | | limit(1), offset(nil) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 109 rows in set (0.026 sec)
