==================================================================================== Hands-on exercise to demonstrate the Execution plan via EXPLAIN_PLAN 1. Create the PLAN_TABLE table used to store the execution plan 2. Ask the database server to explain the execution plan for a specific SQL statement Note: always truncate the PLAN_TABLE before! 3. Show the execution plan 4. Show different execution plans for some statements ------------------------------------------------------------------------------------ Rem Rem utlxplan.sql from 9.2.0.6 Rem Rem This is the format for the table that is used by the EXPLAIN PLAN Rem statement. The explain statement requires the presence of this Rem table in order to store the descriptions of the row sources. Rem --drop table PLAN_TABLE; -- if neccessary create table PLAN_TABLE ( statement_id varchar2(30), timestamp date, remarks varchar2(80), operation varchar2(30), options varchar2(255), object_node varchar2(128), object_owner varchar2(30), object_name varchar2(30), object_instance numeric, object_type varchar2(30), optimizer varchar2(255), search_columns number, id numeric, parent_id numeric, position numeric, cost numeric, cardinality numeric, bytes numeric, other_tag varchar2(255), partition_start varchar2(255), partition_stop varchar2(255), partition_id numeric, other long, distribution varchar2(30), cpu_cost numeric, io_cost numeric, temp_space numeric, access_predicates varchar2(4000), filter_predicates varchar2(4000)); ------------------------------------------------------------------------------------ truncate table plan_table; ------------------------------------------------------------------------------------ -- a simple query and its execution plan -- explain plan for select * from bonus; ------------------------------------------------------------------------------------ -- utlxpls.sql select * from table(dbms_xplan.display); -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | TABLE ACCESS FULL | BONUS | | | | -------------------------------------------------------------------- ------------------------------------------------------------------------------------ -- execution plan without any index -- truncate table plan_table; explain plan for select ename from emp where ename = 'KING'; select * from table(dbms_xplan.display); -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | |* 1 | TABLE ACCESS FULL | EMP | | | | -------------------------------------------------------------------- ------------------------------------------------------------------------------------ -- ... and with an index on the predicate -- create index emp_ename_idx on emp (ename); truncate table plan_table; explain plan for select ename from emp where ename = 'KING'; select * from table(dbms_xplan.display); ----------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | |* 1 | INDEX RANGE SCAN | EMP_ENAME_IDX | | | | ----------------------------------------------------------------------- ------------------------------------------------------------------------------------ -- ... predicate has index, but only the precicate value can be fetched -- from the index. A navigation to the table by rowid (retrieved from -- the index) is still neccessary for the remaining columns -- truncate table plan_table; explain plan for select ename,job from emp where ename = 'KING'; select * from table(dbms_xplan.display); ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | | | | |* 2 | INDEX RANGE SCAN | EMP_ENAME_IDX | | | | ------------------------------------------------------------------------------ ------------------------------------------------------------------------------------ -- a like-query starting with a wildcard will NOT use the index! -- truncate table plan_table; explain plan for select ename from emp where ename like '%ING'; select * from table(dbms_xplan.display); -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | |* 1 | TABLE ACCESS FULL | EMP | | | | -------------------------------------------------------------------- ------------------------------------------------------------------------------------ drop index emp_ename_idx; ------------------------------------------------------------------------------------ -- a little more complicated query and its execution plan -- explain plan for select o.orderdate, i.actualprice from ord o, item i where o.ordid=i.ordid; select * from table(dbms_xplan.display); ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | | | 1 | NESTED LOOPS | | | | | | 2 | TABLE ACCESS FULL | ITEM | | | | | 3 | TABLE ACCESS BY INDEX ROWID| ORD | | | | |* 4 | INDEX UNIQUE SCAN | ORD_ORDID_PK | | | | ------------------------------------------------------------------------------ ====================================================================================