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.

About these ads

One Comment

  1. 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

One Trackback

  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 […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 290 other followers