DISPLAY

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;

-- Connect to the database and create tables.
CREATE TABLE t1 (
    c1 INT
);

CREATE TABLE plan_table (
    statement_id         VARCHAR2(30),
    plan_id              INT,
    gmt_create           TIMESTAMP,
    remarks              VARCHAR2(4000),
    operator             VARCHAR2(255),
    options              VARCHAR2(255),
    object_node          VARCHAR2(40),
    object_owner         VARCHAR2(128),
    object_name          VARCHAR2(128),
    object_alias         VARCHAR2(261),
    object_instance      INT,
    object_type          VARCHAR2(30),
    optimizer            VARCHAR2(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            VARCHAR2(4000),
    partition_start      VARCHAR2(4000),
    partition_stop       VARCHAR2(4000),
    partition_id         INT,
    other                VARCHAR2(4000),
    distribution         VARCHAR2(64),
    cpu_cost             INT,
    io_cost              INT,
    temp_space           INT,
    access_predicates    VARCHAR2(4000),
    filter_predicates    VARCHAR2(4000),
    startup_predicates   VARCHAR2(4000),
    projection           VARCHAR2(4000),
    special_predicates   VARCHAR2(4000),
    time                 INT,
    qblock_name          VARCHAR2(128),
    other_xml            VARCHAR2(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 query plan.
SELECT * FROM table(dbms_xplan.display);
+-------------------------------------------------------------------+
| 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 |
+-------------------------------------------------------------------+