Re: Migrating to cost based optimizer

Some comments in-line

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1159118827.257623_at_bubbleator.drizzle.com...
> hasta_l3_at_hotmail.com wrote:
>>
>> 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.

    3rd Party applications are always difficult - for the     supplier and the customer. The same software package     may be used by customers with dramatically different     data sets - and this can make a big difference to both     the CBO and the RBO in terms of what is a good plan.

    You probably want to collect real stats from a small SET     of representative customers, classified along the lines of     (for example):

        Large organisation, many operators, small number of
        orders per customer (of your client)

        Small organisation, few operators, large number of
        orders per customer (of your client)

        and so on.

    This gives you the best chance of working out where     your application has problems with your clients' data     sizes. It also allows you to work out better indexing     strategies for each class of customer.

>
>> 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) ?
>>

    One of your tasks as a supplier is to understand

1. the scale of the customer's business
2. the relevance this has to your data model

    so that you can choose the best 'canonical' set and indexing     strategy to get them going.

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

    I think it's a good bet that there will always be a few oddities,     but if you (or your client) starts with a legacy data load and     do proper UAT, then the anomalies and places where the     stats need to be adjusted should show up early. But it does     help to have an experienced reviewer checking what's going     on and predicthing threats.

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

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html