Re: CBO optimizer

qwerty wrote:
> 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.
>
>

I might be wildly assuming things, but it sounds from your points 1 and 2 as if you are using 'analyze table computer/estimate statistics' commands.

Don't, if so. Use dbms_stats.gather_xxxx_stats (where "xxxx" is 'table', 'index', 'schema' or 'database'). For a start, you can parallelize the collection of statistics using this mechanism. And for seconds, you can use it in conjunction with table monitoring ('gather stale' or 'gather auto') to collect statistics only for those tables which Oracle deems actually need the fresh information collected.

If you *are* using dbms_stats already, then I'll apologise in advance for the assumption otherwise, and trust others can help.

Regards
HJR Received on Thu Dec 02 2004 - 15:02:21 CST