Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Why does Optimizer relys on COST to produce the execution plan?

Re: Why does Optimizer relys on COST to produce the execution plan?

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: 2006-01-02 16:44:19
Message-id: 004f01c60fb3$68b9d690$c4bc21c8@porgand


Hi,

If CBO was perfect, it would always pick the plan with lowest cost and this would always be the fastest plan for us, despite stale statistics and bad SQL. But in reality, the best plan isn't always used because: 1) Statistics are stale (either segment/object level stats or system stats (including available PGA aggregate memory for sorting/hashing) 2) SQL is so bad/rigid that optimizer is unable to rewrite it better 3) Not properly configured optimizer parameters (mostly back in pre 9i days when had to play around with db_multiblock_read_cound and optimizer_index_* stuff)
4) Optimizer bugs/lack of features

So there's always a case when you're done everything, but optimizer is not (yet) able to cope with it...

Tanel.

> Hi Christian,
>
>>>> The model cannot rely on runtime statistics because the query has
> not run yet. This seam obvious. Therefore the model can only estimate
> the work that has to be done to execute the query.
>>
> You are absolutely right. It just skipped from my mind. Thank you.
>
> I will have a try once I enable the system level statistics on the
> database.
>
> On 1/2/06, Christian Antognini wrote:
>> Jaffar
>>
>> >I have not enabled the system level statistics.
>>
>> Then it's your fault ;-)

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 02 2006 - 16:44:19 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US