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(table_name VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id VARCHAR2 DEFAULT null,
format VARCHAR2 DEFAULT 'TYPICAL',
filter_preds VARCHAR2 DEFAULT null)
RETURN DBMS_XPLAN_TYPE_TABLE;
Parameters
| Parameter | Description |
|---|---|
| table_name | The table that stores the execution plan. The default value is PLAN_TABLE. |
| statement_id | The ID of the statement explained by EXPLAIN. You can run the EXPLAIN SET STATEMENT_ID='XXX' command to set the statement ID. |
| format | The plan format. Valid values:
|
| filter_preds | The additional filter predicates to restrict the operator or plan selected from PLAN_TABLE. |
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.
/* Create a table. */
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 EXPLAIN to explain an SQL statement. */
obclient> EXPLAIN SELECT * FROM t1;
+-------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------+
| ========================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ------------------------------------------ |
| |0 |TABLE SCAN|T1 |1 |2 | |
| ========================================== |
| 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 |
+-------------------------------------------------------------------+
/* Use the DBMS_XPLAN package to display an executed query plan. */
obclient> SELECT DBMS_XPLAN.DISPLAY() FROM DUAL;
+-------------------------------------------------------------------+
| COLUMN_VALUE |
+-------------------------------------------------------------------+
| ========================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| ------------------------------------------ |
| |0 |TABLE SCAN|T1 |1 |2 | |
| ========================================== |
| 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 |
+-------------------------------------------------------------------+