The DISPLAY function displays the execution plan generated for an SQL statement after the statement is explained by EXPLAIN.
Syntax
TYPE DBMS_XPLAN_TYPE_TABLE IS TABLE OF VARCHAR2(4000);
DBMS_XPLAN.DISPLAY(format VARCHAR2 DEFAULT 'TYPICAL',
statement_id VARCHAR2 DEFAULT null,
table_name VARCHAR2 DEFAULT 'PLAN_TABLE',
filter_preds VARCHAR2 DEFAULT null)
RETURN DBMS_XPLAN_TYPE_TABLE;
Parameters
| Parameter | Description |
|---|---|
| format | The plan format. Valid values:
|
| statement_id | The identifier of the EXPLAIN query. You can execute the EXPLAIN SET STATEMENT_ID='XXX' command to set the identifier. |
| table_name | The target table for saving the EXPLAIN plan. The default value is PLAN_TABLE. |
| filter_preds | Additional filter conditions for the PLAN_TABLE, which are used to filter specific operators or plans. |
Examples
Because the table function feature is not available to MySQL tenants, you can directly use the DISPLAY function in a SELECT statement to display plan information.
-- Connect to the database and create a table.
CREATE TABLE t1 (c1 INT);
CREATE TABLE plan_table (
statement_id VARCHAR(30),
plan_id INT,
gmt_create TIMESTAMP,
remarks VARCHAR(4000),
operator VARCHAR(255),
options VARCHAR(255),
object_node VARCHAR(40),
object_owner VARCHAR(128),
object_name VARCHAR(128),
object_alias VARCHAR(261),
object_instance INT,
object_type VARCHAR(30),
optimizer VARCHAR(4000),
search_columns INT,
id INT,
parent_id INT,
depth INT,
position INT,
is_last_child INT,
cost INT,
cardinality INT,
bytes INT,
rowset INT,
other_tag VARCHAR(4000),
partition_start VARCHAR(4000),
partition_stop VARCHAR(4000),
partition_id INT,
other VARCHAR(4000),
distribution VARCHAR(64),
cpu_cost INT,
io_cost INT,
temp_space INT,
access_predicates VARCHAR(4000),
filter_predicates VARCHAR(4000),
startup_predicates VARCHAR(4000),
projection VARCHAR(4000),
special_predicates VARCHAR(4000),
time INT,
qblock_name VARCHAR(128),
other_xml VARCHAR(4000)
);
-- Use the EXPLAIN statement to view the query plan.
EXPLAIN SELECT * FROM t1;
+-------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------+
| =============================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ----------------------------------------------- |
| |0 |TABLE FULL SCAN|t1 |1 |4 | |
| =============================================== |
| 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 |
+-------------------------------------------------------------------+
11 rows in set
-- Use the DBMS_XPLAN package to view the historical plan.
SELECT dbms_xplan.display() FROM dual;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dbms_xplan.display() |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ===============================================
|ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)|
-----------------------------------------------
|0 |TABLE FULL SCAN|t1 |1 |4 |
===============================================
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