RE: Which plan is better - what COST really means ?

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

--
http://www.freelists.org/webpage/oracle-l

Other related posts: