Oracle Partition Key Statistics & tuning Oracle Database Tips by Donald Burleson

Cost based optimizer statistics are critical to the performance of data warehouse queries and there are many publications that demonstrate that "partition level" statistics are required, especially for partition keys.  One DBA notes the issue clearly:

"The problem was one of poor performance for a query that joined two range partitioned tables (having different partitioning keys - in fact one is multicolumn range/list composite partitioned, the other single column range partitioned) via two dimension tables."

Missing partition key statistics are related to the way that the partitioned tables is analyzed with dbms_stats:

"The reason for that was our extensive use of the DBMS_STATS method option clause ?FOR ALL INDEXED COLUMNS SIZE 254″.

Because the column has a single value per partition we are not indexing it, therefore the column was not subject to statistical analysis, therefore the optimizer was making an incorrect assumption of the cardinality."

In response to this partition key statistics performance issue, This Oracle blogger has published a script for locating missing partition key statistics:

"The problem was one of poor performance for a query that joined two range partitioned tables (having different partitioning keys - in fact one is multicolumn range/list composite partitioned, the other single column range partitioned) via two dimension tables.  . .

The immediate sign of a problem was the extensive use of nested loop joins, and that led to the observation that the cardinality of the result set from one of the partitioned tables was low.  . .

When the predicates were manually rewritten against the fact table itself the estimated cardinality reduced even more - in fact it was now 10,000 times lower than the real cardinality, being 388 against a real value of nearly four million. . .

No statistics were stored at the partition level for the partition key column, and this was confirmed by querying USER_PART_COL_STATISTICS. The reason for that was our extensive use of the DBMS_STATS method option clause ?FOR ALL INDEXED COLUMNS SIZE 254″. Because the column has a single value per partition we are not indexing it, therefore the column was not subject to statistical analysis, therefore the optimizer was making an incorrect assumption of the cardinality."

select   substr(table_name,1,20)       partitioned_table,
         substr(column_name,1,20)      key_column,
         count(*)                      no_stats,
         count(*)/total_partitions*100 "NO_STAT%"
from     (
         select table_name,
                column_name,
                partition_name,
                low_value,
                count(*) over (partition by table_name, column_name)
                   total_partitions
         from   user_part_col_statistics
         where  (table_name,column_name) in
                (select name,column_name
                 from   user_part_key_columns
                 where  object_type = 'TABLE')
         )
where    low_value is null
group by table_name,
         column_name,
         total_partitions
/