Generate Explain Plan

Generate/Customize  Explain Plan

0. Creating a Plan Table
1. Generate Explain Plan using SQL Developer
2. Generate Explain Plan using Autotrace in SQL Developer

3. Generate Explain Plan using SQL*Plus
      3.1: Running EXPLAIN PLAN
      3.2: Using utlxpls.sql
      3.3: Using EXPLAIN PLAN with the STATEMENT ID Clause
      3.4: Customizing PLAN_TABLE Output
      3.5: Using EXPLAIN PLAN with the INTO Clause
      3.6: Outline Hit
      3.7: Advanced Format

4. Using SQL*Plus Autotrace

5. Using DBMS_XPLAN.DISPLAY_CURSOR
     5.1: BASIC Format
     5.2: SERIAL Format
     5.3: TYPICAL Format
     5.4: ALL Format
     5.5: ALLSTATS Format
     5.6: ALLSTATS LAST Format
     5.7: Enabling Extra Output
     5.8: Removing Output Sections
     5.9: Advanced Format
     5.10: TYPICAL Format after immediate sql executed

6: Using DBMS_XPLAN.DISPLAY_AWR (From AWR snaps)

0. Creating a Plan Table

The first thing you will need to do is make sure you have a table called PLAN_TABLE available in your schema.
The following script will create it for you if you don't have it already

@$ORACLE_HOME/rdbms/admin/utlxplan.sql


Method 1: Generate Explain Plan using SQL Developer

select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1; (Press F10)
EPlan


Method 2: Generate Explain Plan using Autotrace in SQL Developer

select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1; (Press F6)
AutoTrace


Method 3: Generate Explain Plan using SQL*Plus

Method 3.1: Running EXPLAIN PLAN

— saves the execution plan in the PLAN_TABLE.

SET TIMING ON
set echo on
set linesize 200 pagesize 1000
explain plan for
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;
select * from table(dbms_xplan.display);

output
=======

SQL> set echo on
SQL> set linesize 200 pagesize 1000
SQL> explain plan for select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 3253233075

--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |     1 |     6 |  1928  (99)| 00:00:24 |
|   1 |  SORT AGGREGATE        |             |     1 |     6 |            |          |
|*  2 |   HASH JOIN            |             |   400M|  2288M|  1928  (99)| 00:00:24 |
|*  3 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
|*  4 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C"="T2"."C")
   3 - filter("T1"."C"=1)
   4 - filter("T2"."C"=1)

18 rows selected.

SQL>


Method 3.2: Using utlxpls.sql

SET TIMING ON
set echo on
set linesize 200 pagesize 1000
explain plan for
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;
@$ORACLE_HOME/rdbms/admin/utlxpls.sql

output
======

SQL> set echo on
SQL> set linesize 200 pagesize 1000
SQL> explain plan for select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
..
..
SQL> set markup html preformat on
SQL>
SQL> Rem
SQL> Rem Use the display table function from the dbms_xplan package to display the last
SQL> Rem explain plan. Force serial option for backward compatibility
SQL> Rem
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 3253233075

--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |     1 |     6 |  1928  (99)| 00:00:24 |
|   1 |  SORT AGGREGATE        |             |     1 |     6 |            |          |
|*  2 |   HASH JOIN            |             |   400M|  2288M|  1928  (99)| 00:00:24 |
|*  3 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
|*  4 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C"="T2"."C")
   3 - filter("T1"."C"=1)
   4 - filter("T2"."C"=1)

18 rows selected.

SQL>


Method 3.3: Using EXPLAIN PLAN with the STATEMENT ID Clause

SET TIMING ON
set echo on
set linesize 200 pagesize 1000
EXPLAIN PLAN SET STATEMENT_ID = 'MY_PLAN' FOR
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;
SELECT PLAN_TABLE_OUTPUT 
FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'MY_PLAN','TYPICAL'));

output
=======

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'MY_PLAN' FOR
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;  2

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT
  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'MY_PLAN','TYPICAL'));  2

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 3253233075

--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |     1 |     6 |  1928  (99)| 00:00:24 |
|   1 |  SORT AGGREGATE        |             |     1 |     6 |            |          |
|*  2 |   HASH JOIN            |             |   400M|  2288M|  1928  (99)| 00:00:24 |
|*  3 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
|*  4 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C"="T2"."C")
   3 - filter("T1"."C"=1)
   4 - filter("T2"."C"=1)

18 rows selected.

SQL>


Method 3.4: Customizing PLAN_TABLE Output

SET TIMING ON
set echo on
set linesize 200 pagesize 1000

EXPLAIN PLAN SET STATEMENT_ID = 'MY_PLAN' FOR
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;

col Plan for a70
SELECT cardinality "Rows",
   lpad(' ',level-1)||operation||' '||options||' '||object_name "Plan"
  FROM PLAN_TABLE
CONNECT BY prior id = parent_id
        AND prior statement_id = statement_id
  START WITH id = 0
        AND statement_id = 'MY_PLAN'
  ORDER BY id;

Output
=======

SQL> SELECT cardinality "Rows",
   lpad(' ',level-1)||operation||' '||options||' '||object_name "Plan"
  2    3    FROM PLAN_TABLE
  4  CONNECT BY prior id = parent_id
  5          AND prior statement_id = statement_id
  6    START WITH id = 0
  7          AND statement_id = 'MY_PLAN'
  8    ORDER BY id;


      Rows Plan
---------- --------------------------------------------------------------
         1 SELECT STATEMENT
         1  SORT AGGREGATE
 400000000   HASH JOIN
     20000    INDEX FAST FULL SCAN TEST_C_INDX
     20000    INDEX FAST FULL SCAN TEST_C_INDX

SQL>

(OR)

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'MY_PLAN' FOR
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;  

Explained.

Elapsed: 00:00:00.01
SQL> COL PLAN FORMAT A80
SELECT LPAD( ' ', 2 * ( LEVEL - 1 ) ) ||
SQL>   2         OPERATION || ' ' ||
  3         OPTIONS || ' ' ||
  4         OBJECT_NAME || ' ' ||
  5         OBJECT_TYPE PLAN,
  6         COST
  7    FROM PLAN_TABLE
  8   WHERE STATEMENT_ID = 'MY_PLAN'
  9  CONNECT BY PRIOR ID = PARENT_ID
 10         AND PRIOR STATEMENT_ID = 'MY_PLAN'
 11   START WITH ID =1;

PLAN                                           COST
--------------------------------------------  --------------
SORT AGGREGATE
  HASH JOIN                                     1928
    INDEX FAST FULL SCAN TEST_C_INDX INDEX      12
    INDEX FAST FULL SCAN TEST_C_INDX INDEX      12

Elapsed: 00:00:00.00
SQL>


Method 3.5: Using EXPLAIN PLAN with the INTO Clause

SET TIMING ON
set echo on
set linesize 200 pagesize 1000

explain plan into PLAN_TABLE for
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;

select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'TYPICAL'));

Output
=======

SQL> explain plan into PLAN_TABLE for
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;  2

Explained.

Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'TYPICAL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 3253233075

--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |     1 |     6 |  1928  (99)| 00:00:24 |
|   1 |  SORT AGGREGATE        |             |     1 |     6 |            |          |
|*  2 |   HASH JOIN            |             |   400M|  2288M|  1928  (99)| 00:00:24 |
|*  3 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
|*  4 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C"="T2"."C")
   3 - filter("T1"."C"=1)
   4 - filter("T2"."C"=1)

18 rows selected.

Elapsed: 00:00:00.01
SQL>


Method 3.6: Outline Hit

set echo on
set linesize 200 pagesize 1000

explain plan into PLAN_TABLE for
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;

select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'OUTLINE'));

Output
=======

SQL> select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'OUTLINE'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 3253233075

--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |     1 |     6 |  1928  (99)| 00:00:24 |
|   1 |  SORT AGGREGATE        |             |     1 |     6 |            |          |
|*  2 |   HASH JOIN            |             |   400M|  2288M|  1928  (99)| 00:00:24 |
|*  3 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
|*  4 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$1" "T2"@"SEL$1")
      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
      INDEX_FFS(@"SEL$1" "T2"@"SEL$1" ("TEST"."C"))
      INDEX_FFS(@"SEL$1" "T1"@"SEL$1" ("TEST"."C"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C"="T2"."C")
   3 - filter("T1"."C"=1)
   4 - filter("T2"."C"=1)

35 rows selected.

SQL>


Method 3.7: Advanced Format

set echo on
set linesize 200 pagesize 1000

explain plan into PLAN_TABLE for
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;

select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'Advanced'));

Output
=======

SQL> select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'Advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3253233075

--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |     1 |     6 |  1928  (99)| 00:00:24 |
|   1 |  SORT AGGREGATE        |             |     1 |     6 |            |          |
|*  2 |   HASH JOIN            |             |   400M|  2288M|  1928  (99)| 00:00:24 |
|*  3 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
|*  4 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / T1@SEL$1
   4 - SEL$1 / T2@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$1" "T2"@"SEL$1")
      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
      INDEX_FFS(@"SEL$1" "T2"@"SEL$1" ("TEST"."C"))
      INDEX_FFS(@"SEL$1" "T1"@"SEL$1" ("TEST"."C"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C"="T2"."C")
   3 - filter("T1"."C"=1)
   4 - filter("T2"."C"=1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (#keys=1)
   3 - "T1"."C"[NUMBER,22]
   4 - "T2"."C"[NUMBER,22]

50 rows selected.

SQL>


4. Using SQL*Plus Autotrace

SQL> SET AUTOTRACE TRACEONLY
SQL> select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;

Elapsed: 00:02:04.11

Execution Plan
----------------------------------------------------------
Plan hash value: 3253233075

--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |     1 |     6 |  1928  (99)| 00:00:24 |
|   1 |  SORT AGGREGATE        |             |     1 |     6 |            |          |
|*  2 |   HASH JOIN            |             |   400M|  2288M|  1928  (99)| 00:00:24 |
|*  3 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
|*  4 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C"="T2"."C")
   3 - filter("T1"."C"=1)
   4 - filter("T2"."C"=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         90  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

--------------------------------------
set autotrace off
set autotrace on
set autotrace traceonly

set autotrace on explain
set autotrace on statistics
set autotrace on explain statistics

set autotrace traceonly explain
set autotrace traceonly statistics
set autotrace traceonly explain statistics

set autotrace off explain
set autotrace off statistics
set autotrace off explain statistics
----------------------------------------


5. Using DBMS_XPLAN.DISPLAY_CURSOR

Method 5.1: BASIC Format

SQL> SELECT SQL_ID, CHILD_NUMBER FROM V$SQL WHERE
  SQL_TEXT LIKE 'select count(*) from test t1%';  2

SQL_ID        CHILD_NUMBER
------------- ------------
dkz7v96ym42c6            0

SQL>

set echo on
set linesize 200 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'BASIC'));

Output
=======

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'BASIC'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1

Plan hash value: 3253233075

----------------------------------------------
| Id  | Operation              | Name        |
----------------------------------------------
|   0 | SELECT STATEMENT       |             |
|   1 |  SORT AGGREGATE        |             |
|   2 |   HASH JOIN            |             |
|   3 |    INDEX FAST FULL SCAN| TEST_C_INDX |
|   4 |    INDEX FAST FULL SCAN| TEST_C_INDX |
----------------------------------------------


16 rows selected.

SQL>


Method 5.2: SERIAL Format

set echo on
set linesize 200 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'SERIAL'));

Output
=======

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'SERIAL'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  dkz7v96ym42c6, child number 0
-------------------------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1

Plan hash value: 3253233075

--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |       |       |  1928 (100)|          |
|   1 |  SORT AGGREGATE        |             |     1 |     6 |            |          |
|*  2 |   HASH JOIN            |             |   400M|  2288M|  1928  (99)| 00:00:24 |
|*  3 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
|*  4 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C"="T2"."C")
   3 - filter("T1"."C"=1)
   4 - filter("T2"."C"=1)


23 rows selected.

SQL>


Method 5.3: TYPICAL Format

set echo on
set linesize 200 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'TYPICAL'));

Output:
=======

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'TYPICAL'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  dkz7v96ym42c6, child number 0
-------------------------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1

Plan hash value: 3253233075

--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |       |       |  1928 (100)|          |
|   1 |  SORT AGGREGATE        |             |     1 |     6 |            |          |
|*  2 |   HASH JOIN            |             |   400M|  2288M|  1928  (99)| 00:00:24 |
|*  3 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
|*  4 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C"="T2"."C")
   3 - filter("T1"."C"=1)
   4 - filter("T2"."C"=1)


23 rows selected.

SQL>


Method 5.4: ALL Format

set echo on
set linesize 200 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALL'));

Output:
=======

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALL'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  dkz7v96ym42c6, child number 0
-------------------------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1

Plan hash value: 3253233075

--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |       |       |  1928 (100)|          |
|   1 |  SORT AGGREGATE        |             |     1 |     6 |            |          |
|*  2 |   HASH JOIN            |             |   400M|  2288M|  1928  (99)| 00:00:24 |
|*  3 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
|*  4 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / T1@SEL$1
   4 - SEL$1 / T2@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C"="T2"."C")
   3 - filter("T1"."C"=1)
   4 - filter("T2"."C"=1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (#keys=1)
   3 - "T1"."C"[NUMBER,22]
   4 - "T2"."C"[NUMBER,22]


38 rows selected.

SQL>


Method 5.5: ALLSTATS Format

set echo on
set linesize 200 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALLSTATS'));

Output
=======

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALLSTATS'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  dkz7v96ym42c6, child number 0
-------------------------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1

Plan hash value: 3253233075

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |      9 |        |      7 |00:13:57.84 |     630 |       |       |          |
|   1 |  SORT AGGREGATE        |             |      9 |      1 |      7 |00:13:57.84 |     630 |       |       |          |
|*  2 |   HASH JOIN            |             |      9 |    400M|   2952M|00:08:55.96 |     745 |  1643K|  1643K|     9/0/0|
|*  3 |    INDEX FAST FULL SCAN| TEST_C_INDX |      9 |  20000 |    180K|00:00:00.04 |     405 |       |       |          |
|*  4 |    INDEX FAST FULL SCAN| TEST_C_INDX |      9 |  20000 |    147K|00:00:00.13 |     340 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C"="T2"."C")
   3 - filter("T1"."C"=1)
   4 - filter("T2"."C"=1)


23 rows selected.

SQL>


Method 5.6: ALLSTATS LAST Format

set echo on
set linesize 200 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALLSTATS LAST'));

Output
=======

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  dkz7v96ym42c6, child number 0
-------------------------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1

Plan hash value: 3253233075

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |      1 |        |      0 |00:00:00.01 |       0 |       |       |          |
|   1 |  SORT AGGREGATE        |             |      1 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|*  2 |   HASH JOIN            |             |      1 |    400M|     92M|00:00:16.84 |      59 |  1643K|  1643K| 2040K (0)|
|*  3 |    INDEX FAST FULL SCAN| TEST_C_INDX |      1 |  20000 |  20000 |00:00:00.01 |      45 |       |       |          |
|*  4 |    INDEX FAST FULL SCAN| TEST_C_INDX |      1 |  20000 |   4608 |00:00:00.01 |      14 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C"="T2"."C")
   3 - filter("T1"."C"=1)
   4 - filter("T2"."C"=1)


23 rows selected.

SQL>


Method 5.7: Enabling Extra Output

set echo on
set linesize 200 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALLSTATS LAST +PEEKED_BINDS +PROJECTION +ALIAS +PREDICATE +COST +BYTES'));

Output
======

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALLSTATS LAST +PEEKED_BINDS +PROJECTION +ALIAS +PREDICATE +COST +BYTES'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dkz7v96ym42c6, child number 0
-------------------------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1

Plan hash value: 3253233075

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |      1 |        |       |  1928 (100)|      0 |00:00:00.01 |       0 |       |    |          |
|   1 |  SORT AGGREGATE        |             |      1 |      1 |     6 |            |      0 |00:00:00.01 |       0 |       |    |          |
|*  2 |   HASH JOIN            |             |      1 |    400M|  2288M|  1928  (99)|     92M|00:00:16.84 |      59 |  1643K|  1643K| 2040K (0)|
|*  3 |    INDEX FAST FULL SCAN| TEST_C_INDX |      1 |  20000 | 60000 |    12   (0)|  20000 |00:00:00.01 |      45 |       |    |          |
|*  4 |    INDEX FAST FULL SCAN| TEST_C_INDX |      1 |  20000 | 60000 |    12   (0)|   4608 |00:00:00.01 |      14 |       |    |          |
------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / T1@SEL$1
   4 - SEL$1 / T2@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C"="T2"."C")
   3 - filter("T1"."C"=1)
   4 - filter("T2"."C"=1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (#keys=1)
   3 - "T1"."C"[NUMBER,22]
   4 - "T2"."C"[NUMBER,22]


38 rows selected.

SQL>


Method 5.8: Removing Output Sections

set echo on
set linesize 200 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALLSTATS LAST -NOTE -ROWS -PREDICATE'));

Output
======

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALLSTATS LAST -NOTE -ROWS -PREDICATE'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  dkz7v96ym42c6, child number 0
-------------------------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1

Plan hash value: 3253233075

------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Starts | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|   1 |  SORT AGGREGATE        |             |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|   2 |   HASH JOIN            |             |      1 |     92M|00:00:16.84 |      59 |  1643K|  1643K| 2040K (0)|
|   3 |    INDEX FAST FULL SCAN| TEST_C_INDX |      1 |  20000 |00:00:00.01 |      45 |       |       |          |
|   4 |    INDEX FAST FULL SCAN| TEST_C_INDX |      1 |   4608 |00:00:00.01 |      14 |       |       |          |
------------------------------------------------------------------------------------------------------------------


16 rows selected.

SQL>


Method 5.9: Advanced Format

set echo on
set linesize 200 pagesize 1000
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'Advanced'));

Output
=======

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'Advanced'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  dkz7v96ym42c6, child number 0
-------------------------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1

Plan hash value: 3253233075

--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |       |       |  1928 (100)|          |
|   1 |  SORT AGGREGATE        |             |     1 |     6 |            |          |
|*  2 |   HASH JOIN            |             |   400M|  2288M|  1928  (99)| 00:00:24 |
|*  3 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
|*  4 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / T1@SEL$1
   4 - SEL$1 / T2@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_FFS(@"SEL$1" "T1"@"SEL$1" ("TEST"."C"))
      INDEX_FFS(@"SEL$1" "T2"@"SEL$1" ("TEST"."C"))
      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
      USE_HASH(@"SEL$1" "T2"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C"="T2"."C")
   3 - filter("T1"."C"=1)
   4 - filter("T2"."C"=1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (#keys=1)
   3 - "T1"."C"[NUMBER,22]
   4 - "T2"."C"[NUMBER,22]


55 rows selected.

SQL>


Method 5.10: TYPICAL Format after immediate sql executed

The typical way to display an execution plan for a SQL statement that was just executed
We could have also been set to NULL to produce the same result

set echo on
set linesize 200 pagesize 1000
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

Output
=======

SQL> set echo on
SQL> set linesize 200 pagesize 1000
SQL> select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;

  COUNT(*)
----------
 400000000

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  dkz7v96ym42c6, child number 1
-------------------------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1

Plan hash value: 3253233075

--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |       |       |  1928 (100)|          |
|   1 |  SORT AGGREGATE        |             |     1 |     6 |            |          |
|*  2 |   HASH JOIN            |             |   400M|  2288M|  1928  (99)| 00:00:24 |
|*  3 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
|*  4 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."C"="T2"."C")
   3 - filter("T1"."C"=1)
   4 - filter("T2"."C"=1)


23 rows selected.

SQL>


-----
set echo on
set linesize 200 pagesize 1000
ALTER SESSION SET STATISTICS_LEVEL='ALL';
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'Advanced'));
----


Method 6: Using DBMS_XPLAN.DISPLAY_AWR (From AWR snaps)

set echo on
set linesize 200 pagesize 1000
select * from table(dbms_xplan.display_awr('dkz7v96ym42c6'));

Output
=======

SQL> select * from table(dbms_xplan.display_awr('dkz7v96ym42c6'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID dkz7v96ym42c6
--------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1

Plan hash value: 3253233075

--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |       |       |  1928 (100)|          |
|   1 |  SORT AGGREGATE        |             |     1 |     6 |            |          |
|   2 |   HASH JOIN            |             |   400M|  2288M|  1928  (99)| 00:00:24 |
|   3 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------


16 rows selected.

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala