The DISPLAY_CURSOR function displays the details of an executed query plan.
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
)
RETURN DBMS_XPLAN_TYPE_TABLE;
Parameters
Parameter |
Description |
|---|---|
| plan_id | The plan ID. 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. By default, the tenant that the session connects to is used. |
Examples
Because the table function feature is not available to MySQL tenants, you can directly use the DISPLAY_CURSOR function in a SELECT statement to output plan information.
/* Create a table. */
obclient> CREATE TABLE t1(c1 INT);
Query OK, 0 rows affected
/* Execute a query. */
obclient> SELECT * FROM t1;
Empty set
/* Use the DBMS_XPLAN package to display an executed query plan. */
obclient> SELECT DBMS_XPLAN.DISPLAY_CURSOR() ;
+--------------------------------------------------------------------------------------------------+
| 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 |2 |0 |0 |0 |0 | |
| ================================================================================================ |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T1.C1]), filter(nil), rowset=256 |
| access([T1.C1]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([T1.__pk_increment]), range(MIN ; MAX)always true |
+--------------------------------------------------------------------------------------------------+
/* You can also specify tenant_id, svr_ip, svr_port, and plan_id to display the information about the specified plan. */
obclient> SELECT DBMS_XPLAN.DISPLAY_CURSOR(
123,
'typical',
'127.0.0.1',
2828,
1002
) FROM DUAL;
+--------------------------------------------------------------------------------+
| dbms_xplan.display_cursor(
123,
'typical',
'127.x.x.1',
2828,
1002
) |
+--------------------------------------------------------------------------------+
| |
+--------------------------------------------------------------------------------+
1 row in set