How to read Oracle execution plan

How to read ORACLE Execution Plan

First, what is the implementation plan

An explain plan is a representation of the "access path" is 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 of data read is a single Oracle block, the largest are constrained by operating system limits (and Mult Iblock I/O). Logically Oracle finds the data to read by using the following methods:
Full table Scan (FTS)--All Tables scan
Index Lookup (Unique & Non-unique)--indexed scan (unique and not unique)
Rowid--Physical row ID

Third, the execution plan level relations

--look at the hierarchy with the most right and first execution principle, at the same level, if an action has no child ID, it is first executed

1. A simple example:

Sql> Select/*+parallel (E 4) * * from EMP E;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT optimizer=choose (cost=1 card=82 bytes=7134)

1 0 TABLE access* (full) ' EMP ' (cost=1 card=82 bytes=7134): Q5000

--[:Q5000] represents a parallel approach

1 parallel_to_serial SELECT/*+ no_expand ROWID (A1) */A1. " EMPNO "

, A1. " Ename ", A1." JOB ", A1." MGR ", A1." Hi

The optimal mode is choose, see if the cost parameter has a value to decide whether to use CBO or RBO:
SELECT STATEMENT [CHOOSE] cost=1234 --cost has a value, using the CBO
SELECT STATEMENT [CHOOSE] --cost is empty, with Rbo (9I is displayed)

2. Examples of hierarchical parent-child relationships:
PARENT1
**first Child
The grandchild
**second Child

Here the same principles apply, the the ' the ' the ' the ' initial operation then The child and finally the PARENT collates the output.

Iv. illustration of the example

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) ' DEPT ' (cost=1 card=3 bytes=36)

3 1 ****table ACCESS (full) ' EMP ' (cost=1 card=16 bytes=304)

The left side of the two rows of data, preceded by the serial number ID, followed by the corresponding PID (parent ID).

A shortened summary of:

Execution starts with Id=0:select STATEMENT but this is dependand on it ' s child objects

So it executes its the step:id=1 pid=0 HASH JOIN But this are dependand on it's child objects

So it executes its step:id=2 pid=1 TABLE ACCESS (full) ' DEPT '

Then the second child step:id=3 pid=2 TABLE ACCESS (full) ' EMP '

Rows are returned to the parent step (s) until finished

Five, table access mode

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 blocks in the table that has ever had data written to it. If you are have deleted all the rows of then you'll 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. --Full table scan mode reads the high watermark of the data to the table (HWM is the last block of data that the table has ever extended), and the read speed depends on the Oracle initialization parameters Db_block_multiblock_read_count (I think this translates: The FTS scan will make the table use up to the high water level (HWM), HWM identifies the block in which the table was last written, and if you delete all of the data tables with delete (HWM), only truncate can return the table, FTS use multiple IO to read the data block from the disk.

Query Plan

------------------------------------

SELECT STATEMENT [CHOOSE] Cost=1

**index UNIQUE SCAN EMP_I1--If you find the data you want in the index, you won't be able to access the table again.

2.Index Lookup Index Scan

There are 5 methods of index lookup:

Index Unique Scan-- Index Unique Scan

method to looking up a single key value via a unique index. Always returns a single value, your 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 to accessing a range values of a particular column. At least the leading column of the index must is supplied to access data via the index. Can is used for range operations (e.g. > < <> >= <= between).

Eg:sql> explain to select Mgr from emp where mgr = 5;

index Full Scan-- Index Global Scan

Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good Idea or not. We choose a index full Scan when we have statistics that indicate this it is going to being more efficient than a full table Scan and a sort. For example we/may does a full index scan as we do a unbounded scan the ' an index and want the ' data to is ordered in Dex order.

Eg:sql> explain plan for select Empno,ename to Big_emp order by Empno,ename;

index Fast Full scan-- index fast global scan without ORDER BY circumstances often occur

Scans all the blocks in the index, Rows are not returned in sorted order, introduced in 7.3 and requires =true and CBO, May is hinted using index_ffs hint, uses multiblock I/O, can is executed in parallel, can is used to access Second column of concatenated indexes. This is because we are selecting all of the index.

Eg:sql> explain to select Empno,ename from Big_emp;

index Skip Scan-- index jump Scan, where A conditional column occurs frequently when it is a leading column that is not indexed

Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the "the" the "during" 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 the "specified block and extracts" it is interested in.--rowid scan is the fastest way to access data

Vi. Table Connection method

Seven, operator

1.sort --sort, very consuming resources

There are a number of different operations that promote sorts:

(1) Order BY clauses (2) Group by (3) Sort Merge join–-These three will produce a sort operation

2.filter -filter, such as not in, Min function and so easy to produce

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

3.view -view, mostly generated by inline view (may go deep into the graph table)

When a view cannot is merged into the main query you'll often a projection view operation. This is indicates that the ' view ' is selected from directly as opposed to being broken down into joins on the base table S. A number of constructs make A view non mergeable. Inline views are also non mergeable.

Eg:sql>