Oracle Scratchpad

February 4, 2008

dbms_stats surprise

Filed under: CBO,Execution plans,Infrastructure,Performance,Statistics — Jonathan Lewis @ 5:47 pm BST 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 procedure 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.

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

5 Comments »

  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 BST Feb 4,2008 | Reply

  2. Thanks for pointer to valuable information.

    Comment by Virag Sharma — February 5, 2008 @ 3:05 am BST 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 BST 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 BST 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 BST Feb 8,2008 | Reply


RSS feed for comments on this post. TrackBack URI

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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,084 other followers