Explain the Oracle execution plan

1. What is an execution plan?

An explain plan is a representation of the access path that is taken when a query is executed within Oracle.

Ii. How to access data

At the physical level Oracle reads blocks of data. the smallest amount of data read is a single Oracle block, the largest is constrained by operating system limits (and multiblock I/o ). logically Oracle finds the data to read by using the following methods:
Full Table Scan (FTS)  -- Full table Scan
Index Lookup (unique & non-unique)  -- Index scan (unique and non-unique)
Rowid  -- Physical row id

Iii. Execution Plan Hierarchy

When looking at a plan, the rightmost (ie most inndented) uppermost operation is the first thing that is executed. -- UseThe rightmost most first executionIn terms of hierarchies, if an action has no sub-ID at the same level, it is first executed.

1. Let's look at a simple example.:

Query Plan
-----------------------------------------
Select statement [CHOOSE] cost= 1234
** Table access full large [: Q65001] [ANALYZED] -- [: Q65001] indicates parallel mode, and [ANALYZED] indicates that the object has been ANALYZED.

When the optimization mode is CHOOSE, check whether the Cost parameter has a value to determine whether to use CBO or RBO:
Select statement [CHOOSE] Cost = 1234 -- Cost has a value and uses CBO
Select statement [CHOOSE] Cost = -- the Cost is null and RBO is used.

2. hierarchical parent-child relationship. For more complex examples:

PARENT1
** FIRST CHILD
* *** FIRST GRANDCHILD
** SECOND CHILD

Here the same principles apply, the first grandchild is the initial operation then the first child followed by the second child and finally the PARENT collates the output.

Iv. Example explanation

Execution Plan
----------------------------------------------------------
0 ** select statement Optimizer = CHOOSE (Cost = 3 Card = 8 Bytes = 248)
1 0 ** hash join (Cost = 3 Card = 8 Bytes = 248)
2 1 * table access (FULL) OF 'dept' (Cost = 1 Card = 3 Bytes = 36)
3 1 * table access (FULL) OF 'emp' (Cost = 1 Card = 16 Bytes = 304)

There are two rows of data on the left, with the serial number ID in front and the corresponding PID (parent ID) in the back ).

A shortened summary of this is:
Execution starts with ID = 0: select statement but this is dependand on it's child objects
So it executes its first child step: ID = 1 PID = 0 hash join but this is dependand on it's child objects
So it executes its first child step: ID = 2 PID = 1 table access (FULL) OF 'dept'
Then the second child step: ID = 3 PID = 2 table access (FULL) OF 'emp'
Rows are returned to the parent step (s) until finished

V. Table access methods

1. Full Table Scan (FTS) Full Table Scan

In a FTS operation, the whole table is read up to the high water mark (HWM ). the HWM marks the last block in the table that has ever had data written to it. if you have deleted all the rows then you will still read up to the HWM. truncate resets the HWM back to the start of the table. FTS uses multiblock I/o to read the blocks from disk. -- In full table scan mode, data will be read to the high watermark line of the table (HWM indicates the last data block that has been extended in the Table). The reading speed depends on the Oracle initialization parameters.Db_block_multiblock_read_count

Query Plan
------------------------------------
Select statement [CHOOSE] Cost = 1
** Index unique scan EMP_I1 -- If the desired data is found in the index, the table will not be accessed again.

2. Index Lookup Index Scanning

There are 5 methods of index lookup:

Index unique scan  --Unique index Scan
Method for looking up a single key value via a unique index. always returns a single value, You must supply at least the leading column of the index to access data via the index.
Eg:
SQL> explain plan for select empno, ename from emp where empno = 10;

Index range scan  -- Index local Scan
Index range scan is a method for accessing a range values of a particle column. at least the leading column of the index must be supplied to access data via the index. can be used for range operations (e.g.> <> = <= ).
Eg:
SQL> explain plan for select mgr from emp where mgr = 5;

Index full scan  -- Global index Scan
Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan wocould be a good idea or not. we choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. for example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order.
Eg:
SQL> explain plan for select empno, ename from big_emp order by empno, ename;

Index fast full scan  -- Index quick global scan, which is usually generated without order
Scans all the block in the index, Rows are not returned in sorted order, Introduced in 7.3 and requires V733_PLANS_ENABLED = TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock I/o, can be executed in parallel, can be used to access second column of concatenated indexes. this is because we are selecting all of the index.
Eg:
SQL> explain plan for select empno, ename from big_emp;

Index skip scan  -- Index skip scan. where condition columns are frequently generated when they are non-indexed leading columns.
Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column (s) during the search.
Eg:
SQL> create index I _emp on emp (empno, ename );
SQL> select job from emp where ename = 'Smith ';

3. Rowid physical ID scan

This is the quickest access method available. Oracle retrieves the specified block and extracts the rows it is interested in. -- Rowid scan is the fastest way to access data

Vi. Table connection mode

There are three Connection Methods:

1. Sort Merge Join (SMJ) -- Because sort is resource-consuming, this connection method should be avoided.

Rows are produced by Row Source 1 and are then sorted Rows from Row Source 2 are then produced and sorted by the same sort key as Row Source 1. row Source 1 and 2 are NOT accessed concurrently.

SQL> explain plan
Select e. deptno, d. deptno
From emp e, dept d
Where e. deptno = d. deptno
Order by e. deptno, d. deptno;

Query Plan
-------------------------------------
Select statement [CHOOSE] Cost = 17
** MERGE JOIN
* *** SORT JOIN
* ***** Table access full emp [ANALYZED]
* *** SORT JOIN
* ***** Table access full dept [ANALYZED]

 

Sorting is an expensive operation, especially with large tables. Because of this, SMJ is often not a particle ly efficient join method.

2. Nested Loops (NL) -- A more efficient connection method

Fetches the first batch of rows from row source 1, Then we probe row source 2 once for each row returned from row source 1.
For nested loops to be efficient it is important that the first row source returns as few rows as possible as this directly controls the number of probes of the second row source. also it helps if the access method for row source 2 is efficient as this operation is being repeated once for every row returned by row source 1.

SQL> explain plan
Select a. dname, B. SQL
From dept a, emp B
Where a. deptno = B. deptno;

 

Query Plan
-------------------------
Select statement [CHOOSE] Cost = 5
** NESTED LOOPS
* *** Table access full dept [ANALYZED]
* *** Table access full emp [ANALYZED]

3. Hash Join -- The most efficient connection method

New join type introduced in 7.3, More efficient in theory than NL & SMJ, Only accessible via the CBO. smallest row source is chosen and used to build a hash table and a bitmap The second row source is hashed and checked against the hash table looking for joins. the bitmap is used as a quick lookup to check if rows are in the hash table and are especially useful when the hash table is too large to fit in memory.

 

SQL> explain plan
Select empno
From emp, dept
Where emp. deptno = dept. deptno;

 

Query Plan
----------------------------
Select statement [CHOOSE] Cost = 3
** HASH JOIN
* *** TABLE ACCESS FULL DEPT
* *** TABLE ACCESS FULL EMP

 

Hash joins are enabled by the parameter HASH_JOIN_ENABLED = TRUE in the init. ora or session. TRUE is the default in 7.3.

3.Cartesian Product -- The Kadir product is not a real connection method. SQL must be written incorrectly.

A Cartesian Product is done where they are no join conditions between 2 row sources and there is no alternative method of accessing the data. Not really a join as such as there is no join! Typically this is caused by a coding mistake where a join has been left out.
It can be useful in some circumstances-Star joins uses cartesian products. Notice that there is no join between the 2 tables:

 

SQL> explain plan
Select emp. deptno, dept, deptno
From emp, dept

 

Query Plan
------------------------------
Slect statement [CHOOSE] Cost = 5
** MERGE JOIN CARTESIAN
* *** TABLE ACCESS FULL DEPT
* *** SORT JOIN
* ***** TABLE ACCESS FULL EMP

 

The CARTESIAN keyword indicate that we are doing a cartesian product.

VII. Operators

1. sort -- Sorting, resource consumption

There are a number of different operations that promote sorts:
Order by clses
Group
Sort merge join

2. filter -- Filtering, such as not in and min functions, is easy to generate.

Has a number of different meanings, used to indicate partition elimination, may also indicate an actual filter step where one row source is filtering, another, functions such as min may introduce filter steps into query plans.

3. view -- Views are mostly produced by inline views.

When a view cannot be merged into the main query you will often see a projection view operation. this indicates that the 'view' will be selected from directly as opposed to being broken down into joins on the base tables. A number of constructs make a view non mergeable. inline views are also non mergeable.
Eg:
SQL> explain plan
Select ename, tot
From emp, (select empno, sum (empno) tot from big_emp group by empno) tmp
Where emp. empno = tmp. empno;

 

Query Plan
------------------------
Select statement [CHOOSE]
** HASH JOIN
** Table access full emp [ANALYZED]
** VIEW
* *** SORT GROUP
* ***** Index full scan BE_IX

4. partition view  -- Partition View

 

Partition views are a legacy technology that were superceded by the partitioning option. This section of the article is provided as reference for such legacy systems.

(From: http://hi.baidu.com/edeed/blog/item/73c46538d2614d2796ddd864.html)