The explain plan command
For select, update, delete, and insert statements, the explain plan command generates and stores information about the execution plan chosen by the query optimizer in a table called PLAN TABLE. This table is created when you run the SQL script $ORACLE HOME/rdbms/admin/utlxplan.sql in SQL*Plus. The PLAN TABLE then can be queried using a select statement.
The syntax of the explain plan command is as follows:
explain plan set statement id = ’
Example: We want to generate the execution plan for the query “List the name, job, salary and department of all employees whose
salary is not within the job’s salary range.”
explain plan set statement id = ’MYPLAN’ for select ENAME, JOB, SAL, DNAME from EMP, DEPT where EMP.DEPTNO = DEPT.DEPTNO and not exists (select _ from SALGRADE where EMP.SAL between LOSAL and HISAL);
Note that every execution plan gets a statement id, which is just some string. The rest is just a “normal” select statement.
The above explain plan statement causes the query optimizer to insert data about the execution plan for the query into PLAN TABLE. The following query can be used to view to content of the plan table in an indented format, which reflects the tree structure of the plan (the right-most entries correspond to the leafs of the query tree).
Note that before creating a new plan with the same id, you have to delete all entries from PLAN TABLE (delete from PLAN TABLE;)
select substr(lpad(’ ’,2*(level-1)),1,8)|| substr(operation,1,18) "OPERAsubstr(options,1,12) "OPTIONS", substr(object_name,1,16) object_name, id, parent_id, cost, cardinality, bytes, filter_predicates
from plan_table
start with id=0 and statement_id = ’MYPLAN’
connect by prior id = parent_id and statement_id = ’MYPLAN’;
