Oracle EXPLAIN PLAN


The EXPLAIN PLAN statement will show SQL statement execution plans composssed by the optimiser for all DML statements (SELECT, INSERT, UPDATE and DELETE). The EXPLAIN PLAN is the sequence of operations which the optimiser chooses to execute in order to execute a DML statement. Included is the order in which tables are referenced, method in which each table is accessed and join methods used. Oracle and SQL Trace and TKPROF can also be used to show SQL statement consumption of resources.

The EXPLAIN PLAN statement requires that a table be created to hold results. Use the UTLXPLAN.SQL to create the PLAN_TABLE as shown below.

SQL> desc plan_table;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 STATEMENT_ID                                                   VARCHAR2(30)
 TIMESTAMP                                                      DATE
 REMARKS                                                        VARCHAR2(80)
 OPERATION                                                      VARCHAR2(30)
 OPTIONS                                                        VARCHAR2(30)
 OBJECT_NODE                                                    VARCHAR2(128)
 OBJECT_OWNER                                                   VARCHAR2(30)
 OBJECT_NAME                                                    VARCHAR2(30)
 OBJECT_INSTANCE                                                NUMBER(38)
 OBJECT_TYPE                                                    VARCHAR2(30)
 OPTIMIZER                                                      VARCHAR2(255)
 SEARCH_COLUMNS                                                 NUMBER
 ID                                                             NUMBER(38)
 PARENT_ID                                                      NUMBER(38)
 POSITION                                                       NUMBER(38)
 COST                                                           NUMBER(38)
 CARDINALITY                                                    NUMBER(38)
 BYTES                                                          NUMBER(38)
 OTHER_TAG                                                      VARCHAR2(255)
 PARTITION_START                                                VARCHAR2(255)
 PARTITION_STOP                                                 VARCHAR2(255)
 PARTITION_ID                                                   NUMBER(38)
 OTHER                                                          LONG
 DISTRIBUTION                                                   VARCHAR2(30)

For the select statement below the explain table can be displayed using the following scripts. Some examples follow.

EXPLAIN PLAN SELECT * FROM from demo.employee;

SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options
	||' '||object_name
	||' '||DECODE(id, 0, 'Cost = '||position) "Query Plan"
FROM plan_table

Query Plan
------------------------------------------------
SELECT STATEMENT   Cost =
TABLE ACCESS FULL EMPLOYEE
explain plan for
select	 location.regional_group "Group"
	,department.name "Dept"
	,manager.first_name||' '||manager.last_name "Manager"
	,trim(employee.first_name||' '||employee.last_name) "Employee"
	,employee.hire_date "Hire Date"
	,employee.salary "Salary"
from location,department,employee manager,employee
where department.location_id = location.location_id
and employee.department_id = department.department_id
and manager.employee_id = employee.manager_id
order by 1,2,3,4;
Query Plan
----------------------------------------------------------------------------------------------------
SELECT STATEMENT   Cost =
SORT ORDER BY
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL EMPLOYEE
TABLE ACCESS BY INDEX ROWID EMPLOYEE
INDEX UNIQUE SCAN I_EMPLOYEE$EMPLOYEE_ID
TABLE ACCESS BY INDEX ROWID DEPARTMENT
INDEX UNIQUE SCAN I_DEPARTMENT$DEPARTMENT_ID
TABLE ACCESS BY INDEX ROWID LOCATION
INDEX UNIQUE SCAN I_LOCATION$LOCATION_ID

12 rows selected.