Re: Migrating to cost based optimizer

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 24 Sep 2006 10:27:07 -0700
Message-ID: <1159118827.257623@bubbleator.drizzle.com>

hasta_l3_at_hotmail.com wrote:

>> hasta_l3_at_hotmail.com wrote:
>>
>>> BTW, we have exactly zero experience with CBO :-)
>> Then let me make a recommendation.
>>
>> Spend a lot of time learning how DBMS_STATS works.
>> Be sure you check out the collection of system statistics,
>> dictionary statistics, and how to fool Oracle into believing
>> things about objects that are not true, today, but may well
>> be true, tomorrow.

>
> Thanks, Daniel.
>
> I suppose it would make sense to collect the statistics of
> representative customer, and fool the database we use when developping
> with these stats. Is it right ?

To quote (or paraphrase) Jonathan Lewis ... the better the quality of information the CBO has the better choices it will (generally) make.

> Let's call them the canonical stats.
>
> Which strategy would you recommand for new customers, whose database is
> initially empty (and who dont have a dba available on site) ?
>
> - Automatically collect the local customer statistics periodically ?

That's the one recommended by Oracle.

> - Start with the canonical stats, then switch to an automatic
> collection when the site has reached a data size treshold (to be
> defined)
>
> - Never compute stats automatically. Keep the canonical stats till
> there is a problem.
>
> - Anything else ?

Day one the canonical stats may well send Oracle generating plans that are harmful. You need to test it both ways and choose the one that works the best for your application with your data.

My instinct is always to hire qualified DBAs and make running StatsPack or AWR frequently part of their job requirement.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

Received on Sun Sep 24 2006 - 12:27:07 CDT