Sunday, March 8, 2009

Optimizer partition oddities, part 1: Severe bug in 10.2.0.4 patch set

Forward to part 2

It seems to be a bit like in the movie the "Groundhog Day": Although I've highlighted already twice an issue when using range-list subpartition pruning, it seems to be reoccurring and there are still a lot of oddities to tell about.

A recent case of a client showed that the subpartition pruning fix that has been incorporated into the 10.2.0.4 patch set is unfortunately quite buggy.

The test case used in my previous posts therefore represents only half of the truth.

Although when using explicit subpartition pruning by means of the "subpartition" clause 10.2.0.4 correctly applies the subpartition statistics (in contrast to 10.2.0.3 which uses the partition level statistics), it fails miserably when using "normal" partition pruning by specifying the partition keys.

Using the same test setup as in my previous post running this simple query in the different versions reveals some interesting results:


select data1 from partition_test where x_pkey = 1 and x_slice = '001';


This simply prunes to a single subpartition containing 10 rows which can be seen from the subpartition level statistics. You can refer to the previous posts for the setup and statistics details.

Here are the EXPLAIN PLAN results from the different versions which were taken from the 10053 trace files (explaining the slightly odd formatting).

11.1.0.7:


------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
------------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 2 | | | |
| 1 | PARTITION RANGE SINGLE | | 9 | 90 | 2 | 00:00:01 | 2 | 2 |
| 2 | PARTITION LIST SINGLE | | 9 | 90 | 2 | 00:00:01 | 2 | 2 |
| 3 | TABLE ACCESS FULL | PARTITION_TEST| 9 | 90 | 2 | 00:00:01 | 4 | 4 |
------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
3 - filter("X_PKEY"=1)


10.2.0.4:


------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
------------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 2 | | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 38 | 2 | 00:00:01 | 2 | 2 |
| 2 | PARTITION LIST SINGLE | | 1 | 38 | 2 | 00:00:01 | 2 | 2 |
| 3 | TABLE ACCESS FULL | PARTITION_TEST| 1 | 38 | 2 | 00:00:01 | 4 | 4 |
------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
3 - filter("X_PKEY"=1)


10.2.0.3:


------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
------------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 2 | | | |
| 1 | PARTITION RANGE SINGLE | | 10 | 100 | 2 | 00:00:01 | 2 | 2 |
| 2 | PARTITION LIST SINGLE | | 10 | 100 | 2 | 00:00:01 | 2 | 2 |
| 3 | TABLE ACCESS FULL | PARTITION_TEST| 10 | 100 | 2 | 00:00:01 | 4 | 4 |
------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
3 - filter("X_PKEY"=1)


So according to the EXPLAIN PLAN output 11.1.0.7 and 10.2.0.3 get it right, but 10.2.0.4 reports an incorrect cardinality.

Checking the 10053 optimizer trace file reveals some interesting details:

11.1.0.7:


BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: PARTITION_TEST Alias: PARTITION_TEST (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 10 #Blks: 1 AvgRowLen: 10.00
SUBPARTITIONS::
PRUNED: 1
ANALYZED: 1 UNANALYZED: 0
Partition [3]
#Rows: 10 #Blks: 1 AvgRowLen: 10.00
#Rows: 10 #Blks: 1 AvgRowLen: 10.00
Access path analysis for PARTITION_TEST
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for PARTITION_TEST[PARTITION_TEST]
Column (#2):
NewDensity:0.004950, OldDensity:0.000495 BktCnt:1010, PopBktCnt:1010, PopValCnt:2, NDV:2

Table: PARTITION_TEST Alias: PARTITION_TEST
Card: Original: 10.000000 Rounded: 9 Computed: 9.03 Non Adjusted: 9.03
Access Path: TableScan
Cost: 2.00 Resp: 2.00 Degree: 0
Cost_io: 2.00 Cost_cpu: 8055
Resp_io: 2.00 Resp_cpu: 8055
Best:: AccessPath: TableScan
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 9.03 Bytes: 0

***************************************


11.1.0.7 correctly identifies the subpartition (10 rows, 1 block). What seems to be odd that it reports for the column #2 (X_SLICE) apparently partition level statistics (1010 BktCnt, Number of Distinct Values: 2), but it doesn't seem to influence the cardinality estimate significantly. When removing any histograms on subpartition level the cardinality estimate is correctly shown as 10.

10.2.0.4:


BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: PARTITION_TEST Alias: PARTITION_TEST (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 0 #Blks: 1 AvgRowLen: 0.00
SUBPARTITIONS::
PRUNED: 1
ANALYZED: 1 UNANALYZED: 0
Partition [2] (NOT ANALYZED)
#Rows: 0 #Blks: 58593632 AvgRowLen: 1025.00
#Rows: 0 #Blks: 1 AvgRowLen: 0.00
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#1): X_PKEY(NUMBER) Part#: 2
AvgLen: 22.00 NDV: 0 Nulls: 0 Density: 0.0000e+000 Min: 0 Max: 0
Column (#1): X_PKEY(NUMBER)
AvgLen: 22.00 NDV: 0 Nulls: 0 Density: 0.0000e+000 Min: 0 Max: 0
Column (#2): X_SLICE(VARCHAR2) Part#: 2
AvgLen: 20.00 NDV: 0 Nulls: 0 Density: 0.0000e+000 Min: 0 Max: 0
Column (#2): X_SLICE(VARCHAR2)
AvgLen: 20.00 NDV: 0 Nulls: 0 Density: 0.0000e+000 Min: 0 Max: 0
Table: PARTITION_TEST Alias: PARTITION_TEST
Card: Original: 0 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 2.00 Resp: 2.00 Degree: 0
Cost_io: 2.00 Cost_cpu: 7121
Resp_io: 2.00 Resp_cpu: 7121
Best:: AccessPath: TableScan
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 0.00 Bytes: 0
***************************************


This looks like a mess, and it is indeed. You'll get always an estimated cardinality of 0 (sanity checked to 1) in the 10.2.0.4 patch set if you prune to a single subpartition. This will screw up almost any plan and hence potentially show abysmal performance.

The issue is tracked with bug Bug 7210921 - STATISTICS WITH SUBPARTITIONS ARE NOT CORRECTLY INTERPRETED IN EXPLAIN PLANS and Bug 7264383 - WRONG STATISTICS 0 ROWS FOR QUERY PRUNED TO SUBPARTITION ON 10.2.0.4. More information can be found in Metalink Note 728056.1. A patch for bug 7210921 can be requested and is already available for a couple of platforms.

10.2.0.3:


BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: PARTITION_TEST Alias: PARTITION_TEST (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 1010 #Blks: 4 AvgRowLen: 10.00
SUBPARTITIONS::
PRUNED: 1
ANALYZED: 1 UNANALYZED: 0
Partition [1]
#Rows: 1010 #Blks: 4 AvgRowLen: 10.00
#Rows: 1010 #Blks: 1 AvgRowLen: 10.00
***************************************
SINGLE TABLE ACCESS PATH
-----------------------------------------
BEGIN Single Table Cardinality Estimation
-----------------------------------------
Column (#1): X_PKEY(NUMBER) Part#: 1
AvgLen: 3.00 NDV: 1 Nulls: 0 Density: 4.9505e-004 Min: 1 Max: 1
Histogram: Freq #Bkts: 1 UncompBkts: 1010 EndPtVals: 1
Column (#1): X_PKEY(NUMBER)
AvgLen: 3.00 NDV: 1 Nulls: 0 Density: 4.9505e-004 Min: 1 Max: 1
Histogram: Freq #Bkts: 1 UncompBkts: 1010 EndPtVals: 1
Column (#2): X_SLICE(VARCHAR2) Part#: 1
AvgLen: 4.00 NDV: 2 Nulls: 0 Density: 4.9505e-004
Histogram: Freq #Bkts: 2 UncompBkts: 1010 EndPtVals: 2
Column (#2): X_SLICE(VARCHAR2)
AvgLen: 4.00 NDV: 2 Nulls: 0 Density: 4.9505e-004
Histogram: Freq #Bkts: 2 UncompBkts: 1010 EndPtVals: 2
Table: PARTITION_TEST Alias: PARTITION_TEST
Card: Original: 1010 Rounded: 10 Computed: 10.00 Non Adjusted: 10.00
-----------------------------------------
END Single Table Cardinality Estimation
-----------------------------------------
Access Path: TableScan
Cost: 2.01 Resp: 2.01 Degree: 0
Cost_io: 2.00 Cost_cpu: 63081
Resp_io: 2.00 Resp_cpu: 63081
Best:: AccessPath: TableScan
Cost: 2.01 Degree: 1 Resp: 2.01 Card: 10.00 Bytes: 0
***************************************


The 10.2.0.3 output reveals that it uses the partition level statistics and only gets it right in this particular case due to an frequency histogram on X_SLICE on the partition level. If you remove this histogram the cardinality estimate will be wrong (505).

So if you're using range-list subpartitioning with 10.2.0.4 and use to prune to a single subpartition you should either use 10.2.0.3 optimizer features using the OPTIMIZER_FEATURES_ENABLE parameter/hint to avoid the mess shown above - though 10.2.0.3 can only use partition level statistics - or get the patch mentioned installed.

In the next part of this series I'm going to show some further oddities when using list partitioning. These apply also to 11gR1.