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, 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 of data read is a single Oracle block, the largest was 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)-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) uppermost operation are the first thing that is executed. --Use the most right-most first-run principle to see the hierarchy, at the same level if an action does not have a child ID, it is executed first

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) of ' 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

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 is empty, using Rbo (9I is so shown)

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

Here the same principles apply, the first grandchild are the initial operation then the first child followed by the SECOND Child and finally the PARENT 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's child objects

So it executes her first child step:id=1 Pid=0 HASH JOIN It's dependand on 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 written to 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/O to 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 parameter Db_block_multiblock_read_count (I think this should be translated: The FTS scan causes the table to rise to high water level (HWM), HWM identifies the block at which the table was last written, and if you delete all data tables with delete, still at high water level (HWM), only use Truncate to make the table regression, and FTS uses multi-io to read data blocks from disk.

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-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 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 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 to determine whether a full scan would be a good Idea or not. We choose an index full Scan if we have statistics this indicate that it was going to being more efficient than a full table Scan and a sort. For example we could do a full index scan if we do an unbounded scan of an index and want the data to is ordered in the Dex order.

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

index Fast Full scan-- index Fast Global scan, without Order by situations often occur

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 hinted using index_ffs hint, uses multiblock I/O, can be executed in parallel, can is used to access Second column of concatenated indexes. This is because we were selecting all of the index.

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

index Skip Scan-- index jump Scan, where conditional columns often occur in the case of 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/*+ 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 the rows it is interested in.--rowid scan is the fastest way to access data

Six, table connection method

Seven, operator

1.sort --Sort, very resource-intensive

There is 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 --filtering, such as not in, min function, etc. easy to produce

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

3.view -views, mostly generated by inline views (possibly deep into the view EOG table)

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

Eg:sql> explain plan for

Select Ename,tot from EMP, (select Empno,sum (empno) tot to 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 views

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

Example: assume that A, B, and C are not small tables and a combined index on a table: A (a.col1,a.col2), note that the A.col1 column is indexed. Consider the following query:

Select A.col4 from A, B, C

where b.col3 = ten and a.col1 = b.col1 and a.col2 = c.col2 and c.col3 = 5;

Execution Plan

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

0 SELECT STATEMENT Optimizer=choose

1 0 MERGE JOIN

2 1 SORT (JOIN)

3 2 NESTED LOOPS

4 3 TABLE ACCESS (full) of ' B '

5 3 TABLE ACCESS (by INDEX ROWID) of ' A '

6 5 INDEX (RANGE SCAN) of ' inx_col12a ' (non-unique)

7 1 SORT (JOIN)

8 7 TABLE ACCESS (full) of ' C '

Statistics (statistical information parameters, see another reprinted article)

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

0 Recursive calls (call count)

8 db block gets (number of blocks read from disk, that is, the number of reads through Update/delete/select for update)

6 consistent gets (the number of blocks read from memory, that is, the number of select reads without the FOR update)

0 physical reads (physical read-read from disk to block number, generally ' consistent gets ' + ' db block gets ')

0 Redo Size (redo number-The amount of redo log generated during SQL execution)

551 Bytes sent via sql*net to client

430 Bytes received via sql*net from client

2 sql*net roundtrips To/from Client

2 Sorts (memory) (the sort that takes place in RAM)

0 Sorts (the sort that occurs on the hard disk)

6 Rows processed

When making a connection to a table, only 2 tables are connected first, and then the concatenated result as a row source is connected to the rest of the table, in the above example, the connection order is B and a first, and then the C connection:

B <---> A <---> C

col3=10 col3=5

If there is no execution plan, analyze, which of the above 3 tables should be taken as the first driver table? From the SQL statement, only the B table and the C table have restrictions, so the first driver table should be one of the 2 tables, which is it?

The B table has predicate b.col3 = 10, so that when a full table scan of Table B is done, the constraints (B.col3 = 10) in the WHERE clause are used to get a smaller row source, so B table should be the first driver table. In this case, if you are associated with table A again, you can effectively use the index of table A (because the col1 of table A is listed as leading column).

There is also a predicate (c.col3 = 5) on the C table in the above query, and one might think that the C table can also achieve better performance as the first driver table. Let us analyze again: If the C table is the first driver table, it will ensure that the driver table generates a very small row source, but look at the connection condition a.col2 = C.col2, there is no chance to take advantage of the index of table A, because the col2 column of table A is not leading columns, This nested loop is inefficient, resulting in poor query efficiency. So it is important to choose the correct driver table for the NL connection.

So the above query is a good connection order (B--> A)--> C. If the database is a cost-based optimizer, it uses the calculated cost to determine the appropriate driver table and the appropriate connection sequence. In general, the CBO chooses the correct connection order, and if the CBO chooses a poor connection sequence, we can use the hints provided by Oracle to get the CBO to use the correct connection sequence. As shown below

Select/*+ ordered */A.COL4

From B,a,c

where b.col3 = ten and a.col1 = b.col1 and a.col2 = c.col2 and c.col3 = 5

Since choosing the right driver table is so important, let's take a look at how the execution plan relates to each table, so that the table in the execution plan should be the driver table:

In the execution plan, you need to know which operation is executed first and which is executed after, which is useful for determining which table is the driver table. Before judging, if access to the table is through ROWID and the value of the ROWID is obtained from the index scan, the index scan is removed from the execution plan first. Then in the remainder of the execution plan, the guiding principle for judging the order of execution is that the right and the top actions are performed first. The specific explanations are as follows:

Get the execution plan after the index scan to remove the obstruction judgment: execution plan

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

0 SELECT STATEMENT Optimizer=choose

1 0 MERGE JOIN

2 1 SORT (JOIN)

3 2 NESTED LOOPS

4 3 TABLE ACCESS (full) of ' B '

5 3 TABLE ACCESS (by INDEX ROWID) of ' A '

6 5 INDEX (RANGE SCAN) of ' inx_col12a ' (non-unique)

7 1 SORT (JOIN)

8 7 TABLE ACCESS (full) of ' C '

Look at the 3rd column of the execution plan, the letter part, with a space at the left of each column value as the indent character. The more spaces to the left of the column value, the more indentation the column value is, and the more right the column value is. As shown in the above execution plan: the first column with a value of 6 indents the most, that is, the row is the most right, the first column with the value of 4, 5 is the same as the indentation of the row, but the first column with a value of 4 is higher than the first column with a value of 5 rows, and when talking about the upper and lower relationships, only valid for contiguous, indented

From this figure we can see that for the nested Loops section, the right and the top operation is table ACCESS (full) of ' B ', so this operation is performed first, so the corresponding B table is the first driver table (external table), and naturally, a table is an internal table. It can also be seen that a nested loop of B and A is a new row source, and after the row source is sorted, the sorted row source (with the C.COL3 = 5 restriction) corresponding to the C table is used for the SMJ connection operation. Therefore, from the above can be derived from the following fact: B table first with a nested loop, and then the resulting row source and C table do sort-merge connections.

By analyzing the execution plan above, we cannot say that the C table must be read after B and a, in fact, the B table may be read into memory at the same time as the C table, because the operation of reading the data in the table into memory may be parallel. In fact, many operations may be cross-over, because when Oracle reads the data, it is possible to read the entire chunk of data in memory if it is needed, and it may be read in chunks.

When we look at the execution plan, our key is not to see which operation executes first, which executes, but rather the order of connections between the tables (such as knowing which is the driver table, which needs to be judged from the order of operations), what type of association is used, and the specific access path (such as determining whether the index is used)

After judging from the execution plan which table is the driver table, according to our knowledge it is appropriate to judge the table as the driver table (as in the case of the ABC table above), if it is not appropriate, make changes to the SQL statement so that the optimizer can choose the correct driver table.

How to read Oracle execution plan