copied from http://blog.oracle.com.cn/html/17/t-2917.html
NOTE:40656.1 [Image]
------------------------------------------------------------------------
Article-ID: <Note:40656.1>
Circulation: REVIEW_READY (INTERNAL)
Open-Remarks: 2. See <RemarksOn:NOTE:40656.1>
Folder: server.Rdbms.Performance.SqlTuning
Topic: ** Cost Based Optimizer (CBO)
Platform: GENERIC Generic issue
Subject: Interpreting 10053 trace output
Author: STEVE DIXON
Creation-Date: 22-AUG-1996 14:40:33
Modified-Date: 22-APR-1999 09:04:26
Revision: 1
Document-Type: FAQ
Impact: MEDIUM
Skill-Level: NOVICE
Content-Type: TEXT/PLAIN
Attachments: NONE
Purpose
=======
This article explains how to interpret output generated by the 10053 event.
It highlights the most important sections in the trace file that is produced
and explains how to interpret the information contained within.
It then provides some simple examples for illustration purposes.
The information presented is pertinent to release 8.0.5 but should be
applicable to other releases.
Audience
========
This is an internal document intended to help support analysts interpret
trace output from the 10053 event which details how the CBO has optimizer
queries presented to it. The event itself is not really all that useful to
customers as all it does is confirm what the optimizer has done and is
also difficult to interpret. It is more useful for support or development.
Event 10053
===========
Event 10053 details the choices made by the CBO in evaluating the execution
path for a query. It externalises most of the information that the optimizer
uses in generating a plan for a query. It is useful when there is the
possibility that the optimizer has made an incorrect choice based on the
available statistics.
The 10053 trace output is broken down into a number of sections that
broadly reflect the stages that the optimizer goes through in evaluating a
plan. These stages are as follows:
o Query
o Parameters used by the optimizer
o Base Statistical Information
o Base Table Access Cost
o Join Order and Method Computations
o Recosting for special features
o OR Expansion
o ORDER BY using indexes to avoid sorts
o Partition Views (7.3+)
Setting the event can be done at the session level as follows:
alter session set events '10053 trace name context forever';
The query MUST be parsed with the event turned on for trace output to be
produced. The RBO does not produce any output with this event.
Important information
=====================
This section outlines the most important information in the 10053 trace file.
A simple trace file has been broken into section to illustrate each section.
Each section has then been discussed in turn.
Query
=====
This reports the query that has been submitted to the optimizer for
optimization the query does not reflect any changes that may have been
made to the query such as view merging, subquery merging or transitivity.
Parameters used by the optimizer
================================
This section reflects the parameters that were set at the time the query
was optimized. This could be useful in order to check that there are
no parameters that are modifying plans or changing costs.
Base Statistical Information
============================
The base statistics reflect the table, index and column statistics
pertinent to the query at hand. In later releases this only reports
columns that are actually specified in the query. Many of these statistics
are only really useful for specific problem areas. Column statistics are
included here even though they tend to appear in the report when they
are required as opposed to at the beginning.
Example output:
BASE STATISTICAL INFORMATION
***********************
Table stats Table: EMP Alias: E
TOTAL :: CDN: 14 NBLKS: 1399 TABLE_SCAN_CST: 213 AVG_ROW_LEN: 40 DEGREE
FROM KXFRDEG(): 0
-- Index stats
INDEX#: 7202 COL#: 8
TOTAL :: LVLS: 0 #LB: 1 #DK: 3 LB/K: 1 DB/K: 1 CLUF: 1
***********************
Table stats Table: DEPT Alias: D
TOTAL :: CDN: 4 NBLKS: 1 TABLE_SCAN_CST: 1 AVG_ROW_LEN: 22 DEGREE FROM
KXFRDEG(): 0
Column: DNAME Col#: 2 Table: DEPT Alias: D
NDV: 4 NULLS: 0 DENS: 2.5000e-01
Table stats
~~~~~~~~~~~
Table stats Table: EMP Alias: E
TOTAL :: CDN: 14 NBLKS: 1399 TABLE_SCAN_CST: 213 AVG_ROW_LEN: 40 DEGREE
FROM KXFRDEG(): 0
Table: Table name
Alias: Table alias as used in the query
CDN: Table Cardinality - number of rows in the table
NBLKS: Number of Blocks in the table
TABLE_SCAN_CST: Calculated cost of a full table scan (FTS)
AVG_ROW_LEN: Average row length in bytes
DEGREE FROM KXFRDEG(): Degree of parallelism defined on the table
Index stats
~~~~~~~~~~~
-- Index stats
INDEX#: 7202 COL#: 8
TOTAL :: LVLS: 0 #LB: 1 #DK: 3 LB/K: 1 DB/K: 1 CLUF: 1
INDEX#: OBJECT_ID of the index
COL#: Column numbers that the index refers to in order
LVLS: Number of Levels in the B*Tree
#LB: Number of Leaf blocks
#DK: Number of Distinct Key values
LB/K: Number of Leaf blocks per key value
DB/K: Number of Base table Data blocks per key value
CLUF: Clustering factor see <Note:39836.1>
Column Stats
~~~~~~~~~~~~
If 1/NDV does not equal the DENS figure then it is likely that there are
histograms present on the column.
Column: DNAME Col#: 2 Table: DEPT Alias: D
NDV: 4 NULLS: 0 DENS: 2.5000e-01
Column: Column Name
Col#: Column Id
Table: Table name
Alias: Table alias
NDV: Number of Distinct values in that column
NULLS: Number of NULL values in the column
DENS: Density of the column. This is a measure of selectivity that is
used in some circumstances See <Note:68992.1> and <Note:43041.1>
Also a High and Low value for the column may be included:
LO: Low value in the column
HI: High value in the column
The base table statistics section is mainly useful for checking back against
to see where a particular calculated value came from after examining other
areas of the trace file.
Some of the more general statistics can be checked back against the real
values such as Cardinality and Number of Blocks.
Base Table Access Cost
======================
This section defines the best access method for each table involved in a
query on a cost basis. Access cost and predicted (Computed) Cardinality
are produced based on the supplied predicates.
This is the first thing that should be looked at when examining a 10053
trace. The base table access costs define the starting point for
evaluating plans. If these figures are inaccurate then the final plan
cannot be expected to be accurate.
SINGLE TABLE ACCESS PATH
TABLE: DEPT ORIG CDN: 4 CMPTD CDN: 1
Access path: tsc Resc: 1 Resp: 1
BEST_CST: 1.00 PATH: 2 Degree: 1
TABLE: Table name
ORIG CDN: Original Cardinality of the table (this should be the
same as in the base statistics section, Single table
predicate selectivities are applied to this to give the
Computed cardinality value.
CMPTD CDN: Computed cardinality. This is the expected number of
rows returned from the table after all single table
predicates have been applied. It is calculated by
applying the combined column selectivities of all
single table predicates to the Original cardinality.
Access path: Access method being evaluated.
'tsc' is a Full table scan.
Other Methods are index based and can
include one of the following:
unique: Fully satisfied unique
index with = value
no sta/stp keys: Range Access with no start or
stop key (unbounded Range)
eq-unique: Cost for Range access of index
with a fully qualified key
join index
stp-guess
join stp
equal
index-only
scan
Resc: Serial cost
Resp: Cost after applying the relevant degree of parallelism.
BEST_CST: Cost of the access method chosen for the single table.
PATH: Access method chosen. See the list in the reference section
Degree: Degree of parallelism on to the table
Things to look for in the single table access path section:
o The PATH defines which access method was chosen. This PATH will cost
the least.
o The cost derived in this section is then used throughout the remainder
of the trace file as a basis for calculations.
o computed cardinality is very important for Nested loop joins and sorts
as it can define the amount of processing required. If the computed
cardinality is 1 then the optimizer is more likely to choose cartesian
products. If the optimizer is 100% sure that there is a single row table
present (there needs to be a unique index present for this to happen) then
the optimizer will always start the join orders with this table.
o degree of parallelism can have a drastic effect on the cost of FTS. The
optimizer's use of parameters in determining plans can be modified using
the optimizer_percent_parallel parameter.
Join Order and Method Computations
==================================
This section forms the main body of the report and can be very large
dependent on the number of tables in the query and use of special features.
Potentially the optimizer will consider n! (n factorial) join orders where
n is the number of tables although there are a number of cutoffs in place.
A 7 table join will potentially examine 5040 separate join orders applying
each valid join method. This can have a significant effect on the parse
time and size of the trace file produced. The initial join order
is chosen by ordering the tables in order of increasing computed cardinality
(the ordered hint will force computation to take place on a single join
order, defined by the from clause order, Left to Right). Each join order is
taken in turn and join methods are evaluated on a cost basis. The cheapest
join method is retained. This is then compared with the next join order
cost and the cheapest is retained. This is continued until all join orders
are exhausted.
Remember that the optimizer will not start to evaluate plans that are more
expensive than the best cost already produced. If this happens, then all
you will see is the join order heading followed something else that is
nothing to do with the join order evaluation. This is what has happened in
both of the examples at the end of this article.
Each of the 3 join methods available are evaluated in turn.
GENERAL PLANS
***********************
Join order[1]: DEPT [ D] EMP [ E]
Now joining: EMP *******
NL Join
Outer table: cost: 1 cdn: 1 rcz: 22 resp: 1
Inner table: EMP
Access path: tsc Resc: 213
Join resc: 214 Resp: 214
Column: DEPTNO Col#: 8 Table: EMP Alias: E
NDV: 3 NULLS: 0 DENS: 3.3333e-01 LO: 10 HI: 30
Column: DEPTNO Col#: 1 Table: DEPT Alias: D
NDV: 4 NULLS: 0 DENS: 2.5000e-01 LO: 10 HI: 40
Access path: index (join index)
INDEX#: 7202 TABLE: EMP
CST: 1 IXSEL: 0.0000e+00 TBSEL: 3.3333e-01
Join resc: 2 resp:2
Join cardinality: 5 = outer (1) * inner (14) * sel (3.3333e-01) [flag=0]
Best NL cost: 2 resp: 2
SM Join
Outer table:
resc: 1 cdn: 1 rcz: 22 deg: 1 resp: 1
Inner table: EMP
resc: 213 cdn: 14 rcz: 20 deg: 1 resp: 213
SORT resource Sort statistics
Sort width: 12 Area size: 49152 Degree: 1
Blocks to Sort: 1 Row size: 35 Rows: 1
Initial runs: 1 Merge passes: 1 Cost / pass: 3
Total sort cost: 3
SORT resource Sort statistics
Sort width: 12 Area size: 49152 Degree: 1
Blocks to Sort: 1 Row size: 32 Rows: 14
Initial runs: 1 Merge passes: 1 Cost / pass: 3
Total sort cost: 4
Merge join Cost: 221 Resp: 221
HA Join
Outer table:
resc: 1 cdn: 1 rcz: 22 deg: 1 resp: 1
Inner table: EMP
resc: 213 cdn: 14 rcz: 20 deg: 1 resp: 213
Hash join one ptn: 1 Deg: 1
mem: 64 r: 65 s: 1 c: 2
Hash join Resc: 215 Resp: 215
Join result: cost: 2 cdn: 5 rcz: 42
Best so far: TABLE#: 0 CST: 1 CDN: 1 BYTES: 22
Best so far: TABLE#: 1 CST: 2 CDN: 5 BYTES: 210
***********************
Join order section:
===================
Join order[1]: DEPT [ D] EMP [ E]
Now joining: EMP *******
Join order[1]: This line defines the join order chosen. The '1' indicates
that this is the first join order evaluated. The tables
and aliases are listed in order.
Now joining: Table that is currently being joined by the
methods that follow
The join sections are made all up of 3 parts, outer table, inner table and a
summary section:
Nested Loop Join section:
=========================
NL Join
Outer table: cost: 1 cdn: 1 rcz: 22 resp: 1
Inner table: EMP
Access path: tsc Resc: 213
Join resc: 214 Resp: 214
Column: DEPTNO Col#: 8 Table: EMP Alias: E
NDV: 3 NULLS: 0 DENS: 3.3333e-01 LO: 10 HI: 30
Column: DEPTNO Col#: 1 Table: DEPT Alias: D
NDV: 4 NULLS: 0 DENS: 2.5000e-01 LO: 10 HI: 40
Access path: index (join index)
INDEX#: 7202 TABLE: EMP
CST: 1 IXSEL: 0.0000e+00 TBSEL: 3.3333e-01
Join resc: 2 resp:2
Join cardinality: 5 = outer (1) * inner (14) * sel (3.3333e-01) [flag=0]
Best NL cost: 2 resp: 2
A nested loop join takes rows from the first table (or row source) and
uses the information to lookup in a second row source. The outer
(or driving) table is scanned first followed by a probe of the inner table.
Outer Table
~~~~~~~~~~~
This is information on the table driving the nested loop join.
Outer table: cost: 1 cdn: 1 rcz: 22 resp: 1
Outer table: cost: The cost of accessing the outer (driving table)
using the single table access method defined above.
cdn: (Computed) Cardinality of the outer table calculated
in the single table access section
rcz: Row size
resp: Parallel cost
Inner Table
~~~~~~~~~~~
This information relates to the lookup part of the nested loop join.
New access methods may have now become available due to the prescence
of join predicates that were unavailable when considering the table
standalone in the single table section. These methods are costed out
and displayed in a similar fashion to that in the Single table section.
The first method costed is a FTS followed by an index methods.
Inner table: Table name of the inner table
Access path: This is the access path being evaluated. The value
of this determines the format of the next part of
the report
Specific to tsc:
~~~~~~~~~~~~~~~~
Access path: tsc
Resc: This is the cost of accessing the inner table in
serial with this method
Because the above method used a FTS there was no need to examine the
columns that were joined. In the next step, an index is considered so
column information needs to be examined to determine how much of the
index will need to be examined.
To this end some column information is output.
Column: DEPTNO Col#: 8 Table: EMP Alias: E
NDV: 3 NULLS: 0 DENS: 3.3333e-01 LO: 10 HI: 30
Column: DEPTNO Col#: 1 Table: DEPT Alias: D
NDV: 4 NULLS: 0 DENS: 2.5000e-01 LO: 10 HI: 40
The column information is similar to that seen earlier apart from the
Low and High values:
LO: Low value in the column
HI: High value in the column
Specific to Index join methods:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
e.g. Access path: index (join index)
INDEX#: Object Id of the index
TABLE: Table that the index is based upon
CST: Cost of accessing the table using this index
IXSEL: Index selectivity
TBSEL: Table selectivity
Join cost:
Join resc: Cost of the join in serial.
Formula for costing a nested loop join is:
Cost of outer +
(Cardinality of outer * Cost of inner)
Using the figures in the example:
Outer table: cost: 1 cdn: 1 rcz: 22 resp: 1
Inner table: EMP
Access path: tsc Resc: 213
Join resc: 214 Resp: 214
Cost of outer (Outer table: cost: 1) +
(
Cardinality of outer (cdn: 1) *
Cost of inner (Resc: 213)
)
1 + (1 * 213) = 214
resp: Cost of joining in parallel.
Nested loops summary section
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
This section summarises the information for a nested loop join between
the 2 tables.
Join cardinality: 5 = outer (1) * inner (14) * sel (3.3333e-01) [flag=0]
Best NL cost: 2 resp: 2
Join cardinality: Expected number of rows to be produced from the
join. This should be independent of the join method
used for a particular join order.
Join selectivity is calculated as follows
see <Note:68992.1> for more information:
Sel = 1/max[NDV(t1.c1),NDV(t2.c2)] *
( (Card t1 - # t1.c1 NULLs) / Card t1) *
( (Card t2 - # t2.c2 NULLs) / Card t2)
Best NL cost: Best serial cost for a nested loop join. Note that
the method is not displayed. It has to be worked
out from the cost figure chosen.
resp: Best parallel cost
Sort Merge Join section:
========================
The sort merge join section is very similar to the NL section except that
it has a sorting section which computes the sort costs. Note that the
join cardinality computation does not need to be recalculated since it
is valid for all join methods.
SM Join
Outer table:
resc: 1 cdn: 1 rcz: 22 deg: 1 resp: 1
Inner table: EMP
resc: 213 cdn: 14 rcz: 20 deg: 1 resp: 213
SORT resource Sort statistics
Sort width: 12 Area size: 49152 Degree: 1
Blocks to Sort: 1 Row size: 35 Rows: 1
Initial runs: 1 Merge passes: 1 Cost / pass: 3
Total sort cost: 3
SORT resource Sort statistics
Sort width: 12 Area size: 49152 Degree: 1
Blocks to Sort: 1 Row size: 32 Rows: 14
Initial runs: 1 Merge passes: 1 Cost / pass: 3
Total sort cost: 4
Merge join Cost: 221 Resp: 221
A sort merge join reads data from the outer table, sorts it, reads data
from the second table, sorts that in to the same order and then merges
the 2 sorted runs to produce a join. The order of the inner and outer
tables should be irrelevant to the cost of the join since each side of
the join is independent of the other.
@ However in the trace files you may notice that the costs are actually
@ different. This is probably a bug.
Outer table:
~~~~~~~~~~~~
Outer table:
resc: 1 cdn: 1 rcz: 22 deg: 1 resp: 1
resc: Serial cost of accessing the outer table
cdn: Cardinality of outer table
rcz: Average row size in bytes
deg: Degree of parallelism
resp: Parallel cost of accessing the outer table
This is very similar to the NL section
Inner table:
~~~~~~~~~~~~
Inner table: EMP
resc: 213 cdn: 14 rcz: 20 deg: 1 resp: 213
Inner table: Name of the inner table
resc: Serial cost of accessing the outer table
cdn: Cardinality of outer table
rcz: Average row size in bytes
deg: degree of parallelism
resp: Parallel cost of accessing the outer table
Sorting section:
~~~~~~~~~~~~~~~~
The sort section defines the costs associated with sorting each side of the
join. It starts with the outer table sort cost and then the inner.
Remember that it is possible to use an index (which is already presorted)
instead of doing a sort if there is an index on the join key or an index
has been chosen as the best cost single table access path. The
figures themselves are not usually all that useful:
SORT resource Sort statistics
Sort width: 12 Area size: 49152 Degree: 1
Blocks to Sort: 1 Row size: 35 Rows: 1
Initial runs: 1 Merge passes: 1 Cost / pass: 3
Total sort cost: 3
SORT resource Sort statistics
Sort width: 12 Area size: 49152 Degree: 1
Blocks to Sort: 1 Row size: 32 Rows: 14
Initial runs: 1 Merge passes: 1 Cost / pass: 3
Total sort cost: 4
Sort width: Width of the sort
Area size: Sort area size used
Degree: Degree of parallelism used
Blocks to Sort: Number of blocks to be sorted
Row size: Size of the row
Rows: Number of rows
Initial runs: Number of sort runs produced
Merge passes: Number of sort run merges required
Cost / pass: Cost for each merge run
Total sort cost: Total cost for the sort
Merge join Summary:
~~~~~~~~~~~~~~~~~~~
Formula for costing a Sort Merge Join is as follows:
(Cost of accessing outer table + outer sort cost) +
(Cost of accessing inner table + inner sort cost)
Substituting in the values from the example:
(
Cost of accessing outer table (resc: 1) +
outer sort cost (Total sort cost: 3)
) +
(
Cost of accessing inner table (resc: 213) +
inner sort cost (Total sort cost: 4)
(
(1 + 3) + (213 + 4) = 221
Merge join Cost: 221 Resp: 221
Merge join Cost: Serial merge join cost
Resp: Parallel merge join cost
Hash Join Section:
==================
The hash join section is again very similar to the NL section except that
it has a hash join section which computes the hash cost. The outer and
inner sections are identical to the Sort merge join section. Remember
that hash joins that have been written to disk can swap sides if on disk
data from the inner table is smaller that that from the outer table.
HA Join
Outer table:
resc: 1 cdn: 1 rcz: 22 deg: 1 resp: 1
Inner table: EMP
resc: 213 cdn: 14 rcz: 20 deg: 1 resp: 213
Hash join one ptn: 1 Deg: 1
mem: 64 r: 65 s: 1 c: 2
Hash join Resc: 215 Resp: 215
Hashing section:
~~~~~~~~~~~~~~~~
Hash join one ptn: 1 Deg: 1
mem: 64 r: 65 s: 1 c: 2
Hash join one ptn: Cost of the join part of the hash join
Deg: Degree of parallelism
mem: Available memory
r: Size of Inner in blocks (if r < mem then r := mem + 1)
s: Size of Outer in blocks
c: Number of passes required. Formula c= ceil(r/mem).
c is always going to be 2 or more.
Hash Join summary section:
~~~~~~~~~~~~~~~~~~~~~~~~~~
Formula for the cost of a hash join is:
Cost of accessing outer table +
Cost of building hash table +
Cost of accessing inner table
Substituting in the values from the example:
Cost of accessing outer table (resc: 1) +
Cost of building hash table (Hash join one ptn: 1) +
Cost of accessing inner table (resc: 213)
= 1 + 1 + 213 = 215
Hash join Resc: 215 Resp: 215
Hash join Resc: Serial cost of performing hash join
Resp: Parallel cost of performing hash join
Join Summary Section:
=====================
This section summarises the best cost information for the join order in
question. You have to check back through the join methods to determine
which method has actually been used. If 2 join methods have an identical
cost, then the first method found will be chosen (this effectively gives
and order of NL > SMJ > HAJ).
Join result: cost: 2 cdn: 5 rcz: 42
Join result: cost: Cost of the best join method for the join order
cdn: Cardinality of the join
rcz: Average row size returned by the join
Best so far:
~~~~~~~~~~~~
This section shows the best join costs and cardinalities discovered so far.
Best so far: TABLE#: Table number (order is the order of evaluation in single
table access path).
CST: Best Cost for this table in a join
CDN: Best join cardinality (different join orders can produce
different cardinalities since earlier objects may
have eliminated/failed to eliminate rows)
BYTES: Best number of bytes
Recosting for special features
==============================
Where certain features have been used in the query the optimizer considers
different costing possibilities. Unfortunately this can greatly expand the
amount of information produced in the trace file. The special features
include:
OR Expansion
~~~~~~~~~~~~
The CBO uses costs to decide if it should expand inlists and OR statements
as a recosting phase after it has determined the base join costs for each
join order. This is covered later.
Order by using indexes to avoid sorts
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Once a base cost has been computed, we recompute the costs but use indexes
instead of sorting
Partition Views
~~~~~~~~~~~~~~~
With 7.3 partition views we compute a base cost for the whole partition
as if it were a table. Then we recost each individual table to find the
best access path for each table in the view.
These sections tend to add little new in terms of fields as they are simply
repeats of earlier steps but this time using the new feature. Each section
starts with a line such as:
INlist/Or predicate:
~~~~~~~~~~~~~~~~~~~~
******** Next OR predicate ********
******** OR-branching ********
Sort Recosting:
~~~~~~~~~~~~~~~
****** Recost for ORDER BY (using join row order) *******
***************************************
Partition view:
~~~~~~~~~~~~~~~
This section does not have a heading as such. What the optimizer does is
computes the plan as though the Partition View (PV) is a table. Then it
goes back and checks that the plan chosen is appropriate for each individual
table in the PV. The way to spot this is that there will appear to be
a completely new 10053 trace output with no QUERY line starting it off.
This section will be the recosting for a particular table.
Final Cost section
===================
This section reports the cost of the cheapest plan. It does not report the
join order that provided this.
Final:
CST: 3 CDN: 9 RSC: 3 RSP: 3 BYTES: 378
Extras
======
Later versions of trace will automatically print the explain plan in the trace
file under the heading 'PLAN'. This can be very useful.
Suggested order of 10053 examination
====================================
o Base table access paths - check that the base costs and access methods are
appropriate. If they are not then check the base statistics to determine
why they may be different from expected.
o Join order - check the explain plan to determine which join order has been
chosen. Then find this join order in the 10053 output. Look at the join
method computations to determine why this method has been chosen.
Remember that hash joins can 'swap sides' so the actual join order you
see in the plan may not actually be in the 10053 as such but is there in
a 'pre side swapped' form. With a large trace it is often useful to extract
all the join order lines from the trace file using a command such as grep
(or equivalent) e.g. grep 'Join order' <
ilename>
Reference information:
~~~~~~~~~~~~~~~~~~~~~~
Access path: This is the access method being evaluated.
Alias: Table alias as used in the query
Area size: Sort area size used
AVG_ROW_LEN: Average row length in bytes
BEST_CST: Cost of the access method chosen for the single table.
Best NL cost: Best serial cost for a nested loop join.
Blocks to Sort: Number of blocks to be sorted
c: Number of passes required. Formula c= ceil(r/mem).
c is always going to be 2 or more.
cdn: (Computed) Cardinality of the outer table calculated
in the single table access section
CDN: Table Cardinality - number of rows in the table
CLUF: Clustering factor see <Note:39836.1>
CMPTD CDN: Computed cardinality. This is the expected number of
rows returned from the table after all single table
predicates have been applied. It is calculated by
applying the combined column selectivities of all
single table predicates to the Original cardinality.
Col#: Column Id
COL#: Column uid that an index refers to in order
Column: Column Name
DB/K: Number of Base table Data blocks
per key value
Deg: Degree of parallelism
Cost / pass: Cost for each merge run
CST: Cost of accessing the table using this index
DENS: Density of the column. This is a measure of
selectivity that is used in some circumstances.
See <Note:68992.1> and <Note:43041.1>
Degree: Degree of parallelism on to the table
DEGREE FROM KXFRDEG(): Degree of parallelism defined on the table
#DK: Number of Distinct Key values
Hash join one ptn: Cost of the join part of the hash join
HI: Highest value in the column
INDEX#: OBJECT_ID of the index
Initial runs: Number of sort runs produced
Inner table: Table name of the inner table
IXSEL: Index selectivity
Join order[1]: Defines the join order chosen. The 1 indicates that
this is the first join order evaluated.
The tables and aliases are listed in order.
Join cardinality: Expected number of rows to be produced from the
join. This should be independent of the join method
used for a particular join order.
Join resc: Cost of the join in serial.
LB/K: Number of Leaf blocks per key value
LVLS: Number of Levels in the B*Tree
#LB: Number of Leaf blocks
LO: Lowest value in the column
mem: Available memory
Merge passes: Number of sort run merges required
NBLKS: Number of Blocks in the table
NDV: Number of Distinct values in that column
Now joining: This is the table that is currently being joined
by the methods that follow
NULLS: Number of NULL values in the column
ORIG CDN: Original Cardinality of the table (this should be the
same as in the base statistics section, Single table
predicate selectivities are applied to this to give the
Computed cardinality value.
PATH: This is the access method chosen. List elsewhere.
r: Size of Inner in blocks (if r < mem then r := mem + 1)
rcz: Row size
Resc: Cost of an operation in serial.
Resp: Cost of an operation in parallel
Row size: Size of the row
Rows: Number of rows
RSC: Serial cost
RSP: Parallel cost
s: Size of Outer in blocks
Sort width: Width of the sort
Table: Table name
TABLE_SCAN_CST: Calculated cost of a full table scan (FTS)
TBSEL: Table selectivity
Total sort cost: Total cost for the sort
PATH values from 10053 trace output
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The PATH field above shows the cheapest access method for the table.
This value can be decoded from the list below from kko.h. (8.0.5.)
#define KKOAPPAR 0 /* parallel hint, not stored */
#define KKOAPNON 1 /* no access path spec */
#define KKOAPTSC 2 /* Table Scan */
#define KKOAPIXU 3 /* Index Unique */
#define KKOAPIXR 4 /* Index Range */
#define KKOAPAND 5 /* Index And-Equal */
#define KKOAPORD 6 /* Order by using an index */
#define KKOAPOCL 7 /* Open Cluster */
#define KKOAPHSH 8 /* Hash Cluster */
#define KKOAPRID 9 /* Rowid Lookup */
#define KKOAPIXB 10 /* range scan backwards */
#define KKOAPRIR 11 /* RowId Range scan */
#define KKOAPDRV 12 /* driving_site hint, not stored */
#define KKOAPCAC 14 /* cache hint, not stored */
#define KKOAPNCA 15 /* nocache hint, not stored */
#define KKOAPPRT 16 /* partitions hint, not stored */
#define KKOAPNPR 17 /* nopartitions hint, not stored */
#define KKOAPANT 18 /* anti-join, not stored */
#define KKOAPIRR 19 /* Index Rowid Range scan */
#define KKOAPBMI 20 /* bitmap index *
#define KKOAPPIA 21 /* parallel_index hint, not stored */
#define KKOAPPID 22 /* noparallel_index hint, not stored */
#define KKOAPIFF 23 /* index fast full scan */
#define KKOAPSWP 24 /* swap inputs to join */
#define KKOAPFTB 25 /* fact table */
#define KKOAPNFT 26 /* not a fact table */
#define KKOAPMVI 27 /* merge of this view */
#define KKOAPNPP 28 /* dont push join predicate into this view */
#define KKOAPPPJ 29 /* push join predicate into this view */
#define KKOAPNMV 30 /* no_merge of this view */
#define KKOAPSEM 31 /* semi-join, not stored */
===============================================================================
===============================================================================
===============================================================================
Examples
========
Data Setup Assumptions:
~~~~~~~~~~~~~~~~~~~~~~~
Emp and Dept are the standard EMP & DEPT tables but EMP has been manipulated
to increase its FTS cost (lots of rows were inserted and deleted to raise
the Highwater Mark of the table and thus the number of blocks to be read
on FTS).
Both Tables have been analyzed with compute.
In addition, Emp has an single column, non-unique, index on its' deptno
column. This index was created after the emp rows had been deleted and so
is unaffected. The index is called E_DEPTNO_IX and its' OBJECT_ID is 7202.
2 examples are presented:
o simple join
o join with an IN list
Simple join:
~~~~~~~~~~~~
Consider the following select and explain plan.
select ename
from emp e,dept d
where e.deptno = d.deptno
and d.dname = 'SALES';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=210)
1 0 NESTED LOOPS (Cost=2 Card=5 Bytes=210)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=1 Bytes=22)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=14 Bytes=280)
4 3 INDEX (RANGE SCAN) OF 'E_DEPTNO_IX' (NON-UNIQUE)
The output is fairly simple to interpret. Note that the second join order
has not been examined because the cost of accessing the first table EMP (214)
is much bigger than then cost of the cheapest plan so far - NL (3). This
join order has therefore been 'pruned'.
Example trace taken from 10053 output (8.0.5.0.0):-
QUERY
select ename
from emp e,dept d
where e.deptno = d.deptno
and d.dname = 'SALES'
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
OPTIMIZER_PERCENT_PARALLEL = 0
OPTIMIZER_MODE/GOAL = Choose
HASH_AREA_SIZE = 131072
HASH_JOIN_ENABLED = TRUE
HASH_MULTIBLOCK_IO_COUNT = 1
OPTIMIZER_SEARCH_LIMIT = 5
SORT_AREA_SIZE = 65536
SORT_DIRECT_WRITES = FALSE
SORT_WRITE_BUFFER_SIZE = 32768
PARTITION_VIEW_ENABLED = FALSE
FAST_FULL_SCAN_ENABLED = FALSE
DB_FILE_MULTIBLOCK_READ_COUNT = 8
ALWAYS_STAR_TRANSFORMATION = FALSE
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats Table: DEPT Alias: D
TOTAL :: CDN: 4 NBLKS: 1 TABLE_SCAN_CST: 1 AVG_ROW_LEN: 22 DEGREE FROM
KXFRDEG(): 0
***********************
Table stats Table: EMP Alias: E
TOTAL :: CDN: 14 NBLKS: 1399 TABLE_SCAN_CST: 213 AVG_ROW_LEN: 40 DEGREE
FROM KXFRDEG(): 0
-- Index stats
INDEX#: 7202 COL#: 8
TOTAL :: LVLS: 0 #LB: 1 #DK: 3 LB/K: 1 DB/K: 1 CLUF: 1
***************************************
SINGLE TABLE ACCESS PATH
TABLE: EMP ORIG CDN: 14 CMPTD CDN: 14
Access path: tsc Resc: 213 Resp: 213
BEST_CST: 213.00 PATH: 2 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
Column: DNAME Col#: 2 Table: DEPT Alias: D
NDV: 4 NULLS: 0 DENS: 2.5000e-01
TABLE: DEPT ORIG CDN: 4 CMPTD CDN: 1
Access path: tsc Resc: 1 Resp: 1
BEST_CST: 1.00 PATH: 2 Degree: 1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]: DEPT [ D] EMP [ E]
Now joining: EMP *******
NL Join
Outer table: cost: 1 cdn: 1 rcz: 22 resp: 1
Inner table: EMP
Access path: tsc Resc: 213
Join resc: 214 Resp: 214
Column: DEPTNO Col#: 8 Table: EMP Alias: E
NDV: 3 NULLS: 0 DENS: 3.3333e-01 LO: 10 HI: 30
Column: DEPTNO Col#: 1 Table: DEPT Alias: D
NDV: 4 NULLS: 0 DENS: 2.5000e-01 LO: 10 HI: 40
Access path: index (join index)
INDEX#: 7202 TABLE: EMP
CST: 1 IXSEL: 0.0000e+00 TBSEL: 3.3333e-01
Join resc: 2 resp:2
Join cardinality: 5 = outer (1) * inner (14) * sel (3.3333e-01) [flag=0]
Best NL cost: 2 resp: 2
SM Join
Outer table:
resc: 1 cdn: 1 rcz: 22 deg: 1 resp: 1
Inner table: EMP
resc: 213 cdn: 14 rcz: 20 deg: 1 resp: 213
SORT resource Sort statistics
Sort width: 12 Area size: 49152 Degree: 1
Blocks to Sort: 1 Row size: 35 Rows: 1
Initial runs: 1 Merge passes: 1 Cost / pass: 3
Total sort cost: 3
SORT resource Sort statistics
Sort width: 12 Area size: 49152 Degree: 1
Blocks to Sort: 1 Row size: 32 Rows: 14
Initial runs: 1 Merge passes: 1 Cost / pass: 3
Total sort cost: 4
Merge join Cost: 221 Resp: 221
HA Join
Outer table:
resc: 1 cdn: 1 rcz: 22 deg: 1 resp: 1
Inner table: EMP
resc: 213 cdn: 14 rcz: 20 deg: 1 resp: 213
Hash join one ptn: 1 Deg: 1
mem: 64 r: 65 s: 1 c: 2
Hash join Resc: 215 Resp: 215
Join result: cost: 2 cdn: 5 rcz: 42
Best so far: TABLE#: 0 CST: 1 CDN: 1 BYTES: 22
Best so far: TABLE#: 1 CST: 2 CDN: 5 BYTES: 210
***********************
Join order[2]: EMP [ E] DEPT [ D]
Final:
CST: 2 CDN: 5 RSC: 2 RSP: 2 BYTES: 210
PLAN
Cost of plan: 2
Operation...........Object name.....Options.........Id...Pid..
SELECT STATEMENT 0
NESTED LOOPS 1
TABLE ACCESS DEPT FULL 2 1
TABLE ACCESS EMP BY INDEX ROWID 3 1
INDEX E_DEPTNO_IX RANGE SCAN 4 3
Select with INlist - shows additional OR processsing:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
OR expansions can be difficult to interpret because of the seemingly
repeated sections.
Again the second join order has not been examined
because the cost of accessing the first table EMP (214)
is much bigger than then cost of the cheapest plan so far - NL (3). This
join order has therefore been 'pruned'.
This trace has the following join sections:
Join order[1]: DEPT [ D] EMP [ E] - evaluated. NL is best cost = 3
Join order[2]: EMP [ E] DEPT [ D] - not evaluated because cost of EMP (214)
> lowest cost - NL (3).
Then the OR recosting. There is a branch for each value. Each join order
is reevaluated:
******** Next OR predicate ********
******** OR-branching ********
...
Join order[1]: DEPT [ D] EMP [ E] - evaluated. NL is best cost = 2
Join order[2]: EMP [ E] DEPT [ D] - not evaluated
******** OR-branching ********
...
Join order[1]: DEPT [ D] EMP [ E] - evaluated. NL is best cost = 2
Join order[2]: EMP [ E] DEPT [ D] - not evaluated
These costs are combined (not shown in the trace) and the cost 2 + 2 = 4
is greater than the unexpanded cost, so that is chosen:
Final:
CST: 3 CDN: 9 RSC: 3 RSP: 3 BYTES: 378
Example trace taken from 10053 output (8.0.5.0.0):-
QUERY
EXPLAIN PLAN SET STATEMENT_ID='PLUS6675' FOR select ename
from emp e,dept d
where e.deptno = d.deptno
and d.dname in ('ACCOUNTING','SALES')
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
OPTIMIZER_PERCENT_PARALLEL = 0
OPTIMIZER_MODE/GOAL = Choose
HASH_AREA_SIZE = 131072
HASH_JOIN_ENABLED = TRUE
HASH_MULTIBLOCK_IO_COUNT = 1
OPTIMIZER_SEARCH_LIMIT = 5
SORT_AREA_SIZE = 65536
SORT_DIRECT_WRITES = FALSE
SORT_WRITE_BUFFER_SIZE = 32768
PARTITION_VIEW_ENABLED = FALSE
FAST_FULL_SCAN_ENABLED = FALSE
DB_FILE_MULTIBLOCK_READ_COUNT = 8
ALWAYS_STAR_TRANSFORMATION = FALSE
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats Table: DEPT Alias: D
TOTAL :: CDN: 4 NBLKS: 1 TABLE_SCAN_CST: 1 AVG_ROW_LEN: 22 DEGREE FROM
KXFRDEG(): 0
***********************
Table stats Table: EMP Alias: E
TOTAL :: CDN: 14 NBLKS: 1399 TABLE_SCAN_CST: 213 AVG_ROW_LEN: 40 DEGREE
FROM KXFRDEG(): 0
-- Index stats
INDEX#: 7202 COL#: 8
TOTAL :: LVLS: 0 #LB: 1 #DK: 3 LB/K: 1 DB/K: 1 CLUF: 1
***************************************
SINGLE TABLE ACCESS PATH
TABLE: EMP ORIG CDN: 14 CMPTD CDN: 14
Access path: tsc Resc: 213 Resp: 213
BEST_CST: 213.00 PATH: 2 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
Column: DNAME Col#: 2 Table: DEPT Alias: D
NDV: 4 NULLS: 0 DENS: 2.5000e-01
TABLE: DEPT ORIG CDN: 4 CMPTD CDN: 2
Access path: tsc Resc: 1 Resp: 1
BEST_CST: 1.00 PATH: 2 Degree: 1
******************