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

turn on monitoring - let oracle do it for you

you have no choice but to analyze the tables, no reason for the users to stop while it is going on

what command do you use for analysing - might be able to speed it up for u Received on Thu Dec 02 2004 - 14:54:11 CST