The DISPLAY_CURSOR function is used to display the details of executed query plans.
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 last executed plan is used. |
| format | Specifies the plan format information. The following parameters are available:
|
| svr_ip | The IP address of the node where the plan resides. By default, it is the IP address of the node to which the session is connected. |
| svr_port | Port number of the node where the plan resides. By default, it is the port number of the node to which the session is connected. |
| tenant_id | Tenant ID of the plan. By default, it is the tenant to which the current session is connected. |
| sql_handle | An execution plan is a handle for an SQL statement. When querying a specific execution plan of an SQL statement, if multiple similar SQL statements exist, you can use the sql_handle to precisely identify the target statement. |
| plan_name | The name of the execution plan. |
Examples
In user tenants, since the Function Table feature is not available, 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 the query.
obclient [test]> SELECT * FROM t1;Use the
DBMS_XPLANpackage to view historical execution 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)Parameterized queries.
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)