The DISPLAY_CURSOR function displays the details of an executed query plan.
Limitations
For V4.3.5, this feature is supported starting from V4.3.5 BP2.
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 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. |
| sql_handle | The execution plan used to identify the handle of an SQL statement. When querying the specific execution plan of an SQL statement, the sql_handle can be used to precisely specify the target statement in cases where multiple similar SQL statements exist. |
| plan_name | The name of the execution plan. |
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 [test]> CREATE TABLE t1(c1 INT);Execute a query.
obclient [test]> SELECT * FROM t1;Use the
DBMS_XPLANpackage to view historical plans.Query without 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 with 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)