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

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.

• Original Message ----- From: "The Human Fly" To: "Christian Antognini" Cc: "oracle-l" Sent: Monday, January 02, 2006 6:17 AM Subject: Re: Why does Optimizer relys on COST to produce the execution plan?

> 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