Posted by With every release of Oracle, more and more power comes to the optimizer. Many of these are new features (such as adaptive cursor sharing, adaptive optimization, dynamic sampling, etc)…but also within the “core” of the optimizer, there are continuing efforts to transform and interrogate SQL’s to try derive a ‘smarter’ query and hence hopefully a smarter plan.
Its always a balancing act…how much can you re-jig a query without running the risk of actually changing what the query does…
Here’s an example of where that balance is slightly wrong in 12c
SQL> drop table T;
Table dropped.
SQL> create table T ( seq number primary key, x number, y number , u number);
Table created.
SQL> declare
2 s number := 1;
3 begin
4 for i in 1 .. 9 loop
5 for j in i+1 .. 10 loop
6 insert into T values (s,i,j, 0);
7 s := s + 1;
8 end loop;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
So we’ve put 45 rows into the table, the values of which are not particularly important. We are going to compare these rows with those in another table
SQL> drop table F;
Table dropped.
SQL> create table F ( g number, n number, x number, y number );
Table created.
SQL> insert into F values (1,1,3,4);
1 row created.
SQL> insert into F values (1,1,5,7);
1 row created.
SQL> insert into F values (1,1,7,8);
1 row created.
SQL> select * from F;
G N X Y
---------- ---------- ---------- ----------
1 1 3 4
1 1 5 7
1 1 7 8
So lets look at all the possible values in F in either X or Y columns.
SQL> select x from F where g = 1 union all select y from F where g = 1;
X
----------
3
5
7
4
7
8
6 rows selected.
So before we bring T and F into a query, lets use those values above to find all the rows in T for which X and Y both so not match that list
SQL> select seq,x,y
2 from T
3 where u = 0
4 and x not in ( 3,5,7,4,7,8)
5 and y not in ( 3,5,7,4,7,8);
SEQ X Y
---------- ---------- ----------
1 1 2
5 1 6
8 1 9
9 1 10
13 2 6
16 2 9
17 2 10
38 6 9
39 6 10
45 9 10
10 rows selected.
So at this point – you can see that all appears well. Now… I replace the: and x not in ( 3,5,7,4,7,8) and y not in ( 3,5,7,4,7,8); with and x not in ( select x from F where g = 1 union all select y from F where g = 1) and y not in ( select x from F where g = 1 union all select y from F where g = 1); which should yield the same result, because as we saw earlier, “select x from F where g = 1 union all select y from F where g = 1” yields “3,5,7,4,7,8” But we dont…we get ADDITIONAL rows.
SQL> select seq,x,y
2 from T
3 where u = 0
4 and x not in ( select x from F where g = 1 union all select y from F where g = 1)
5 and y not in ( select x from F where g = 1 union all select y from F where g = 1);
SEQ X Y
---------- ---------- ----------
1 1 2
2 1 3
3 1 4
4 1 5
5 1 6
6 1 7
7 1 8
8 1 9
9 1 10
10 2 3
11 2 4
12 2 5
13 2 6
14 2 7
15 2 8
16 2 9
17 2 10
36 6 7
37 6 8
38 6 9
39 6 10
45 9 10
Somewhere in the optimization, we re-jigged just that little too much…and we got wrong results.
The is a good lesson for you own programs…optimize plenty…but not too much 🙂
Learning is not a spectator sport 
I know that there will always be bugs in software but wrong results have become a real disease – just search “wrong results” in Metalink.
The extent of the problem is inexcusable, in my opinion.
If you can’t trust your database to return the right results, what can you do?
By the way, what happens if you try the precompute_subquery hint in the subqueries?
I presume you’d get the right result.
No 12c to test it on… should really get that sorted.
the workaround adopted as the site where this was discovered was to drag the subquery out into a WITH (and possibly with a materialize hint, can’t recall).
I agree that wrong results are an ongoing problem…but I suppose on the other hand, we also scream when we “manually” see optimizations that we think the optimizer should have spotted…
I reckon its a tough job being an optimizer 🙂
Lesson duly noted 🙂
I’m guessing the plans or a trace gave you an indication of what transformation Oracle is doing. Is it something to do with the identifiers x and y – i.e. not correctly using the x and y columns from the F table for the subquery, or incorrectly assuming that it can skip the subquery for the last predicate?
Something has changed into the 12c CBO. 11.2.0.3 is giving correct results with different execution plan
alter session set optimizer_features_enable= '11.2.0.3'; explain plan for select count(1) from T where u = 0 and x not in ( select x from f where g = 1 union all select y from f where g = 1) and y not in ( select x from f where g = 1 union all select y from f where g = 1); select * from table(dbms_xplan.display); Plan hash value: 1926670974 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 39 | 20 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 39 | | | |* 2 | FILTER | | | | | | |* 3 | TABLE ACCESS FULL | T | 45 | 1755 | 3 (0)| 00:00:01 | | 4 | UNION-ALL | | | | | | |* 5 | TABLE ACCESS FULL| F | 3 | 78 | 3 (0)| 00:00:01 | |* 6 | TABLE ACCESS FULL| F | 3 | 78 | 3 (0)| 00:00:01 | | 7 | UNION-ALL | | | | | | |* 8 | TABLE ACCESS FULL| F | 3 | 78 | 3 (0)| 00:00:01 | |* 9 | TABLE ACCESS FULL| F | 3 | 78 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter( NOT EXISTS ( (SELECT "X" FROM "F" "F" WHERE "G"=1 AND LNNVL("X"<>:B1)) UNION ALL (SELECT "Y" FROM "F" "F" WHERE "G"=1 AND LNNVL("Y"<>:B2))) AND NOT EXISTS ( (SELECT "X" FROM "F" "F" WHERE "G"=1 AND LNNVL("X"<>:B3)) UNION ALL (SELECT "Y" FROM "F" "F" WHERE "G"=1 AND LNNVL("Y"<>:B4)))) 3 - filter("U"=0) 5 - filter("G"=1 AND LNNVL("X"<>:B1)) 6 - filter("G"=1 AND LNNVL("Y"<>:B1)) 8 - filter("G"=1 AND LNNVL("X"<>:B1)) 9 - filter("G"=1 AND LNNVL("Y"<>:B1)) SQL> show parameter optimizer NAME TYPE VALUE ------------------------------------ ----------- ----------------------- optimizer_features_enable string 12.1.0.1 Plan hash value: 1341523447 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 11 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 26 | | | |* 2 | FILTER | | | | | | |* 3 | TABLE ACCESS FULL | T | 45 | 1170 | 3 (0)| 00:00:01 | | 4 | UNION-ALL | | | | | | |* 5 | TABLE ACCESS FULL| F | 3 | 78 | 3 (0)| 00:00:01 | |* 6 | TABLE ACCESS FULL| F | 3 | 78 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter( NOT EXISTS ( (SELECT "X" FROM "F" "F" WHERE "G"=1 AND LNNVL("X"<>:B1)) UNION ALL (SELECT "Y" FROM "F" "F" WHERE "G"=1 AND LNNVL("Y"<>:B2)))) 3 - filter("U"=0) 5 - filter("G"=1 AND LNNVL("X"<>:B1)) 6 - filter("G"=1 AND LNNVL("Y"<>:B1))Dear Connor:
Thank you very much for bringing this defect to the attention of Oracle. At Oracle we take wrong results issues within the database very seriously. In fact, as you may have noticed in the service request which you entered for this problem, the assigned support analyst actually increased the severity of it from the initial severity with which you opened it, because all service requests for wrong results issues must be at severity two or higher.
Development has worked this problem as Bug 17980980, the fix for which will be included in Oracle Database 12c Rel. 1 Patchset 1 (12.1.0.2). The workaround in 12.1.0.1 is to set
ALTER SESSION SET “_fix_control”=’10216738:OFF’;
(or “ALTER SYSTEM”).
A customer with a valid support license may also request a one-off backport patch onto 12.1.0.1 for his or her specific platform.
Sincerely,
Ted
Oracle Global Customer Support
Dear Ted,
And here below is another case were wrong results can be produced and which is still not solved in 12c.
http://hourim.wordpress.com/2013/10/23/cbo-and-unusable-unique-index/
I have already signaled this to the optimizer group (maria collagan) but it seems that they have considered it as not worth the investigation.
Best regards
Mohamed Houri
Hi, Mohamed:
I’m sorry that you’re experiencing this problem.
Would you please try the following workaround?
ALTER SESSION SET “_optimizer_join_elimination_enabled” = false;
However, I noticed that your example was executed on 10.2.0.3. Kindly use 11.2.0.3 or higher, because those are the only releases still within Error Correction Support.
I believe that the issue you describe is the same one which Development is currently investigating as Bug 17533502, which was reported against 11.2.0.3. I’d encourage you to keep an eye on it to see when its status has changed to eighty, meaning a fix has been devised.
Sincerely,
Ted
Oracle Global Customer Support
Hi Ted,
Thanks for your answer. I have updated my blog according to the information you’ve supplied.
Best regards
Mohamed Houri
PS : Connor, I am sorry for this non intentional intrusion and thanks for your patience
It’s my pleasure.
Sincerely,
Ted
Oracle Global Customer Support