Re: CBO optimizer

"qwerty" <qwerty_at_yahoo.it> wrote in message news:<conuif$l3b$1_at_lacerta.tiscalinet.it>...
> Hi,
> i'm working with a 9.2 db server, on this server we have a small number of
> static tables (i.e. containing configuration values) and a large number of
> dynamic tables.
> Such dynamic tables may be very small (less than 100 of records) and some
> minutes later can be large (more than 100.000 of rercords) and than again
> can become small and so on....
> With this kind of tables, i found very difficult work with the CBO
> optimization, infact, if the analyze (using package dbms_stats) is running
> while the tables are empty, the CBO will use a Full scan table, obviously
> this is not good when the table are full of data (more than 100.000 of
> records).
>
> I know that oracle suggest to calculate statistics often and when is known
> that the data has changed a lot, but with my db, those statistcs take more
> than 1hour to run (calculating them only on the dynamic tables).. which is
> too much... I cannot wait such time before let the application work ...
>
> I'm facing with different solutions (i cannot estimate statistics when the
> application loads the table, because i cannot change its code):
> 1) estimate the statistics in order to reduce the time of execution and
> executing them very often, this means sometime the application perform well
> and sometime no.
> 2) compute statistcs only when the table is full of data, one time, and
> never (or rarely) recalculate them, this means that the application can
> perform well if the actual data is quite "similar" to the data on which the
> statistic has been calculated, this method should probably let a better
> solution.
>
> Are there other solutions to this problem???, can you give me suggestions
> ???
>
> Best regards
> M.I.

look up the "cardinality" hint.
http://asktom.oracle.com is a good start. Tom Kyte also covered this topic in a recent article in Oracle Magazine.

-bdbafh Received on Fri Dec 03 2004 - 12:35:21 CST