The DISPLAY_CURSOR function is used to display the details of the 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 ID of the execution plan. If not specified, the plan from the last execution is used. |
| format | Specifies the plan format information. The optional parameters are as follows:
|
| 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 tenant ID to which the plan belongs. The default value is the tenant to which the session is currently connected. |
| sql_handle | The handle (Handle) used to identify the SQL statement for the execution plan. When querying a specific execution plan for an SQL statement, if there are multiple similar SQL statements, you can use sql_handle to precisely specify the target statement. |
| plan_name | The name of the execution plan. |
Examples
MySQL-compatible tenants do not have the Function Table feature, so the DISPLAY_CURSOR function can be directly used in SELECT statements 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 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(67475, 'typical', '11.xxx.xxx.xxx', 32903, 1, 'AE3BAF04FD13DF0DC5B4B9C6EFA9C8CA', '3290318591324336132') FROM DUAL;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 |DISTRIBUTED INSERT| |1 |13 |0 |0 |0 |488 | |1 |└─EXPRESSION | |1 |1 |1 |0 |0 |20 | ================================================================================================ Outputs & filters: ------------------------------------- 0 - output(nil), filter(nil) columns([{test_sqlstat: ({test_sqlstat: (test_sqlstat.a, test_sqlstat.b)})}]), column_values([column_conv(INT,PS:(11,0),NOT NULL,__values.a)], [column_conv(INT,PS:(11,0),NULL,__values.b)]) 1 - output([__values.a], [__values.b]), filter(nil) values({3, 3}) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.173 sec)