Fixed stats

Some time ago I had two questions about fixed objects statistics for which I couldn’t quickly find the answers. Questions are:

At the time of asking these question I’ve read CBO development team blog post on the topic, but still I was unable to answer them distinctly. Well, it appears the questions are simple and could probably be deduced after careful reading (first two questions for sure). The core phrase is:

The DBMS_STATS.GATHER_FIXED_OBJECTS_STATS procedure gathers the same statistics as DBMS_STATS.GATHER_TABLE_STATS except for the number of blocks

That’s the key, the statistics are the same, except for the number of blocks. Which effectively means, you can gather the statistics on the specific X$-table in the same way as you do with a normal table, by using DBMS_STATS.GATHER_TABLE_STATS procedure. The only difference would be you will not populate the number of blocks (but you can do this manually). Now, since these statistics are essentially the same as statistics for normal tables, it makes no sense to run the GATHER_FIXED_OBJECTS_STATS procedure on all RAC nodes. After all, the database is still the same and it doesn’t care about the table statistics in relation to RAC node (at least for now in 11g).

I wish I was that smart back then 🙂 I’ve found the answers recently via MOS notes updates:
In RAC, Should We Execute GATHER_FIXED_OBJECTS_STATS And Gather_dictionary_stats In Both Nodes? [ID 1479804.1] which gives the definitive answer “No” and
Why do some Fixed Tables not have Optimizer Statistics [ID 1355608.1] which explains that some fixed tables cannot have optimizer statistics by design decisions made by Oracle.
What I don’t understand after getting better idea of fixed stats is why Oracle made the choice to introduce a specific new procedure, without any parameters/configuration options if they could have better done it inside the standard procedure for statistics gathering? To me it looks very strange and I would prefer not to introduce one more complication to the already over-complicated statistics package.

This entry was written by Timur Akhmadeev, posted on February 25, 2013 at 23:42, filed under Oracle and tagged . Bookmark the permalink. Follow any comments here with the RSS feed for this post. Post a comment or leave a trackback: Trackback URL.

One Comment

  1. Sayan Malakshinov
    Posted February 26, 2013 at 00:31 | Permalink | Reply

    >> why Oracle made the choice to introduce a specific new procedure,
    >> without any parameters/configuration options if they could have
    >> better done it inside the standard procedure for statistics gathering?

    Maybe, they did so for compatibility with previous versions. Also as we can gather stats on ordinary tables in maintenance period or on a small loads(except gtt), but for fixed objects would be better do it on usual load, so perhaps a calling same procedures but with different parameters and a calling different procedures do not differ greatly

2 Trackbacks

  1. By v$lock | Oracle Scratchpad on May 2, 2013 at 23:03

    […] to Timur Akhmadeev who recently published a note pointing out that you could collect stats on individual X$ tables. Do make sure you test this on […]

  2. By Fixed Stats | Oracle Scratchpad on October 17, 2016 at 15:43

    […] option to gather fixed objects stats individually with a call to dbms_stats.gather_table_stats() is not commonly known, but it does […]

Leave a comment