UnderstandOracle execution plan

1. What is the execution plan Anexplainplanisarepresentationoftheaccesspaththatistakenwhenaqueryisexecutedwithin

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

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

Iii. Execution Plan Hierarchy

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 PARALLEL_TO_SERIAL SELECT/* + NO_EXPAND ROWID (A1) */A1. "EMPNO"

, A1. "ENAME", A1. "JOB", A1. "MGR", A1. "HI

2.Example of hierarchical parent-child relationship:
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)

Two rows of data on the left, with the serial number in frontID, Followed by the correspondingPID(ParentID).

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

Indicates the last data block that the table has extended. The read speed depends onOracleInitialization parametersDb_block_multiblock_read_count (I think the translation should be like this: FTSScanning will increase the table usage to a high level(HWM), HWMThe block that identifies the last data written to the table,If you useDELETEAll Deleted Data Tables are still in high water level.(HWM ),Only useTRUNCATETo make the table Regression, FTSManyIORead data blocks from Disks).

Query Plan

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

Select statement [CHOOSE] Cost = 1

2. Index LookupIndex 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 --Partial index 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 --Fast global index scanningOrderUsually

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,WhereCondition columns are common 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/* + index_ss (emp I _emp) */job from emp where ename = 'Smith ';

3. RowidPhysicalIDScan

Vi. Table connection mode

VII. Operators

There are a number of different operations that promote sorts:

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.

May go deep into the view base table)

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.

Example:Assume thatA,B,CAre they small tables?AA composite index on the table:A (a. col1, a. col2), Note:A. col1Column is the index guide column. Consider the following query:

From

Execution Plan

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

See another reprinted article)

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

If there is no execution plan, analyze the above3Which of the following tables should be used as the first driver table? SlaveSQLStatement, onlyBTable andCThe table has restrictions, so the first driver table should be2Which one is in the table?

In the above QueryCThe table also has predicates.(C. col3 = 5), Some may thinkCAs the first driving table, the table can also achieve better performance. Let's analyze it again: ifCAs the first driver table, the driver table can be generated very small.Row sourceBut check the connection conditions.A. col2 = C. col2At this time, there is no chance to useATable index, becauseATableCol2Column is notLeading columnNested loopThe query efficiency is poor. SoNLIt is important to select the correct driver table for the connection.

Therefore, the above query has a better connection order:(B-> A)-> C. If the database is a cost-based optimizer, it will use the calculated cost to determine the proper drive table and the proper connection sequence. Generally,CBOSelect the correct connection order. IfCBOWe can also useORACLEProvidedHintsTo makeCBOUse the correct connection sequence. As shown below

Select/* + ordered */A. col4

AndAnd

Since it is so important to select the correct driver table, let's take a look at the execution plan and how the tables are associated to obtain which table in the execution plan should be the driver table:

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

NESTED LOOPS

Table access (FULL) OF 'B'

Table access (by index rowid) OF 'A'

INDEX (range scan) OF 'partition _ col12a' (NON-UNIQUE)

Table access (FULL) OF 'C'

View the execution plan3Column, that is, the letter section. The left side of each column value contains spaces as indentions. The more spaces on the left of the column value, the more indent the column value and the more right the column value is. As shown in the preceding execution plan, the first column value is6The line has the most indentation, that is, the row is the rightmost; the value of the first column is4,5The indentation of the row is the same, and the degree to right is the same, but the value of the first column is4Is5When talking about the upper and lower relations, it is only valid for consecutive rows with consistent indentation.

Table andACreate a nested loop for the table, and then generateRow sourceAndCSort tables-merge connections.

　 When we look at the execution plan, the key is not to see which operation is executed first, which operation is executed later, but to view the order of the connections between tables.(If you know which driver table is used, you need to determine the operation order.), What type of association is used and the specific access path(For example, you can determine whether an index is used.)

After determining which table is the driving table from the execution plan, we can determine the table as the driving table based on our knowledge.(Just like the above judgmentABCTable)Is it appropriate? If not, rightSQLStatement, so that the optimizer can select the correct driver table.