CBO optimizer

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. Received on Thu Dec 02 2004 - 14:34:05 CST