When dealing with partitioned tables the Optimizer relies on both the statistics for the entire table (global statistics) as well as the statistics for the individual partitions (partition statistics) to select a good execution plan for a SQL statement. If the query needs to access only a single partition, the Optimizer uses only the statistics of the accessed partition. If the query access more than one partition, it uses a combination of global and partition statistics.
It is very common with range partitioned tables to have a new partition added to an existing table, and rows inserted into just that partition. If end-users start to query the newly inserted data before statistics have been gathered, it is possible to get a suboptimal execution plan due to stale statistics. One of the most common cases occurs when the value supplied in a where clause predicate is outside the domain of values represented by the [minimum, maximum] column statistics. This is known as an „out-of-range‟ error. In this case, the Optimizer prorates the selectivity based on the distance between the predicate value, and the maximum value (assuming the value is higher than the max), that is, the farther the value is from the maximum or minimum value, the lower the selectivity will be.
The "Out of Range" condition can be prevented by using the DBMS_STATS.COPY_TABLE_STATS procedure (available from Oracle Database 10.2.0.4 onwards). This procedure copies the statistics of a representative source [sub] partition to the newly created and empty destination [sub] partition. It also copies the statistics of the dependent objects: columns, local (partitioned) indexes, etc. The minimum and maximum values of the partitioning column are adjusted as follows;
It can also scale the statistics (such as the number of blocks, or number of rows) based on the given scale_factor. The following command copies the statistics from SALES_Q3_2011 range partition to the SALES_Q4_2011 partition of the SALES table and scales the basic statistics by a factor of 2.
Speaking At DOAG 2018 Conference And IT Tage 2018 - I will be speaking at the yearly DOAG conference in December as well as at the IT Tage in November. My talk will be "Oracle Optimizer System Statistics Upd...
Acum o zi