How to read Oracle's execution plan

How to read Oracle's execution plan

First, what is the implementation plan

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

Second, how to access data

At the physical level Oracle reads blocks of data. The smallest amount Ofdata read is a single Oracle block, the largest are constrained by OperatingSystem limits (and Multib Lock I/O). Logically Oracle finds the data to read byusing the following methods:
Full table Scan (FTS)-Fully scanned
Index Lookup (Unique & non-unique)-indexed scan (unique and non-unique)
Rowid--Physical row ID

III. Implementation Plan Hierarchy relationship

When looking in a plan, the rightmost (ie most inndented) uppermostoperation are the first thing that is executed.--adopt the most The first right-most-executed principle looks at the hierarchy, at the same level if an action does not have a child ID, it is executed first.

1. Look at a simple example :

Query Plan
-----------------------------------------
SELECT STATEMENT [CHOOSE] cost=1234
**table ACCESS Full LARGE [: Q65001] [analyzed]--[:q65001] means parallel, [ANALYZED] means that the object has been parsed

When the optimization mode is choose, see if the cost parameter has a value to decide whether to use the CBO or RBO:
SELECT STATEMENT [CHOOSE] cost=1234--cost value, using CBO
SELECT STATEMENT [CHOOSE] cost=--cost is empty, using Rbo

2. Hierarchical parent-child relationships, see more complex examples:

PARENT1
**first Child
First grandchild
**second Child

Here the same principles apply, the first grandchild are the initialoperation then the first child followed by the SECOND C Hild and finally theparent collates the output.

Iv. Examples and explanations

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)

Two rows of data on the left, preceded by a serial number ID, followed by the corresponding PID (parent ID).

A shortened summary of this is:
Execution starts with Id=0:select STATEMENT It's dependand on it ' Schild objects
So it executes her first child step:id=1 Pid=0 HASH JOIN It's Dependandon it ' s child objects
So it executes their 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 is returned to the parent step (s) until finished

V. Table Access method

1.Full Table Scan (FTS) Full table Scan

In a FTS operation, the whole table was read up to the high water mark (HWM). The HWM marks, the last block in the table, has ever had data writtento it. If you had deleted all, the rows then you'll still read up to the HWM. Truncate resets the HWM back to the start of the table.   FTS uses Multiblock I/oto read the blocks from disk. --Full table scan mode reads the data to the table's high watermark (HWM is the last block of data that the table has ever expanded), and the read 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, no more access to the table

2.Index Lookup Index Scan

There is 5 methods of index lookup:

index unique scan   --index unique scan
Method for looking up a single key value via a Unique index. Always returns asingle value, your must supply at LEAST the leading column of the index toaccess 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 particularcolumn. At LEAST the leading column of the index must is supplied to AccessData via the index. Can is used for range operations (e.g. > < <> >= <= between).
Eg:
sql> explain plan for select Mgr from emp where mgr = 5;

index full scan   --index global scan
Full index scans is only available in the CBO as Otherwise we is unable todetermine whether a full scan would is a good idea or not. We choose an indexfull Scan when we had statistics that indicate that it was going to being moreefficient than a full table s Can and a sort. For example we indexscan if we do a unbounded scan of an index and wantthe data to being ordered in the Inde X order.
Eg:
sql> explain plan for select Empno,ename from Big_emp order by Empno,ename;

Index Fast full scan--indexing quick global scan, often occurs without order by
Scans all the block in the index, Rows is not returned in sorted order,introduced in 7.3 and requires v733_plans_enabled= TRUE and CBO, may be hintedusing index_ffs hint, uses multiblock I/O, can be executed in parallel, can be usedto access SE Cond column of concatenated indexes. This is because we areselecting all of the index.
eg
Sql> explain plan for select Empno,ename from Big_emp;

Index Skip Scan---------------indexed skip
Index skip scan finds rows evenif The column is not the leading column of aconcatenated index. It skips the first column (s) during the search.
eg
Sql> CREATE INDEX i_emp on EMP (empno, ename);
Sql> Select/*+ index_ss (EMP i_emp) */job from EMP where ename= ' SMITH ';

3.Rowid Physical ID Scan

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

Six, table connection method

There are three ways to connect:

1.Sort Merge Join (SMJ) -because sort is very resource-intensive, this connection should be avoided

Rows is produced by row Source 1 and is then sorted Rows from Row Source2 is then produced and sorted by the same sort Key as Row Source 1. Row Source1 and 2 is not accessed concurrently.

Sql> explain plan for
Select/*+ ordered */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 a expensive operation, especially with Largetables. Because of this, smj are often not a particularly efficient join method.

2.Nested Loops (NL) --a more efficient way to connect

Fetches the first batch of rows from row Source 1, then we probe RowSource 2 once for each row returned from row Source 1.
For nested loops to be efficient it's important that first row sourcereturns as few rows as possible as this directly Controls the number of Probesof the second row source. Also it helps if the access method for row source 2is efficient as this operation are being repeated once for every row ret urned Byrow Source 1.

Sql> explain plan for
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 way to connect

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 tobuild a hash table and a bitmap the second row source is hashed and checkedagains T the hash table looking for joins. The bitmap is used as a quick lookupto check if rows was in the hash table and was especially useful when the Hashtable was Too large to fit in memory.

Sql> explain plan for
Select/*+ Use_hash (EMP) */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 is enabled by the parameter Hash_join_enabled=true in Theinit.ora or session. TRUE is the default in 7.3.

3.Cartesian Product -Cartesian product, not a true connection, SQL must write a problem

A Cartesian Product is do where they is no join conditions between 2row sources and there is no alternative method of a Ccessing the data. Notreally a join as such as there is no join! Typically this was caused by a codingmistake where a join had been left out.
It can be useful in some Circumstances-star joins uses Cartesian products. Noticethat there is no join between the 2 tables:

Sql> explain plan for
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 Acartesian product.

Seven, operator

1.sort --Sort, very resource-intensive

There is a number of different operations that promote sorts:
ORDER BY clauses
GROUP BY
Sort Merge Join

2.filter --filtering, such as not in, min function, etc. easy to produce

Have a number of different meanings, used to indicate partitionelimination, could also indicate an actual filter step where O Ne Row Source isfiltering, another, functions such as min may introduce filter steps into Queryplans.

3.view -views, mostly generated by inline views

When a view cannot is merged into the main, query you'll often see aprojection view operation. This indicates, the ' view ' would be selected fromdirectly as opposed to being broken-to-joins on the base tables . Anumber of constructs make a view non mergeable. Inline views is also nonmergeable.
eg
Sql> explain plan for
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 by
INDEX Full SCAN Be_ix

4.partition View --Partitioned view

Partition views is a legacy technology that were superceded by thepartitioning option. This section of the article is provided as reference Forsuch legacy systems.

How to read Oracle's execution plan