Oracle Scratchpad

February 4, 2008

dbms_stats surprise

Filed under: CBO,Execution plans,Infrastructure,Performance,Statistics — Jonathan Lewis @ 5:47 pm GMT Feb 4,2008

It’s been several years since I started telling people that one of the solutions to dealing with optimizer problems was to adjust (or create) stored statistics using the dbms_stats procedures set_column_stats(), set_index_stats(), and set_table_stats().

Imagine my surprise to discover an old (2003) Metalink note confirming my claim that it is perfectly reasonable to take this approach.  If you have an account, you will find a demonstration script in note 157276.1. [Updated from time to time: currently the relevant doc id is 1276174.1, with 130899.1 as a little bonus]

There’s also a note (dated Nov 2002) which contains a tidy looking script for reporting object statistics at the object, partition, and subpartition level. I haven’t tested it yet, but you might find it useful. The note number is 31412.1


  1. Security by obscurity; I always thought Metalink was designed to squirrel away useful information by making it as hard to find as possible…

    Regards Nigel

    Comment by Nigel Thomas — February 4, 2008 @ 9:47 pm GMT Feb 4,2008 | Reply

  2. Thanks for pointer to valuable information.

    Comment by Virag Sharma — February 5, 2008 @ 3:05 am GMT Feb 5,2008 | Reply

  3. When the index with poor selectivity has a non-
    uniform data distribution, the cost of the FTS may be lower than the
    cost for the index range scan, still the performance using the index for
    values with small number of rows may be better.

    Jonathan, the note states the above. In such a case would not a histogram on that column help instead of setting the stats manually?

    Comment by Yas — February 5, 2008 @ 8:58 am GMT Feb 5,2008 | Reply

  4. Yas,

    That looks a reasonable observation based on the description given. However, in this case, I was interested only in the point that there is a Metalink note that adds a degree of “legality” to the use of the ‘set_xxxxxx_stats’ calls.

    Comment by Jonathan Lewis — February 5, 2008 @ 6:50 pm GMT Feb 5,2008 | Reply

  5. […] Lewis, on his Oracle Scratchpad, has a dbms_stats surprise. “It’s been several years since I started telling people that one of the solutions to […]

    Pingback by Log Buffer #83: a Carnival of the Vanities for DBAs — February 8, 2008 @ 9:36 pm GMT Feb 8,2008 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: