Oracle Scratchpad

May 26, 2011

Interval Partitions

Filed under: Infrastructure,Oracle,Partitioning — Jonathan Lewis @ 12:07 pm BST May 26,2011

Here’s a little gem from the OTN database forum – why should the CPU usage of querying an “interval partitioned” table depend on the size of something that’s missing ?

(The most significant symptom is identified in a reply I wrote to the thread – but that’s only a step in the right direction).

February 28, 2011


Filed under: Indexing,Partitioning — Jonathan Lewis @ 6:12 pm BST Feb 28,2011

From time to time the question about whether local indexes on partitioned tables should be prefixed or non-prefixed appears on the Oracle forums and mailing lists.

It’s possible that I’m wrong – although no-one has come up with a counter-example to the statement I keep repeating – but the whole prefixed/non-prefixed thing for local indexes dates back to a limitation in the optimizer somewhere in the 8.0 time line where Oracle couldn’t do partition elimination on indexes properly but the overhead due to this limitation could be dramatically reduced (in most cases) by sticking the partition key at the start of the index.

The guidelines for local indexes are the same as they would be for a non-partitioned index on a non-partitioned heap table – the partitioning column(s) are just columns in the table that might be worth including in the index, and the order of the index columns is dictated by the important queries that have to access the table.

For further comments, there’s a note I wrote (which I’ve just been reminded of) on the OTN database forum that adds a little detail to this argument.

February 3, 2011

Upgrade issues

Filed under: CBO,Oracle,Partitioning,Troubleshooting,Upgrades — Jonathan Lewis @ 6:41 pm BST Feb 3,2011

Here’s an example of how a bug-fix can create problems. It’s a code change in 11.2.x.x and (I believe) relating to the costing of queries involving (but perhaps not restricted to) composite partitioned tables. I first saw this change in an email from Doug Burns, who sent me the 10053 traces from a very simple query that had started using the wrong index after an upgrade from to

As part of his testing he had set the optimizer_features_enable parameter back to and found that not only did the choice of index change back to the expected index, but the costs of the two indexes changed dramatically. (The cost of using the “right” index changed from 15 to something in excess of 9,000 on the upgrade!)


November 14, 2010

Local Indexes – 2

Filed under: CBO,Partitioning,Performance — Jonathan Lewis @ 5:42 pm BST Nov 14,2010

In the previous note on local indexes I raised a couple of questions about the problems of different partitions holding different volumes of data, and supplied a script to build some sample data that produced the following values for blevel across the partitions of a list-partitioned table.


November 11, 2010

Local Indexes

Filed under: CBO,Infrastructure,Partitioning — Jonathan Lewis @ 10:45 pm BST Nov 11,2010

In a recent article about list partitioned tables I raised some questions about the cases where the optimizer can’t (yet) do partitioning pruning even when the opportunity is clearly visible to the human eye. The most important example was the case where each partition was defined to hold rows for just one partition key value – but the optimizer could not prune out the redundant partition for a query like: “partition_key != {constant}”.

I recently came across a situation where this really made a big difference. The system had a huge table that had been list partitioned as follows (with some camouflage):


November 3, 2010

List partitions

Filed under: Partitioning,Tuning — Jonathan Lewis @ 7:01 pm BST Nov 3,2010

Despite the title and content, this article is more about thinking and testing than it is about list partitions as such. It’s just based on a little thought experiment with list partitioning.

I have a list-partitioned table, with no indexes, defined as follows:


April 19, 2010

Index Rebuild ?

Filed under: Index Rebuilds,Indexing,Partitioning,Troubleshooting — Jonathan Lewis @ 7:13 pm BST Apr 19,2010

While searching on Metalink for clues about an oddity relating to an index rebuild, I came across Bug 6767655  – reported in and fixed in 11.2.  The problem is described as follows:

When having two sessions running concurrently, one doing inserts to a partitioned table, and the other doing partition maintenance operations, there is a chance of the DML session core dumping in kauxs_do_journal(),  i.e.:

ORA-7445: exception encountered: core dump [kauxs_do_journal()+21] [SIGSEGV]  [Address not mapped to object] [0x2] [] []

The problem seems to be a timing issue – or at least there is a timing component involved. When doing the partition split, an ORA-54 is normally raised, however by running the split in a loop, it will eventually succeed (and thus causing problems for the session running the DML).


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

  • 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 (,

February 24, 2010

CBO Surprise 2

Filed under: CBO,Execution plans,Partitioning — Jonathan Lewis @ 7:45 pm BST Feb 24,2010

In an earlier article I gave a description of how splitting a single date ranges into a pair of date ranges with an OR would change the arithmetic and so run the risk (or introduce the benefit) of changing the execution plan.

At the time I made a couple of comments about other details that could be demonstrated by the same query – but postponed saying anything about them. This follow-up article addresses the omission.


December 17, 2009

Partition Indexing

Filed under: CBO,Indexing,Infrastructure,Partitioning — Jonathan Lewis @ 9:01 pm BST Dec 17,2009

From time to time I get asked if it’s possible to index a partitioned table so that recent partitions have different (local) indexes from older partitions. The answer is “not really, but there’s a couple of dirty tricks which aren’t very nice and aren’t very stable“. (You can always play around – dangerously – with unusable indexes or function-based indexes).

With Oracle 11.2 there’s a new optimizer feature called “table expansion” which I’m guessing has been created to address this issue. Christian Antognini introduces it in this posting – which is actually starts by talking about zero-sized segments and unusable indexes.

November 25, 2009


Filed under: Oracle,Partitioning — Jonathan Lewis @ 7:10 pm BST Nov 25,2009

In one of those little coincidences that seem to happen so often a question came up on the server news group that has an answer in a blog note I’d written just a few of days earlier . The question was simply asking for a way of counting the number of rows in each partition of a partitioned table.


November 21, 2009

ora_hash function

Filed under: Oracle,Partitioning,Performance,Troubleshooting — Jonathan Lewis @ 10:59 am BST Nov 21,2009

A few weeks ago I wrote a note demonstrating the way in which Oracle’s strategy for hash partitioning is engineered to give an even data distribution when the number of partitions is a power of two. In one of the comments, Christo Kutrovsky pre-empted my planned follow-up by mentioning the hashing function ora_hash() that appeared in 10g.


September 21, 2009

Hash Partitions

Filed under: Infrastructure,Partitioning,Performance — Jonathan Lewis @ 5:57 pm BST Sep 21,2009

I made a throwaway comment in a recent posting about using powers of two for the number of partitions when using hash partitioning. The article in question was talking about globally partitioned indexes, but the “power of 2” principle was first associated with tables.

Here’s a simple demonstration of hash partitioning in action demonstrating why Oracle adopted this “power of 2” rule. We start by creating a table that doesn’t obey the rule – with six partitions – and collect stats on it to see how many rows go into each partition:

December 19, 2008


Filed under: Indexing,Infrastructure,Partitioning,Performance — Jonathan Lewis @ 10:22 pm BST Dec 19,2008

I wrote a short reply this morning to a question from someone on the OTN forum who wanted a little help with indexing strategies after partitioning one of his larger tables.

The comments I made there capture the key points you need to consider when comparing the costs and benefits of locally partitioned indexes with those of global, or globally partitioned indexes – so I thought I’d post a pointer to the thread.

August 20, 2007

Intelligent Stats

Filed under: CBO,Partitioning,trace files,Troubleshooting — Jonathan Lewis @ 5:43 pm BST Aug 20,2007

Experiences like this one [Ed: Nov 2008 – the blog has become private since I wrote this note]are always worth reading about to remind yourself what you can do with the dbms_stats package when it’s really necessary.

And while I’m pointing to other URLs, here’s another one worth knowing about – event “Cursor: pin S wait on X”.  It’s not surprising to see this wait event occasionally in a busy 10g system, but if you’re losing a significant amount of time, it could be a bug.

« Previous PageNext Page »

Powered by