The DISPLAY_CURSOR function displays the details of an executed query plan.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
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
/* 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. In an Oracle tenant, it is used together with the table function feature. */
obclient> SELECT * FROM TABLE(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 * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
123,
'typical',
'127.0.0.1',
2828,
1002)) ;
+--------------------------------------------------------------------------------+
| dbms_xplan.display_cursor(
123,
'typical',
'127.x.x.1',
2828,
1002
) |
+--------------------------------------------------------------------------------+
| |
+--------------------------------------------------------------------------------+
1 row in set