If your data model forsees storing “current” and “historical” rows in the same table, there is a chance that there will be queries doing self-joins drived by the “current” rows. This model can give rise to wrong join cardinality estimates applied on skewed data distributions. The more emphasized the skew is, the larger is the error. Table partitioning turns out to be a good solution in such cases. Continue Reading
This blog post describes how the calculation of new density has modified with the introduction of hybrid histograms in Oracle 12c. Continue Reading
In 12c the parameter options was introduced in gather_table_stats. Unfortunately, it doesn’t work as expected. Continue Reading
Disjunctive subqueries are the subqueries which are applied to the or operator. Alternatively, these queries can be rewritten to use the union all operator which may be a more efficient way to retrieve the records from the database. This blog post compares the efficiency of SQL Server and Oracle optimizer when dealing with the transformations of disjunctive subqueries.
Continue Reading
Excessive query parsing time with extended events and long in lists were observed on a 12c database. As a workaround set the undocumented parameter _optimizer_extended_stats_usage_control to 224. Continue Reading
The purpose of this post is to warn of an edge case where the cardinality estimate might significantly increase after adding a conjunctive filter predicate. This problem might arise in Oracle 12c due to sql plan directives on partitioned tables when there is a value which is rare in one partition, but appears frequently in other partitions. Continue Reading
Temp temporary transformation is the operation in the execution plan where Oracle stores the subquery results in a temporary table. Cardinality estimates can get different if the temp table transformation is used in Oracle 12c. The purpose of this post is to explain why this happens. Continue Reading