Learning is not a spectator sport

December 20, 2013

The challenge of optimization

Filed under: Uncategorized — connormcdonald @ 8:46 pm

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 :-)

About these ads

11 Comments »

  1. 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?

    Comment by Dom Brooks — December 20, 2013 @ 11:01 pm

    • 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.

      Comment by Dom Brooks — December 20, 2013 @ 11:26 pm

      • 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 :-)

        Comment by connormcdonald — December 23, 2013 @ 1:16 pm

  2. 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?

    Comment by Jeffrey Kemp — December 20, 2013 @ 11:03 pm

  3. 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))
    
    

    Comment by hourim — December 23, 2013 @ 3:58 pm

  4. 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

    Comment by Ted (Oracle Global Customer Support) — December 26, 2013 @ 10:42 pm

  5. 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

    Comment by hourim — December 26, 2013 @ 11:12 pm

    • 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

      Comment by Ted (Oracle Global Customer Support) — December 26, 2013 @ 11:36 pm

  6. […] 27-12-2013 :  following a discussion here , Ted from the Oracle Global Customer Support, pointed me to the bug number 17533502 which seems […]

    Pingback by CBO and unusable unique index | Mohamed Houri’s Oracle Notes — December 27, 2013 @ 4:23 pm

  7. 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

    Comment by hourim — December 27, 2013 @ 4:30 pm

    • It’s my pleasure.

      Sincerely,

      Ted
      Oracle Global Customer Support

      Comment by Ted (Oracle Global Customer Support) — December 27, 2013 @ 9:36 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The WordPress Classic Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 78 other followers

%d bloggers like this: