How to Execution and Explain Plan

Execution plan and explain plan

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement’s execution plan is the sequence of operations Oracle performs to run the statement.

The row source tree is the core of the execution plan. It shows the following information:

· An ordering of the tables referenced by the statement

· An access method for each table mentioned in the statement

· A join method for tables affected by join operations in the statement

· Data operations like filter, sort, or aggregation

In addition to the row source tree, the plan table contains information about the following:

· Optimization, such as the cost and cardinality of each operation

· Partitioning, such as the set of accessed partitions

· Parallel execution, such as the distribution method of join inputs

The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.

Before issuing an EXPLAIN PLAN statement, you must have a table to hold its output. PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans. Use the SQL script UTLXPLAN.SQL to create the PLAN_TABLE in your schema.

For example:

EXPLAIN PLAN FOR

SELECT last_name FROM employees;

With multiple statements, you can specify a statement identifier and use that to identify your specific execution plan. Before using SET STATEMENT ID, remove any existing rows for that statement ID.

EXPLAIN PLAN

SET STATEMENT_ID = ‘bad1′ FOR

SELECT last_name FROM employees;

Specifying Different Tables for EXPLAIN PLAN

You can specify the INTO clause to specify a different table.

EXPLAIN PLAN

INTO my_plan_table

FOR

SELECT last_name FROM employees;

SELECT * FROM RULESDBO.ENTITY WHERE ENTITY_ID = ‘1005’;

EXPLAIN PLAN FOR

SELECT * FROM RULESDBO.ENTITY WHERE ENTITY_ID = ‘1005’;

EXPLAIN PLAN

SET STATEMENT_ID = ‘SAMPLE’ FOR

SELECT * FROM RULESDBO.ENTITY WHERE ENTITY_ID = ‘1005’;

COMMIT;

SELECT * FROM PLAN_TABLE;

EXPLAIN PLAN command – This displays an execution plan for a SQL statement without actually

executing the statement.

V$SQL_PLAN – A dynamic performance view introduced in Oracle 9i that shows the execution plan

for a SQL statement that has been compiled into a cursor and stored in the cursor cache.

Under certain conditions the plan shown when using EXPLAIN PLAN can be different from the plan

shown using V$SQL_PLAN.

For example, when the SQL statement contains bind variables the plan

shown from using EXPLAIN PLAN ignores the bind variable values while the plan shown in

V$SQL_PLAN takes the bind variable values into account in the plan generation process.

Displaying an execution plan has been made easier since the introduction of the DBMS_XPLAN

package in Oracle 9i.

DBMS_XPLAN package – The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats. You can also use the DBMS_XPLAN package to display the plan of a statement stored in the Automatic Workload Repository (AWR) or stored in a SQL tuning set

The Oracle Optimizer is a cost-based optimizer. The execution plan selected for a SQL statement is

just one of the many alternative execution plans considered by the Optimizer. The Optimizer selects

the execution plan with the lowest cost, where cost represents the estimated resource usage for that

plan. The lower the cost the more efficient the plan is expected to be. The optimizer’s cost model

accounts for the IO, CPU, and network resources that will be used by the query.

Understanding the execution plan

Cardinality– Estimate of the number of rows coming out of each of the operations.

Access method – The way in which the data is being accessed, via either a table scan or index

access.

Join method – The method (e.g., hash, sort-merge, etc.) used to join tables with each other.

Join type – The type of join (e.g., outer, anti, semi, etc.).

Join order – The order in which the tables are joined to each other.

Partition pruning – Are only the necessary partitions being accessed to answer the query?

Parallel Execution – In case of parallel execution, is each operation in the plan being

conducted in parallel? Is the right data redistribution method being used?

The explain plan is by far the most useful tool at our disposal when it comes to investigating why the

Optimizer makes the decisions it makes. By breaking down the explain plan and reviewing the four key

elements of: cardinality estimations, access methods, join methods, and join orders; you can determine if the execution plan is the best available plan.

Displaying a Plan Table Using DBMS_XPLAN.DISPLAY

Execute an explain plan command on a SELECT statement:

EXPLAIN PLAN FOR

SELECT * FROM emp e, dept d

WHERE e.deptno = d.deptno

AND e.ename=’benoit';

Display the plan using the DBMS_XPLAN.DISPLAY table function

SET LINESIZE 130

SET PAGESIZE 0

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

This query produces the following output:

Plan hash value: 3693697075

—————————————————————————

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————

|   0 | SELECT STATEMENT   |      |     1 |    57 |     6  (34)| 00:00:01 |

|*  1 |  HASH JOIN         |      |     1 |    57 |     6  (34)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    37 |     3  (34)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3  (34)| 00:00:01 |

—————————————————————————

Predicate Information (identified by operation id):

—————————————————

1 – access(“E”.”DEPTNO”=”D”.”DEPTNO”)

2 – filter(“E”.”ENAME”=’benoit’)

15 rows selected.

The DBMS_XPLAN.DISPLAY function can accept 3 optional parameters:

· table_name – Name of the PLAN_TABLE, default value ‘PLAN_TABLE’.

· statement_id – Statement id of the plan to be displayed. The default value is NULL, which displays the most recent execution plan in the PLAN_TABLE.

· format – Controls the level of detail displayed, default value ‘TYPICAL’. Other values include ‘BASIC’, ‘ALL’, ‘SERIAL’. There is also an undocumented ‘ADVANCED’ setting.

Note. From Oracle 10g Release 2 onwards the format of the output can be tailored by using the standard list of formats along with keywords that represent columns to including or excluding (prefixed with ‘-‘). As a result, the format column can now be a space or comma delimited list. The list of available columns varies depending on the database version and function being called. Check the documentation for your version.