oracle explain plans explained

8/8/2019 Oracle Explain Plans EXPLAINED

1/35

Explained ..I Hope ...


2/35

What is an explain plan?

How does Oracle access data

Access Methods in detail

Operations

Bind Variables

Parallel Query

How to obtain explain plans

Explain plan Hierarchy

Cost

Examples ..


3/35

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

Query processing can be divided into 7 phases:

1. Syntactic Checks the syntax of the query .2. Semantic Checks that all objects exist and are accessible .

3. View Merging Rewrites query as join on base tables as opposed to using views .4. Statement Transformation Rewrites query transforming some complex constructs

into simpler ones.

5. Optimization Determines the optimal access path for the query to take. ( Cost ofthe query )

6. QEP Generation QEP = Query Evaluation Plan7. Execution of the statement.

The explain plan is produced by the parser. Once the access path has been decided upon it isstored in the library cache together with the statement itself. Queries in the library cache basedupon a hashed representation of the query. When looking for a statement in the library cache, theparser first applies a hashing algorithm to the statement and then scans for this hash value in thelibrary cache. This access path will be used until the query is reparsed.


4/35

At the physical level Oracle reads blocks of data. Logically Oracle finds thedata to read by using the following methods:

Full Table Scan (FTS)

Index Lookup (unique & non-unique)

Index Unique Scan Index Range Scan

Index Full Scan Index Fast Full Scan

Rowid


5/35


6/35

Joins A Join is a predicate that attempts to combine 2 row sources. The parser only ever join 2 row sources

together. Join steps are always performed serially even though underlying row sources may havebeen accessed in parallel.

select A.col4from A,B,C

where B.col3 = 10and A.col1 = B.col1and A.col2 = C.col2and C.col3 = 5;1 3 2

( col3=10 ) B A C ( col3=5 )

Join Types

Sort Merge Join (SMJ)

Rows are produced by Row Source 1 and are then sorted Rows from Row Source 2 are thenproduced and sorted by the same sort key as Row Source 1. Row Source 1 and 2 are NOTaccessed concurrently. Sorted rows from both sides are then merged together (joined)

MERGE/ \

SORT SORT| |

Row Source 1 Row Source 2


7/35

Nested Loops (NL)

First we return all the rows from row source 1 Then we probe row source 2 once for each

row returned from row source 1. Accessing row source 2 is known a probing the innertable. For nested loops to be efficient it is important that the first row source returns asfew rows as possible as this directly controls the number of probes of the second rowsource.

Row source 1Row 1 -------------- -- Probe -> Row source 2Row 2 -------------- -- Probe -> Row source 2Row 3 -------------- -- Probe -> Row source 2

Row source 1 is known as the outer tableRow source 2 is known as the inner table

Hash Join

Smallest row source is chosen and used to build a hash table and a bitmap. The secondrow source is hashed and checked against the hash table looking for joins. The bitmap isused as a quick lookup to check if rows are in the hash table and are especially usefulwhen the hash table is too large to fit in memory.

Cartesian Product

A Cartesian Product is done where they are no join conditions between 2 row sources andthere is no alternative method of accessing the data Not really a join as such as there is nojoin! Typically this is caused by a coding mistake where a join has been left out. It can beuseful in some circumstances .. yea right


8/35

Operations that show up in explain plans Sorts

There are a number of different operations that promote sorts.

order by clauses group by

sort merge joinSorts are expensive operations especially on large tables where the rows do not fit in memoryand spill to disk.

Filter 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 asmin may introduce filter steps into query plans

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 tobeing broken down into joins on the base tables. A number of constructs make a viewnon mergeable. Inline views are also non mergeable.

Partition Views Allows a large table to be broken up into a number of smaller partitions which can be

queried much quicker than the table as a whole. A union all view is built over the top toprovide the original functionality. Check constraints or where clauses provide partitionelimination capabilities


9/35

Bind variables are recommended in most cases because they promote sharing of sql code.

At parse time the parser has NO IDEA what the bind variable contains. With RBO this makes nodifference but with CBO, which relies on accurate statistics to produce plans, this can be aproblem.

variable x varchar2(18);

assigning values:

begin

:x := 'hello';

end;/

explain plan for

select *

from dept

where rowid = ':x';

Query Plan------------------------------------

SELECT STATEMENT [CHOOSE] Cost=1

TABLE ACCESS BY ROWID DEPT [ANALYZED]


10/35

Main indicators that a query is using PQO:

[:Q1000004] entries in the explain plan

Checkout the other column for details of what the slaves are executing

v$pq_slave will show any parallel activity

Parallel Query operates on a producer/consumer basis.

When you specify parallel degree 4 oracle tries to allocate 4 producer slaves and 4 consumerslaves. The producers can feed any of the consumers. If there are only 2 slaves available then weuse these. If there is only 1 slave available then we go serial If there are none available then we useserial.

Consumer processes typically perform a sorting function. If there is no requirement for the datato be sorted then the consumer slaves are not produced and we end up with the number of slavesused matching the degree of parallelism as opposed to being 2x the degree.


11/35

PARALLEL_FROM_SERIAL

This means that source of the data is serial but it is passed to a parallel consumer

PARALLEL_TO_PARALLEL

Both the consumer and the producer are parallel

PARALLEL_COMBINED_WITH_PARENT

This operation has been combined with the parent operator. For example in a sort merge jointhe sort operations would be shown as PARALLEL_COMBINED_WITH_PARENT becausethe sort and the merge are handled as 1 operation.

PARALELL_TO_SERIAL

The source of the data is parallel but it is passed to a serial consumer. This typically willhappen at the top of the explain plan but could occur anywhere .


12/35

Example

select /*+ parallel(B,4) parallel(A,4) */A.dname, avg(B.sal), max(B.sal)

from dept A, emp B

where A.deptno = B.deptno

group by A.dname

order by max(B.sal), avg(B.sal) desc;

Execution Plan #2 (Parallel)

OBJECT_NAME OBJECT_NODE ORDER------------------------------- ----------- -------

SELECT STATEMENT Cost = ??

SORT ORDER BY :Q55004 **[7]**

SORT GROUP BY :Q55003 **[6]**

MERGE JOIN :Q55002 **[5]**

SORT JOIN :Q55002 **[4]**

TABLE ACCESS FULL emp :Q55001 **[2]**SORT JOIN :Q55002 **[3]**

TABLE ACCESS FULL dept :Q55000 **[1]**


13/35

Execution Plan #2 -- ORDER column**[1]** (:Q55000) "PARALLEL_FROM_SERIAL"

Serial execution of SELECT DEPTNO, DNAME FROM DEPT

**[2]** (:Q55001) "PARALLEL_TO_PARALLEL"

SELECT /*+ ROWID(A1)*/A1."DEPTNO" C0, A1."SAL" C1FROM "EMP" A1WHERE ROWID BETWEEN :1 AND :2

**[3]** (:Q55002) "PARALLEL_COMBINED_WITH_PARENT"**[4]** (:Q55002) "PARALLEL_COMBINED_WITH_PARENT"**[5]** (:Q55002) "PARALLEL_TO_PARALLEL"

SELECT /*+ ORDERED USE_MERGE(A2)*/A2.C1 C0, A1.C1 C1FROM :Q55001 A1,:Q55000 A2WHERE A1.C0=A2.C0

**[6]** (:Q55003) "PARALLEL_TO_PARALLEL"

SELECT MAX(A1.C1) C0, AVG(A1.C1) C1, A1.C0 C2FROM :Q55002 A1GROUP BY A1.C0

**[7]** (:Q55004) "PARALLEL_FROM_SERIAL"

SELECT A1.C0 C0, A1.C1 C1, A1.C2 C2FROM :Q55003 A1ORDER BY A1.CO, A1.C1 DESC


14/35

Explain plan for

Main advantage is that it does not actually run the query - just parses the sql. This meansthat it executes quickly. In the early stages of tuning explain plan gives you an idea of thepotential performance of your query without actually running it. You can then make ajudgement as to any modifications you may choose to make.

Autotrace ( Advanced users ) Autotrace can be configured to run the sql & gives a plan and statistics afterwards or just give

you an explain plan without executing the query. SQL*Plus

set autotrace on

set autotrace on explain

set autotrace traceonly explain

set autotrace traceonly statistics

Tkprof ( Advanced users ) Analyzes trace file

alter session set sql_trace true;

tkprof {file stem} {file stem} explain=userid/password sort=$options$


15/35

Simple explain plan:

Query Plan

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


TABLE ACCESS FULL LARGE [:Q65001] [ANALYZED]

The right most upper most operation of an explain plan is the first thing that the explain plan willexecute. In this case TABLE ACCESS FULL LARGE is the first operation. This statement means weare doing a full table scan of table LARGE. When this operation completes then the resultant rowsource is passed up to the next level of the query for processing. In this case it is the SELECTSTATEMENT which is the top of the query.

[CHOOSE] is an indication of the optimizer_goal for the query. This DOES NOT necessarily

indicate that plan has actually used this goal. The only way to confirm this is to check the cost=part of the explain plan as well. For example the following query indicates that the CBO has beenused because there is a cost in the cost field:


16/35


However the explain plan below indicates the use of the RBO because the cost field is blank:

SELECT STATEMENT [CHOOSE] Cost=

The cost field is a comparative cost that is used internally to determine the best cost for particular

plans. The costs of the statements are not really directly comparable.

[:Q65001] indicates that this particular part of the query is being executed in parallel. Thisnumber indicates that the operation will be processed by a parallel query slave as opposed tobeing executed serially.

[ANALYZED] indicates that the object in question has been analyzed and there are currently

statistics available for the CBO to use. There is no indication of the 'level' of analysis done.


17/35

SELECT STATEMENT (first_rows)

HASH JOIN

TABLE ACCESS (analyzed) EP1 (full)

NESTED LOOPS

TABLE ACCESS (analyzed) EP3 (by index rowid)BITMAP CONVERSION (to rowids)

BITMAP OR

BITMAP INDEX EP3_BI1 (single value)

BITMAP INDEX EP3_BI2 (single value)TABLE ACCESS (analyzed) EP2 (by index rowid)

INDEX (analyzed) UNIQUE EP2_PK (unique scan)


18/35


HASH JOIN


NESTED LOOPS


BITMAP OR





19/35


HASH JOIN


NESTED LOOPS


BITMAP OR





20/35


HASH JOIN


NESTED LOOPS


BITMAP OR





21/35


HASH JOIN


NESTED LOOPS


BITMAP OR





22/35


23/35


HASH JOIN


NESTED LOOPS


BITMAP OR





24/35


HASH JOIN


NESTED LOOPS


BITMAP OR





25/35


HASH JOIN


NESTED LOOPS


BITMAP OR





26/35


HASH JOIN


NESTED LOOPS


BITMAP OR





27/35


HASH JOIN


NESTED LOOPS


BITMAP OR





28/35

COST

( cpu_cost , io_cost , network_cost , etc, etc .)

the optimizer is given a query. This query could be solved in one of possibly hundreds ofways. The optimizer generates many of these plans and assigns to each step of the plan aCOST based on the environment (hints will affect the cost, init.ora parameters will affect thecost, statistics will affect the costs, etc etc etc). For each of these plans -- an aggregate "cost"is computed. The plan with the lowest cost wins.

The cost is used to find the "winning" query plan for a query. The cost has no bearing as towhether or not it is the BEST plan or whether it will take a long time to run or a short time torun. The cost is just a number computed by the software according to an algorithm and isinfluenced heavily by the environment.

The cost is just a number. It does not mean the plan is good, bad orindifferent. Its just part of an algorithm. ( Tom Kyte Oracle , VP Core Technologies )


29/35

Execution plans are important

How much effort does it really take to look atEXPLAIN PLANs ......

Break complex plans down


30/35


31/35


32/35


33/35


34/35


35/35

THANK - YOU

oracle explain plans explained

Documents