Re: Dbms_stats Analyze Data Dictionay ?

"Jack Silvey" <depifster_at_yahoo.com> wrote in message news:25c7944d.0305251049.4b6064fc_at_posting.google.com...
> If you analyze the DD, the optimizer will try to use cost based
> optimization in dictionary queries. This can be a disaster since the
> dictionary was designed long ago to run under rule. The CBO could
> start making very bad choices about execution plans and this can
> really slow down all your users (sort segment allocation, query
> parsing, space allocation, stats gathering, etc.)

Dictionary was never designed to run under certain kind of optimizer. Logical schema design is completely independent from optimizer algorithm finding a certain access path to the data. Plus, dictionary is evolving to accomodate new types of objects added, new X$ tables, etc, etc.

Given the complexity of certain dictionary views one can be very skeptical about CBO abilities. Here Wolfgan's "fallacies" paper comes in mind.

Missing statistics is no longer a problem in 9.2, since sampling would gather it dynamically. Sampling might even be superior to statically gathered statistics, because it can accomodate corellated single table predicates.

However, there is no statistics on X$ tables. Neither dbms_stats.gather_table_stats, nor sampling works with X$ tables in 9.2. Therefore, any query against dictionary view having X$ tables in view definition might have a goofy plan generated. Received on Wed May 28 2003 - 13:17:24 CDT