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.