Re: Cost vs efficiency?

<sweidanz_at_yahoo.com> wrote in message
news:Yryx7.9$cH1.347_at_nsw.nnrp.telstra.net...
> I was reading some of the messages in asktom.oracle.com discussions and i
> was wondering about the following:
> One of the example was demonstrating the differences between using IN and
> EXISTS with big and small tables. The only thing that made me confused is
> that the same query when you run it using EXISTS has a larger cost than
> when you run it using IN. And apparently by getting the statistics in each
> run you can see the one with larger cost is more efficient than the one
> with smaller cost, in terms of number of consistent gets, db block gets
> and number of rows returned by each step in the execution plan.
>
> My question is what does the cost mean? My understanding is that the
> optimiser, when using the cost based, will evaluate each execution plan
> and based on the least cost will determine which one to use. The least
> cost means that number of block reads/data and indexes will be minimum and
> in addition to other factors as well. right? So how come larger cost is
> more efficient than smaller cost query?
>
> I wanted to post this question to Tom but apparently there is no posting
> messages to the site at present.
>
> thanks for any help,
> ZS

There is a note on this at Metalink, explaining how CBO works. It's rather large, so I don't think I'm going to post it in full.

Quoting from this note, 35934.1

 For any SQL statement there are a finite number of possible  'execution plans'.

 The 'best plan' will always be the 'best plan' regardless of how it was  arrived at.

 The term 'best plan' may mean either:

• The plan which uses up minimal resource to process all rows affected by the statement. Referred to as ALL_ROWS.
• The plan which returns the first row of a statement as quickly as possible. Referred to as FIRST_ROWS.

 The CBO does *NOT* understand characteristics associated with an  application, nor can it fully understand the impact of complex  relationships between joined tables. It only has limited information  available to determine the 'best' plan.

 The CBO determines the best plan by calculating an estimated COST  for various execution plans and uses the plan with the lowest cost.  As this inherently involves assumptions about relative costs the  chosen plan may not necessarily be the true best plan. Such occurrences  are often reported to Oracle support as being "bugs" because the CBO  did not choose the best plan for a given scenario. One can generally  prove that given the input statistics available and the default 'cost'

        involved. The chosen plan was calculated to be the best plan, even if

        it was not.  However advanced the CBO becomes there will always be
        situations where the plan chosen is not optimal.  Hence you should
        always be prepared to 'tune' such statements.

 The RBO is no longer being enhanced. This means that certain execution  plans are ONLY available to the CBO. However, the RBO continues  to exist in Oracle 8.

The *full* story is in

104538.133

Also you could use event 10053 to verify how CBO is making it's sometimes stupid decisions.
The recent book of Jonathan Lewis shows basically how to use it, there is also a note on it at Metalink, but this one is very difficult to digest.

Hth,

Sybrand Bakker
Senior Oracle DBA Received on Fri Oct 12 2001 - 12:12:43 CDT