I’ve been working on a project with a very large subpartitioned table and I’ve been trying to come up with a sensible statistics generation strategy. This application is on a Linux based RAC system currently running 10g R2 (10.2.0.3). I’ve been trying to use every source of information available and of course, many 10053 traces. I came across this little blurb on Jonathon Lewis’ web site:

Sub-Partition stats (12^th June)

Page 39, Second paragraph:

The problem of partitions and table-level statistics echoes on down the chain to subpartitions. If you want to query exactly one subpartition of one partition, then the optimizer uses the statistics for that one subpartition.

This is wrong. It looks as if Oracle hardly uses sub-partition statistics at all. I have a test case on 10.2.0.1 which shows the optimizer using the partition level statistics to calculate the cardinality of a very simple query that has been identified as targeting exactly one sub-partition of one partition.  However, the access path is a full table scan of that sub-partition, and the cost of the scan is clearly derived from the number of blocks in the sub-partition.

Thanks to Adrian Billington for bringing this to my attention.

I must say that this mirrors what I’ve found. For the queries that I’ve been modeling, the 10053 traces lead me to believe that the optimizer only considers the row and block counts for the pruned subpartitions in its calculations. All other information, including histogram statistics, all appear to be taken from the partition level only.   

The following excerpt from a 10053 trace is the only place that I’ve found where the optimizer considers the subpartition statistics:

Table Stats::
Table: mytab Alias: mytab (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 286200000 #Blks: 1598304 AvgRowLen: 82.00
SUBPARTITIONS::
PRUNED: 1
ANALYZED: 1 UNANALYZED: 0
Partition [5]
#Rows: 286200000 #Blks: 1598304 AvgRowLen: 82.00
#Rows: 25004700 #Blks: 135309 AvgRowLen: 82.00 <== This is the only place that reflects subpartition stats.

This entry was posted on Sunday, February 3rd, 2008 at 3:15 pm and is filed under Oracle DBA. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.