PT - Execution Plan using EXPLAIN PLAN FOR Command

SQL> EXPLAIN PLAN FOR select e.ename, d.dname from emp e, dept d where e.dept_no=d.dept_no and e.dept_no=50;

Explained.

SQL> set linsize 200
SP2-0158: unknown SET option "linsize"
SQL> set linessize 200
SP2-0158: unknown SET option "linessize"
SQL> set linesize 200
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1706465873

------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |    23 |  1173 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |         |    23 |  1173 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID        | DEPT    |     1 |    26 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                 | DEPT_PK |     1 |       |     0   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |    23 |   575 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | DEPT_IX |    23 |       |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------

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

   3 - access("D"."DEPT_NO"=50)
   5 - access("E"."DEPT_NO"=50)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

22 rows selected.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1706465873

------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |    23 |  1173 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |         |    23 |  1173 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID        | DEPT    |     1 |    26 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                 | DEPT_PK |     1 |       |     0   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |    23 |   575 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | DEPT_IX |    23 |       |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------

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

   3 - access("D"."DEPT_NO"=50)
   5 - access("E"."DEPT_NO"=50)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

22 rows selected.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1706465873

------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |    23 |  1173 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |         |    23 |  1173 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID        | DEPT    |     1 |    26 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                 | DEPT_PK |     1 |       |     0   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |    23 |   575 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | DEPT_IX |    23 |       |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------

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

   3 - access("D"."DEPT_NO"=50)
   5 - access("E"."DEPT_NO"=50)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

22 rows selected.

SQL> SELECT LPAD(' ', 2*(LEVEL-1)) || OPERATION || ' ' || OPTIONS || ' ' ||OBJECT_NAME || ' ' || DECODE(ID,0,'COST = ' || POSITION) "QUERY PLAN" FROM PLAN_TABLE START WITH ID=0 CONNECT BY PRIOR ID = PARENT_ID;

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT STATEMENT   COST = 3
  NESTED LOOPS
    TABLE ACCESS BY INDEX ROWID DEPT
      INDEX UNIQUE SCAN DEPT_PK
    TABLE ACCESS BY INDEX ROWID BATCHED EMP
      INDEX RANGE SCAN DEPT_IX

6 rows selected.

SQL>