|
Explain Execution Plan.- SQL Server caches the plan of execution of query or stored procedure which it uses in subsequent call.
- This is a very important feature with regards to performance enhancement.
- You can view execution plan of data retrieval graphically or textually.
- Execution plans, describes the SQL command EXPLAIN PLAN, and explains how to interpret its output.
- 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:
1. An ordering of the tables referenced by the statement.
2. An access method for each table mentioned in the statement.
3. A join method for tables affected by join operations in the statement.
4. Data operations like filter, sort, or aggregation
In addition to the row source tree, the plan table contains information about the following:
1. Optimization, such as the cost and cardinality of each operation.
2. Partitioning, such as the set of accessed partitions.
3. 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 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.
|
