RE: Which plan is better - what COST really means ?
- From: J.Velikanovs@xxxxxxxx
- Date: Sun, 16 Jan 2005 17:03:31 +0200
======================================================================
GRANT TOTAL:
COST measured in SINGLE BLOCK PHYSICAL IO UNITS
COST represents
1. Before 9i (9i as well if cpu_costing feature doesn?t used)
IO OPERATIONS COUNT.
2. Starting from (9i if cpu_costing feature is used)
TIME to execute SQL.
======================================================================
(Wolfgang Breitling)
======================================================================
>The "cost" in the CBO's calculations is basically the count of the number
>of blocks that need to be examined in order to get the result.
(Jonathan Lewis)
======================================================================
>One day someone, somewhere, will agree
>with me that the cost of a query IS and always
>has been the optimizer's estimate of the actual
>run time of a query - with the slightly idiosyncratic
>"assumed time for a single block read" as the unit
>of measure.
I agree now! But only starting with Oracle version 9i, if cpu_costing
feature is on.
======================================================================
I had an offline discussion with Jonathan (as well as with Wolfgang) on
this subject.
Jonathan kindly pointed me on his article ?Understanding System
Statistics?
http://www.oracle.com/technology/pub/articles/lewis_cbo.html.
That explains a lot. I would title this articles ?Revolution of Optimizer
thinking?.
I recommend you to read this one and to start to use cpu_costing feature
(available starting from 9i), if you didn?t it before.
To summarized what I have understood I can say:
1. If you don?t use cpu_costing (possible starting from 9i and not enabled
by default) the
A. COST figure represents PHYSICAL IO COUNT need to be done to execute
particular query (CBO thoughts about that of course).
B. Cost MEASURED in SINGLE BLOCK PHYSICAL IO UNITS.
C. Optimizer converts multi blocks reads
(mrcount/db_file_multiblock_read_count), as well as aware how many blocks
possible already memory (using optimizer_index_% parameters).
2. If you use cpu_costing
A. COST figure represents how much TIME takes to execute query (CBO
prediction).
B. Cost MEASURED in SINGLE BLOCK PHYSICAL IO UNITS.
C. Any operation execution time converted to sbpIO units.
Taken from the article:
Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
#CPUCycles / cpuspeed
) / sreadtim
There for #CPUCycles calculations:
Oracle keeps an internal reference list of basic operations with their
costs in the form: "operation X requires N cpu units," and this list is
calibrated against the cpuspeed element of system statistics.
GRANT TOTAL:
COST measured in SINGLE BLOCK PHYSICAL IO UNITS
COST represents
1. Before 9i (9i as well if cpu_costing feature doesn?t used) IO
OPERATIONS COUNT.
2. Starting from (9i if cpu_costing feature is used) TIME to execute SQL.
Jurijs
+371 9268222 (+2 GMT)
============================================
Thank you for teaching me.
http://otn.oracle.com/ocm/jvelikanovs.html
On 2005.01.15 20:10:07 oracle-l-bounce wrote:
>At 10:00 AM 1/15/2005, you wrote:
>
>>Which type of IO by your opinion optimizer takes as base type for cost
>>estimating?
>>As far as I know different types of operations (FS, IRS, IQS, IFS,
FILTER)
>>execute IO differently. I mean CPU utilization is different for single
IO
>>unit in case of different operations.
>>
>>Another question is If cost is IO, how optimizer estimates sorting/hash
>>etc. actions in IO units?
>
>The "cost" in the CBO's calculations is basically the count of the number
>of blocks that need to be examined in order to get the result. For
example,
>for a fully qualified primary key access to a row one block in each of
the
>index levels needs to be examined from the root over the branch blocks to
>the leaf and then the data blocks needs to be examined, ergo the cost is
>"LVLS +1", the height of the index plus one. For a full table scan, each
>block up to the hwm needs to be examined. The CBO is aware of multiblock
>reads, so the number of blocks is divided be
db_file_multi_block_read_count
>to come up with a basic cost for the FTS. However, the CBO is also aware
>that not every multiblock read will be set up to read dfmrc blocks so it
>applies a fudge factor to the basic FTS cost => table-scan-cost (tsc) =
>blocks/dfmrc*fudge-factor.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
