This is a short blog post about another query transformation (check my first blog post [Oracle] DB Optimizer Part VIII - Looking under the hood of query transformation (done by CBO) with simple real life example first, if you never heard of the term "query transformations"), that fixes a cardinality estimation issue with multiple (OR) predicates that cover the whole data range. I have to admit that this is a special case, but it can happen as observed by my client
One of my clients recently upgraded his Oracle database from 10.2.0.5 to 184.108.40.206 for testing / verification purpose and noticed an execution plan change for one of the most executed and critical queries due to a change of the cardinality estimates. The query itself is generated by an application framework and so we don't have much influence on it at all, but basically said the generated query includes several predicates on the same column, which cover the whole data range in consequence.
I remembered about a sub topic called "Problems with Multiple Predicates" (page 58) in book "Cost-Based Oracle Fundamentals" by Jonathan Lewis (check reference section for more details) about such an issue with Oracle 10g and was able to demonstrate the new CBO behavior on Oracle 12c with that book example as well. You can download the whole test case script "birth_month_01.sql" by Jonathan Lewis from Github. So let's dig into it right away.
The CBO / cardinality calculation issue
Create base table
SQL> create table AUDIENCE as select trunc(dbms_random.value(1,13))month_no from all_objects
where rownum <= 1200;
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'AUDIENCE');
We created a table called AUDIENCE with 1.200 rows in sum and a column called MONTH_NO with 12 different values.
The exemplary SQL
SQL> select /*+ GATHER_PLAN_STATISTICS */ count(*) from AUDIENCE
where MONTH_NO > 8 OR MONTH_NO <= 8;
If you look closely at the two predicates, you may notice that there is no value restriction at all (ignoring NULL values) in "real life". So in general the generated predicates are not needed at all, but as we have no influence on the SQL generation framework we have to deal with that. So let's verify the optimizer behavior with Oracle 10.2.0.5 at first.
Oracle 10g (10.2.0.5)
Look closely at the wrong CBO cardinality estimate of 986 rows and the corresponding predicate information "filter(("MONTH_NO"<=8 OR "MONTH_NO">8))". How does the CBO calculate the (wrong) cardinality in that case on Oracle 10.2.0.5? I am just quoting from book "Cost-Based Oracle Fundamentals" as Jonathan Lewis described it 100 times better than i ever can do.
month_no > 8: This is an unbounded (no limit at one end), open (8 is excluded) range.
- Selectivity = (high_value – limit) / (high_value – low_value) = (12 – 8) / (12 – 1) = 4 / 11
month_no <= 8: This is an unbounded, closed (includes 8) range, so adjust for closure.
- Selectivity = (limit – low_value) / (high_value – low_value) + 1 / num_distinct = (8 – 1) / (12 – 1) + 1 / 12 ) = 7 / 11 + 1 / 12
Let’s dissect the where clause:
month_no > 8 -- (predicate 1)
or month_no <= 8 -- (predicate 2)
- From our single selectivity formula, (required range / total range), we calculate the selectivity for predicate1, month_no > 8, as (12 – 8) / (12 – 1) = 4 / 11 = 0.363636
- Similarly, the selectivity for predicate2, month_no <= 8, is (8 – 1) / (12 – 1) + 1/12 = 7/11 + 1/12 = 0.719696
- Our formula for selectivity(P1 OR P2) is selectivity(P1) + selectivity(P2) – selectivity(P1 AND P2), so the combined selectivity is 0.363636 + 0.719696 – (0.363636 * 0.719696) = 0.8216
- Multiply this selectivity by the 1,200 rows in the table, round up, and you get 986. It’s clearly the wrong answer for reasons that are intuitively obvious to the human mind. But the machine is not human—it follows the code, it doesn’t understand the situation.
So that's the way how it was working the whole time with Oracle 10.2.0.5, but obviously it seems like something has changed with Oracle 220.127.116.11 in the client environment. So let's take a look at the new one.
Oracle 12c (18.104.22.168)
Look closely at the predicate information. The filter predicates on column MONTH_NO has been gone and replaced by predicate "MONTH_NO IS NOT NULL". The CBO calculates the correct cardinality (1.200 rows) with help of that transformation right now.
By the way this query transformation possibility also exists since Oracle 11g (or at least since 22.214.171.124.6 as i have no older version right here for verification). Here is the example on Oracle 11g in addition.
Oracle 11g (126.96.36.199.6)
If you have any further questions - please feel free to ask or get in contact directly, if you need assistance by troubleshooting Oracle database (performance) issues.