Oracle Explain plan

Creating a PLAN_TABLE

CONNECT HR/your_password
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created.

EXPLAIN PLAN FOR
 SELECT last_name FROM employees;

• UTLXPLS.SQL

  This script displays the plan table output for serial processing. Example 13-2, "EXPLAIN PLAN Output" is an example of the plan table output when using the UTLXPLS.SQL script.

• UTLXPLP.SQL

  This script displays the plan table output including parallel execution columns.

• DBMS_XPLAN.DISPLAY procedure

  This procedure accepts options for displaying the plan table output. You can specify:

  • A plan table name if you are using a table different than PLAN_TABLE

  • A statement Id if you have set a statement Id with the EXPLAIN PLAN

  • A format option that determines the level of detail: BASIC, SERIAL, and TYPICAL, ALL,

  Some examples of the use of DBMS_XPLAN to display PLAN_TABLE output are:

  SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
  
  SELECT PLAN_TABLE_OUTPUT
   FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));