DISPLAY_CURSOR|V4.2.5| docs|Distributed Database

DISPLAY_CURSOR

Last Updated：2026-02-11 07:43:39 Updated

The DISPLAY_CURSOR function is used to display the details of the executed query plan.

Applicability

Community Edition of OceanBase Database supports this feature starting from V4.2.5.

Syntax

DBMS_XPLAN.DISPLAY_CURSOR(plan_id      DECIMAL          DEFAULT    0,         -- default value: last plan
                          format       VARCHAR(32)      DEFAULT    'TYPICAL',
                          svr_ip       VARCHAR(64)      DEFAULT    null,   -- default value: server connected by client
                          svr_port     DECIMAL          DEFAULT    0,     -- default value: server connected by client
                          tenant_id    DECIMAL          DEFAULT    0,     -- default value: current tenant
                          sql_handle   VARCHAR(32)      DEFAULT    NULL,
                          plan_name    VARCHAR(32)      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 execution plan of the last query is used.
format	The format of the plan. Valid values: `'BASIC'`: displays a small amount of information, such as operator ID, operator name, operator remarks, and expression information. `'TYPICAL'`: displays basic information, such as operator ID, operator name, operator remarks, optimizer-estimated output rows, optimizer-estimated execution time, and expression information. If the plan has been executed, it also displays the actual feedback information of the plan, such as the actual number of rows and cost. `'ALL'`: displays rich plan information, such as operator ID, operator name, operator remarks, optimizer-estimated output rows, optimizer-estimated execution time, and expression information, as well as the effective Hint, Query Block Name Trace information, Outline information, base table optimization information, constant parameterization information, constraint information, and Plan Note. `'ADVANCED'`: displays rich plan information, which is the same as that displayed when the 'ALL' parameter is specified. In addition, the tree structure information of complex plans is also displayed.
svr_ip	The IP address of the node where the plan is located. The default value is the IP address of the node to which the session is connected.
svr_port	The port number of the node where the plan is located. The default value is the port number of the node to which the session is connected.
tenant_id	The ID of the tenant to which the plan belongs. The default value is the ID of the tenant to which the session is connected.
sql_handle	The handle (Handle) used to identify the SQL statement in the execution plan. When querying a specific execution plan of an SQL statement, you can use the sql_handle parameter to precisely specify the target statement when there are multiple similar SQL statements.
plan_name	The name of the execution plan.

Examples

In a MySQL tenant, you can directly use the DISPLAY_CURSOR function in a SELECT statement to output plan information.

Create a table.

obclient [test]> CREATE TABLE t1(c1 INT);

Execute a query.
```
obclient [test]> SELECT * FROM t1;
```

Use the DBMS_XPLAN package to view historical plans.

Query without specifying parameters.

obclient [test]> SELECT DBMS_XPLAN.DISPLAY_CURSOR() ;

The query result is as follows:

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DBMS_XPLAN.DISPLAY_CURSOR()                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ================================================================================================
|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          |155         |
================================================================================================

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

|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.173 sec)

Query by specifying parameters.

obclient [test]> SELECT DBMS_XPLAN.DISPLAY_CURSOR(294, 'typical', 'xx.xx.xx.xx',  2828, 1002, '07E5B378A3CD3778A58E18DB9AD2A430', '7420493073239164301') FROM DUAL;

The query result is as follows:

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DBMS_XPLAN.DISPLAY_CURSOR(         294,         'typical',         'xx.xx.xx.xx',         2828,         1002,         '07E5B378A3CD3778A58E18DB9AD2A430',         '7420493073239164301'       ) |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                                                                    |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.005 sec)