Gathering Aggregated Cost-Based Optimiser Statistics on Partitioned Objects

Recently, I have been looking into how to gather cost-based optimizer statistics on composite partitioned objects.  Collecting global statistics on a large partitioned object can be a time-consuming and resource intensive business as Oracle samples all the physical partitions or sub-partitions. Briefly, if you do not collect global statistics on a partitioned table, Oracle will aggegrate the statistics on the physical partitons or sub-partitions to calculate statistics on the logical table and partition segments.

Oracle 10g makes a number of mistakes in its calculation of these aggregated statistics.  In particular, the number of distinct values on columns by which the table is partitioned have impossibly low values.  This is can affect cardinality calculations and so lead the optimizer to choose the wrong execution plan.

I have now published the second of two documents on my website that examine aspects of statistics on partitioned, and in particular composite partitioned tables. The first document examines the problems in 10g, and proposes a procedure to 'correct' the aggregated statistics to at least minimum possible values.

• Gathering Aggregated Cost-Based Optimiser Statistics on Partitioned Objects in Oracle 10g 

The second document looks at the same issue in 11g and shows that while most of the issues are fixed, one problem remains.

• Gathering Aggregated Cost-Based Optimiser Statistics on Partitioned Objects in Oracle 11gR2