Re: Dbms_stats Analyze Data Dictionay ?

From: Mikito Harakiri <mikharakiri_at_ywho.com>
Date: Wed, 28 May 2003 11:57:37 -0700
Message-ID: <cL7Ba.14$we7.79@news.oracle.com>

"Brian Peasland" <oracle_dba_at_remove_spam.peasland.com> wrote in message news:3ED4FF0E.A82D6260_at_remove_spam.peasland.com...
> > 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.
>
> But X$ "tables" aren't part of the Data Dictionary. Rather, they are
> gateways into memory locations, etc. There are no indexes on X$ tables,
> etc. So what relevance does this really have when talking about stats
> against the Data Dictionary?

First, there are indexes on X$ tables. Second, X$ "tables" are part of the Data Dictionary, as Data dictionary views do contain X$ tables in their definition. The fact that X$ tables are in-memory structures doesn't matter.

Second, what is the purpose of analyzing data dictionary? To get queries against data dictionary views/tables to perform better, isn't it? Therefore, in order to get queries against dictionary views right, one must have statistics on all the tables involved, including X$ tables. Received on Wed May 28 2003 - 13:57:37 CDT