Re: Optimizer Mode

Keep in mind that the statistics are generic descriptions that don't necessarily change with the actual data. For example, if you have 30,000 current employees and every month you drop 1,000 and add 1,000, changes are that the percent of males and females remains about the same, and so is the distribution by department. Small percentage changes won't make a difference, neither will it matter if you have 35,000 rows instead of 30,000.

When you need to refresh stats is when the tables have significant changes such as increasing five times in size, as when you move from test to production, or when you do a very large load.

In other words, you should have a good idea of what tables really impact your database. Those are the ones you analyze and for those it is worth taking the time to learn more about them.

Richard

Ivan Boesing wrote:
>
> Hi Oracle gurus,
>
> I recently changed the optimizer mode of my database. I collect
> statistics with the analyze command for some tables (about 10). The
> optimizer works fine with this tables and statistics.
> Howewer, I have some doubts. Do I collect statistics for each table in
> database?? If so, how can I do it automatically (or I have do it manually,
> table per table). I should do this by a midnight script, but for every new
> table I would need to update the script..... By other hand, if
> statistics are necessary just for the most used tables I can collect it
> manually, but if the table change in size so frequently how the optimizer
> will work?
> Sumarizing, how can I proceed with statistics and cost based
> optimization method?
>
> Thanks in advance
>
> Ivan Boesing
> boesing_at_geocities.com
Received on Fri Aug 15 1997 - 00:00:00 CDT