Partitioning 12c

Most useful presentation of OOW so far, from Hermann Baer of Oracle on improvements in partitioning features in 12c – and there are lots of useful ones, including:

Online move of a partition – so easy to compress a partition when it has reached its final “read-only” state

multiple partition maintenance in one operation – e.g. merge 3 monthly partitions into one quarterly partition, or split one partition into 24 (think about “how do I partition a non-partitioned table”, and 12c has just made it easier and quicker – exchange it into an empty partitioned table, then do one massive split).

partial indexing – define which partitions of a partitioned table should be included in the indexes you create on the table – and the optimizer also knows that different partitions need different plans (an enhancement of “table expansion”.

interval-reference partitionining – you can create an interval partitioned table, and user ref-partitioning to create child (and further decendent) tables, and their partitions will automatically be generated, truncated, and dropped as the parent is extended, truncated or dropped (needs enabled foreign key constraints).

Lots more details – and lots of stress-testing to be done – but I’m off to hear “the optimizer lady” talk about hints.

Reminder:

Oracle has a “safe harbour” slide at the start of all presentations about future developments pointing out that the information presented is an indication of direction, but not guaranteed to make it into production.

Recursive SQL

A question came up on the OTN database forum a few days ago asking why a very simple query against a hash partitioned table was doing so much physical I/O to get a very small amount of data from a single partition:

I have table hash-partitioned on an indexed column “id” which is non-unique and part of my primary key. Inside each partition, rows with the same id are located close to each other which is done by dbms_redefinition reorg using orderby_cols. The intention is to reduce the amount of physical reads since there are no queries that don’t filter on the id column.

What I’m seeing is a lot of physical reads though. The first partition has roughly 80K rows, an average row length of 347, block size of 8K and compression … resulting in 821 blocks. And when (after flushing buffered cache and shared pool) submit a query that filters on “id” only and results is 106 selected rows, I see roughly 1400 physical reads.

The poster has clearly applied some careful thought to minimising the work required – so what’s gone wrong ? To help us analyse the problem he posted the results from running a query through autotrace:

Truncate Partition

A recent posting on the OTN database forum (which I didn’t answer correctly on my first attempt) raised the problem of truncating partitions when there are referential integrity constraints in place. Let’s start with a demonstration of the problem:

Table rebuilds

Here’s a question from the OTN forum that I’ve seen fairly frequently in the last couple of years:

I have a table abc(c_date date, c_id varchr2(20), c_name varchar2);
The table is already range partitoned on “c_date” column and I have lot of data in that table.
Now my task is to create the hash partions under range; hash partiton needs 16 partitons.
I dont want to create another table with range and hash partitons and migrate the data. The amount of data I have is 3 terabytes.

Indexing

A question about partitioning came up on OTN a few days ago – but the really interesting part of the thread was about the choice of indexes, and how the choice of partitioning, combined with the data patterns, could make a big difference to the choice of indexes. I think the comments I made about indexes are worth seeing, so I’ve linked to the thread.

Partitioned Bitmaps

The following question appeared in a comment to an earlier posting on multi-column bitmap indexes and the inability of Oracle to create a bitmap index join when (to the human eye) the strategy was an obvious choice.

I have a query which is using 2 indexes both are bitmap indexes (sizes are 37 and 24 Mbs) and table size is 17gb. While i ran the following query which can very well get the index itself, it takes around 6-8 minutes and using pga around 3 gb.

could you please explain me why ?

Interval Partitions

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

Prefixed

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 of the error it made could be dramatically reduced (in most cases) by sticking the partition key at the start of the index.

The guideline 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.

Upgrade issues

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) 10.2.0.5 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 10.2.0.4 to 11.2.0.2.

As part of his testing he had set the optimizer_features_enable parameter back to 10.2.0.4 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!)

Local Indexes – 2

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.

Local Indexes

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

List partitions

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:

Index Rebuild ?

While searching on Metalink for clues about an oddity relating to an index rebuild, I came across Bug 6767655  – reported in 10.2.0.3 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] [] []

DIAGNOSTIC ANALYSIS:
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).

Partition Stats

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:

• March 2007: Subpartitions and Optimizer Stats
• April 2008: (a) Subpartition Pruning
• April 2008: (b) Exchange Partition and Aggregated Stats
• Mar 2009: (a) Partition Oddities and 10.2.0.4 bug
• Mar 2009: (b) List partitions
• June 2009 Dynamic Sampling and partitioned tables
• Jan 2012 Incremental partition statistics

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.

• Gathering aggregated stats on partitioned objects in 10g
• Gathering aggregated stats on partitioned object in 11gR2

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)

CBO Surprise 2

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.