关于如何理解Explain Plan的输出

关于如何理解

Explain Plan

的输出

关于怎样解释

Explain

的输出曾经一直是一个困扰我的问题，

后来我在

Metalink

上找到这篇

文章，顿时豁然开朗。

希望有同样问题的同志能从这 篇文章有所收获，

曾经想翻译成中文，

但实在没有时间，

有心

的同志可以试试。

Interpreting Explain plan

1. Background information

1.1 What's an explain plan?

~~~~~~~~~~~~~~~~~~~~~~~

An explain plan is a representation of the access path that is taken when a query is executed within

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 where

appropriate (e.g. subquery unnesting, in/or

transformation)

[5] Optimization - determines the optimal access path for the

query to take. With the Rule Based

Optimizer (RBO) it uses a set of heuristics

to determine access path. With the Cost

Based Optimizer (CBO) we use statistics

to analyze the relative costs of accessing

objects.

[6] QEP Generation

[7] QEP Execution

(QEP = Query Evaluation Plan)

Steps [1]-[6] are handled by the parser.

Step [7] is the execution of the statement.

The

explain

plan

is

produced

by

the

parser.

Once

the

access

path

has

been

decided

upon

it

is

stored in the library cache together with the statement itself. We store queries in the library cache

based

upon

a

hashed

representation

of

that

query.

When

looking

for

a

statement

in

the

library

cache, we first apply a hashing algorithm to the statement and then we look for this hash value in

the library cache.

This access path will be used until the query is reparsed.

1.2 Terminology

~~~~~~~~~~~

Row Source - a set of rows used in a query

may be a select from a base object or the result set returned by

joining 2 earlier row sources

Predicate - where clause of a query

Tuples - rows

Driving Table - This is the row source that we use to seed the query.

If this returns a lot of rows then this can have a negative

affect on all subsequent operations

Probed Table - This is the object we lookup data in after we have retrieved

relevant key data from the driving table.

1.3 How does Oracle access data?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~

At

the

physical

level

Oracle

reads

blocks

of

data. The

smallest

amount

of

data

read

is

a

single

Oracle block, the largest is constrained by operating system limits (and multiblock i/o).

Logically Oracle finds the data to read by using the following methods:

Full Table Scan (FTS)

Index Lookup (unique & non-unique)

Rowid

1.4 Explain plan Hierarchy

~~~~~~~~~~~~~~~~~~~~~~

Simple explain plan:

Query Plan

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

SELECT STATEMENT [CHOOSE] Cost=1234

TABLE ACCESS FULL LARGE [:Q65001] [ANAL

YZED]

The rightmost uppermost operation of an explain plan is the first thing that the explain plan will

execute. In this case TABLE ACCESS FULL LARGE is the first operation. This statement means

we are doing a full table scan of table LARGE. When this operation completes then the resultant

row source is passed up to the next level of the query for processing. In this case it is the SELECT

STA

TEMENT 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 been

used because there is a cost in the cost field:

SELECT STATEMENT [CHOOSE] Cost=1234

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 different statements are not really directly comparable.

[:Q65001] indicates that this particular part of the query is being executed in parallel. This number

indicates

that

the

operation

will

be

processed

by

a

parallel

query

slave

as

opposed

to

being

executed serially.

[ANAL

YZED]

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.

2. Access Methods in detail

2.1 Full Table Scan (FTS)

~~~~~~~~~~~~~~~~~~~~~

In a FTS operation, the whole table is read up to the high water mark (HWM). The HWM marks

the last block in the table that has ever had data written to it. If you have deleted all the rows then

you will 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.

Multiblock

i/o

is

controlled

by

the

parameter

. This defaults to:

db_block_buffers / ( (PROCESSES+3) / 4 )

Maximum values are OS dependant

Buffers from FTS operations are placed on the Least Recently Used (LRU) end of the buffer cache

so

will

be

quickly

aged

out.

FTS

is

not

recommended

for

large

tables

unless

you

are

reading >5-10% of it (or so) or you intend to run in parallel.

Example FTS explain plan:

~~~~~~~~~~~~~~~~~~~~~~~~

SQL> explain plan for select * from dual;

Query Plan

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

SELECT STATEMENT [CHOOSE] Cost=

TABLE ACCESS FULL DUAL

2.2 Index lookup

~~~~~~~~~~~~

Data

is

accessed

by

looking

up

key

values

in

an

index and

returning

rowids.

A

rowid

uniquely

identifies an individual row in a particular data block. This block is read via single block i/o.

In

this

example

an

index

is

used

to

find

the

relevant

row(s)

and

then

the

table

is

accessed

to

lookup the ename column (which is not included in the index):

SQL> explain plan for

select empno,ename from emp where empno=10;

Query Plan

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

SELECT STATEMENT [CHOOSE] Cost=1

TABLE ACCESS BY ROWID EMP [ANAL

YZED]

INDEX UNIQUE SCAN EMP_I1

Notice the 'TABLE ACCESS BY ROWID' section. This indicates that the table data is not being

accessed

via

a

FTS

operation

but

rather

by

a

rowid

lookup.

In

this

case

the

rowid

has

been

produced by looking up values in the index first.

The index is being accessed by an 'INDEX UNIQUE SCAN' operation. This is explained below.

The index name in this case is EMP_I1.

If all the required data resides in the index then a table lookup may be unnecessary and all you

will see is an index access with no table access.

In the following example all the columns (empno) are in the index. Notice that no table access

takes place:

SQL> explain plan for

select empno from emp where empno=10;

Query Plan

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

SELECT STATEMENT [CHOOSE] Cost=1

INDEX UNIQUE SCAN EMP_I1

Indexes are presorted so sorting may be unnecessary if the sort order required is the same as the

index.

e.g.

SQL> explain plan for select empno,ename from emp

where empno > 7876 order by empno;

Query Plan

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

SELECT STATEMENT [CHOOSE] Cost=1

TABLE ACCESS BY ROWID EMP [ANAL

YZED]

INDEX RANGE SCAN EMP_I1 [ANAL

YZED]

In this case the index is sorted so ther rows will be returned in the order of the index hence a sort

is unecessary.

explain plan for

select /*+ Full(emp) */ empno,ename from emp

where empno> 7876 order by empno;

Query Plan

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

SELECT STATEMENT [CHOOSE] Cost=9

SORT ORDER BY

TABLE ACCESS FULL EMP [ANAL

YZED] Cost=1 Card=2 Bytes=66

Because we have forced a FTS the data is unsorted and so we must sort the data after it has been

retrieved.

There are 4 methods of index lookup:

index unique scan

index range scan

index full scan

index fast full scan

2.2.1 Index unique scan

~~~~~~~~~~~~~~~~~

Method for looking up a single key value via a unique returns a single value. You

must supply AT LEAST the leading column of the index to access data via the index, However

this may return > 1 row as the uniqueness will not be guaranteed.

example explain plan:

SQL> explain plan for

select empno,ename from emp where empno=10;

Query Plan

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

SELECT STATEMENT [CHOOSE] Cost=1

TABLE ACCESS BY ROWID EMP [ANAL

YZED]

INDEX UNIQUE SCAN EMP_I1

2.2.2 Index range scan

~~~~~~~~~~~~~~~~

Method for accessing multiple column values. You must supply AT LEAST the leading column of

the

index

to

access

data

via

the

index.

Can

be

used

for

range

operations

(e.g.

>

<

<>

>=

<=

between)

e.g.

SQL> explain plan for select empno,ename from emp

where empno > 7876 order by empno;

Query Plan

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

SELECT STATEMENT [CHOOSE] Cost=1

TABLE ACCESS BY ROWID EMP [ANAL

YZED]

INDEX RANGE SCAN EMP_I1 [ANAL

YZED]

A non-unique index may return multiple values for the predicate col1 = 5 and will use an index

range scan

SQL> explain plan for select mgr from emp where mgr = 5

Query plan

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

SELECT STATEMENT [CHOOSE] Cost=1

INDEX RANGE SCAN EMP_I2 [ANAL

YZED]

2.2.3 Index Full Scan

~~~~~~~~~~~~~~~

In certain circumstances it

is possible for the whole index to be scanned as opposed to a range

scan

(i.e.

where

no

constraining

predicates

are

provided

for

a

table).

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 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.

The

optimizer

may

decide

that

selecting

all

the

information from the index and not sorting is more efficient than doing a FTS or a Fast Full Index

Scan and then sorting.

An Index full scan will perform single block i/o's and so it may prove to be

inefficient.

e.g.

Index BE_IX is a concatenated index on big_emp (empno,ename)

SQL> explain plan for select empno,ename from big_emp order by empno,ename;

Query Plan

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

SELECT STATEMENT [CHOOSE] Cost=26

INDEX FULL SCAN BE_IX [ANAL

YZED]

2.2.4 Index Fast Full Scan

~~~~~~~~~~~~~~~~~~~~

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.

Note that INDEX FAST FULL SCAN is the mechinism behind fast index create and recreate.

e.g.

Index BE_IX is a concatenated index on big_emp (empno,ename)

SQL> explain plan for select empno,ename from big_emp;

Query Plan

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

SELECT STATEMENT [CHOOSE] Cost=1

INDEX FAST FULL SCAN BE_IX [ANAL

YZED]

Selecting the 2nd column of concatenated index:

SQL> explain plan for select ename from big_emp;

Query Plan

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

SELECT STATEMENT [CHOOSE] Cost=1

INDEX FAST FULL SCAN BE_IX [ANAL

YZED]

2.3 Rowid

~~~~~

This is the quickest access method available

Oracle

simply

retrieves

the

block

specified

and

extracts

the

rows

it

is

interested

in.

Most

frequently seen in explain plans as Table access by Rowid

Access by rowid :

SQL> explain plan for select * from dept where rowid = ':x';

Query Plan

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

SELECT STATEMENT [CHOOSE] Cost=1

TABLE ACCESS BY ROWID DEPT [ANAL

YZED]

Table is accessed by rowid following index lookup:

SQL> explain plan for

select empno,ename from emp where empno=10;

Query Plan

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

SELECT STATEMENT [CHOOSE] Cost=1

TABLE ACCESS BY ROWID EMP [ANAL

YZED]

INDEX UNIQUE SCAN EMP_I1

3. Joins

A Join is a predicate that attempts

to combine 2 row sources. We only ever join 2

row sources

together. Join steps are always performed serially even though underlying row sources may have

been accessed in parallel.

3.1 Join order - order in which joins are performed

~~~~~~~~~~

The

join

order

makes

a

significant

difference

to

the

way

in

which

the

query

is

executed.

By

accessing particular row sources first, certain predicates may be satisfied that are not satisfied by

with other join orders. This may prevent certain access paths from being taken.

e.g. Suppose there is a concatenated index on A(1,2)

Note that 1 is the leading column.

Consider the following query:

select 4

from A,B,C

where 3 = 10

and 1 = 1

and 2 = 2

and 3 = 5

We could represent the joins present in the query using the following schematic:

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

col3=10 col3=5

There are really only 2 ways we can drive the query: via 3 or 3. We would have to do a

Full scan of A to be able to drive off it. This is unlikely to be efficient with large tables;

If we drive off table B, using predicate 3=10 (as a filter or lookup key) then we will retrieve

the value for 1 and join to 1. Because we have now filled the leading column of the

concatenated index on table A we can use this index to give us values for 2 and join to A.

However if we drive off table c, then we only get a value for 2 and since this is a trailing

column of a concatenated index and the leading column has not been supplied at this point, we

cannot use the index on a to lookup the data.

So it is likely that the best join order will be B A C. The CBO will obviously use costs to establish

whether the individual access paths are a good idea or not.

If the CBO does not choose this join order then we can hint it by changing the

from clause to read:

from B,A,C

and using the /*+ ordered */ hint. The resultant query would be:

select /*+ ordered */ 4

from B,A,C