Oracle Scratchpad

March 17, 2010

Partition Stats

Filed under: Oracle,Partitioning,Statistics — Jonathan Lewis @ 7:44 am BST Mar 17,2010

It’s possible to spend ages talking about the best ways of collecting, or creating, statistics on partitioned tables.

The possible strategies for maintaining partitioned tables, (exchange partition, split partition, drop partition etc.) the types of partitioning available, and the way that the optimizer plays with the stats as you do so, have kept changing over the years, and I’ve got a large set of examples designed to test what happens to the stats as you do things to the table – but it’s impossible to keep it up to date.

Doug Burns is writing a series of articles about the trials, tribulations, and successes about partitioned tables and statistics. The series was well worth reading and will give you an insight into the problems you may have to address, so I’ve produced a catalogue to make it easy to visit the individual chapters in order.  Make sure you also read the comments and related links.

  • Part 1 – In which we see a simple example and do a default stats collection
  • Part 2 – In which we consider Global Stats
  • Part 3 – In which subpartitions and aggregation cause problems
  • Part 4 – In which our hero fights his way through stats aggregation woes
  • Part 5 - In which we encounter a partition exchange
  • Part 6a – In which we start to use dbms_stats.copy_table_stats()
  • Part 6b – In which we see how reputable individuals handle their mistakes
  • Part 6c – In which we hear about 10.2.0.5 and lots of bugs
  • Part 6d – In which we revisit earlier errors and discuss the benefit of discussion
  • Part 6e – In which we revisit earlier problem again and talk about a bug.
  • Part 7 – Not in the original series, but an interesting (slow) experience in 11g

I thought I’d collate a few other items on partition stats and optimizer behaviour – mainly from Randolf Geist’s blog:

And one from Kerry Osbourne – which lists a new granularity option, and a patch for 10.2.0.4

  • Feb 2009: Maintaining statistics on a large partitioned table.  (See also Metalink Doc ID: 6526370.8)

A couple (as pdf files) from David Kurtz, with a particular view to optimising Peoplesoft.

And an investigation into an oddity with the optimizer when using partitioned indexes

  • Feb 2011: Jokes of the CBO with local indexes (10.2.0.4, 11.1.0.7)

4 Comments »

  1. [...] A key topic of the discussion was related to Oracle statistics and the CBO. It was timely that the Oracle Scratchpad’s Jonathan Lewis posted a series created by Doug Burns all about stats. [...]

    Pingback by Log Buffer #183, a Carnival of the Vanities for DBAs | The Pythian Blog — March 19, 2010 @ 8:08 pm BST Mar 19,2010 | Reply

  2. Statistics on Partitioned Tables – Contents…

    When Jonathan Lewis decided it was time to post a list of the Partition Stats posts on his blog and Noons suggested I made them easier to track down, I listened. So this post will link to the others and, at least in the short term, I’ve also included …

    Trackback by Doug's Oracle Blog — March 28, 2010 @ 11:05 pm BST Mar 28,2010 | Reply

  3. [...] Jonathan Lewis decided it was time to post a list of the Partition Stats posts on his blog and Noons suggested I made them easier to track down, I listened. So this post will [...]

    Pingback by Statistics on Partitioned Tables – All Things Oracle — August 1, 2012 @ 5:10 pm BST Aug 1,2012 | Reply

  4. [...] and manipulating stats the best reference I have is still a set of articles by Doug Burns, which I’ve collated here with a few from other [...]

    Pingback by Usage Stats « Oracle Scratchpad — January 24, 2013 @ 7:00 pm BST Jan 24,2013 | 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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,985 other followers